MsSQL数据库备份

要给一台服务器上的所有数据库每隔一段时间做一次完整备份.可以建一个作业(JOB)定时运行.实现批量备份服务器上所有数据库的方法有以下方式:

1.拼凑SQL语句

通过查询系统表sys.databases获取服务器上数据库名,拼凑备份语句然后执行.具体语句如下:

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + ' BACKUP DATABASE '+ QUOTENAME(name,'[]') 
            + ' TO DISK = ''D:\Data_Back\'+ name + '_BackUp_' + CONVERT(CHAR(8),GETDATE(),112) 
            + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak' 
            + ''' WITH NOINIT, NOUNLOAD, NAME = N''' + name + '_backup'', NOSKIP, STATS = 10, NOFORMAT'+char(13)+char(10) 
FROM sys.databases WHERE database_id >4 AND name like '%%' AND state =0
PRINT(@SQL) 
EXECUTE(@SQL)

执行效果:

BACKUP DATABASE [YSAnHui] TO DISK = 'D:\Data_Back\YSAnHui_BackUp_20150316935.bak' WITH NOINIT, NOUNLOAD, NAME = N'YSAnHui_backup', NOSKIP, STATS = 10, NOFORMAT

BACKUP DATABASE [YSBeiJing] TO DISK = 'D:\Data_Back\YSBeiJing_BackUp_20150316935.bak' WITH NOINIT, NOUNLOAD, NAME = N'YSBeiJing_backup', NOSKIP, STATS = 10, NOFORMAT

BACKUP DATABASE [distribution] TO DISK = 'D:\Data_Back\distribution_BackUp_20150316935.bak' WITH NOINIT, NOUNLOAD, NAME = N'distribution_backup', NOSKIP, STATS = 10, NOFORMAT

BACKUP DATABASE [YSGuangDong] TO DISK = 'D:\Data_Back\YSGuangDong_BackUp_20150316935.bak' WITH NOINIT, NOUNLOAD, NAME = N'YSGuangDong_backup', NOSKIP, STATS = 10, NOFORMAT

2.使用游标的方式进行完整备份:

DECLARE
  @DBName      VARCHAR(100),
  @SQL         VARCHAR(1000),
  @FileName    VARCHAR(200),
  @CurTime     VARCHAR(50)

SET @CurTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) 
             + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

DECLARE CurDBName CURSOR FOR SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0

BEGIN
    --Execute Backup
    SET @FileName = 'D:\Data_Back\' + @DBName + '_BackUp_' + @CurTime
    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@SQL)

    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName

3.参考系统存储过程sp_MSforeachtable后，在sp_MSforeachdb的基础上创建存储过程，这样你就可以让SQL在指定的数据库上执行了.

EXEC sp_helptext sp_MSforeachdb

4.备份语句

--完整备份
  Backup Database NorthwindCS
  To disk='G:\Backup\NorthwindCS_Full_20140908.bak'
--差异备份
  Backup Database NorthwindCS
  To disk='G:\Backup\NorthwindCS_Diff_20140908.bak' With Differential
--日志备份，默认截断日志
  Backup Log NorthwindCS
  To disk='G:\Backup\NorthwindCS_Log_20140908.bak'
--日志备份，不截断日志
  Backup Log NorthwindCS
  To disk='G:\Backup\NorthwindCS_Log_20070908.bak' With No_Truncate
--截断日志不保留
  Backup Log NorthwindCS With No_Log
--或者
  Backup Log NorthwindCS With Truncate_Only
--截断之后日志文件不会变小,有必要可以进行收缩

--文件备份
  Exec Sp_Helpdb NorthwindCS --查看数据文件
  Backup Database NorthwindCS
  File='NorthwindCS' --数据文件的逻辑名
  To disk='G:\Backup\NorthwindCS_File_20070908.bak'

--文件组备份
  Exec Sp_Helpdb NorthwindCS --查看数据文件
  Backup Database NorthwindCS
  FileGroup='Primary' --数据文件的逻辑名
  To disk='G:\Backup\NorthwindCS_FileGroup_20070908.bak'
  With init

--分割备份到多个目标,恢复的时候不允许丢失任何一个目标
  Backup Database NorthwindCS
  To disk='G:\Backup\NorthwindCS_Full_1.bak',
     disk='G:\Backup\NorthwindCS_Full_2.bak'

--镜像备份,每个目标都是相同的
  Backup Database NorthwindCS
  To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
  Mirror
  To disk='G:\Backup\NorthwindCS_Mirror_2.bak'
  With Format --第一次做镜像备份的时候格式化目标

--镜像备份到本地和远程
  Backup Database NorthwindCS
  To disk='G:\Backup\NorthwindCS_Mirror_1.bak'
  Mirror
  To disk='\\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak'
  With Format
--每天生成一个备份文件
  Declare @Path Nvarchar(2000)
  Set @Path ='G:\Backup\NorthwindCS_Full_'+Convert(Nvarchar,Getdate(),112)+'.bak'
  Backup Database NorthwindCS To dick=@path

--从NoRecovery或者Standby模式恢复数据库为可用
  Restore Database NorthwindCS_Bak With Recovery
--查看目标备份中的备份集
  Restore HeaderOnly
  From Disk ='G:\Backup\NorthwindCS_Full_20070908.bak'

--查看目标备份的第一个备份集的信息
  Restore FileListOnly
  From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'
  With File=1

--查看目标备份的卷标
  Restore LabelOnly
  From Disk ='G:\Backup\NorthwindCS_Full_20070908_2.bak'

--备份设置密码保护备份
  Backup Database NorthwindCS
  To disk='G:\Backup\NorthwindCS_Full_20070908.bak'
  With Password ='927',init
  Restore Database NorthwindCS
  From disk='G:\Backup\NorthwindCS_Full_20070908.bak'
  With Password ='927'

--备份:
   backup database dbname to disk=';d:\dbn.aa';
--恢复:
   restore database dbname from disk=';d:\dbn.aa';

---恢复必须在单用户模式下操作才能够成功,我们需要断开其他用户的连接,并且切换到其他数据库,才能够成功

 

转载于:https://www.cnblogs.com/hgmzas/p/4341092.html