MS SQL数据批量备份还原(适用于MS SQL 2005+)

我们知道通过Sql代理，可以实现数据库的定时备份功能；当数据库里的数据库很多时，备份一个数据库需要建立对应的定时作业，相对来说比较麻烦；

还好，微软自带的osql工具，比较实用，通过在命令行里里输入命令，也能实现数据库备份的功能；如果能通过sql语句来生成一个批处理文件，那就方便了；

下面即是生成批处理文件的sql脚本，通过它可以实现：运行一个批处理文件，备份数据库服务器上的所有数据库到指定目录。

生成批量备份脚本：

/************************************************************
 * 数据备份脚本：
 * 1.运行前，请根据实际配置修改相应账户信息；
 * 2.请将脚本执行结果另存为批处理文件，执行批处理文件即可；
 * 时间: 2015/3/10 14:21:15
 ************************************************************/
 
DECLARE @name        NVARCHAR(200)  
DECLARE @path        NVARCHAR(500)   
DECLARE @dbServer    NVARCHAR(500) 
DECLARE @dbUserName  NVARCHAR(500)   
DECLARE @dbUserPwd   NVARCHAR(500)   
--备份文件存放路径
SET @path = 'E:\05_DataBase_bakup\'   
--数据库服务器
SET @dbServer = '.'   
--数据库用户名        
SET @dbUserName = 'sa'  
--数据库用户密码         
SET @dbUserPwd = '123'        
DECLARE cursors CURSOR  
FOR
    --查询集合
    SELECT [name]
    FROM   [sysdatabases]
    WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb')
             
 OPEN cursors                       
 FETCH NEXT FROM cursors INTO @name  
WHILE @@fetch_status = 0
BEGIN
    --遍历集合 
    PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd + 
    ' -Q "BACKUP DATABASE ['
    + @name + '] TO DISK = ''' + @path +
    '[%date:~0,4%%date:~5,2%%date:~8,2%]' + @name + '.bak''"' 
    FETCH NEXT FROM cursors INTO @name
END
 CLOSE cursors                
 DEALLOCATE cursors

生成批量还原脚本：

/************************************************************
 * 生成批量还原数据库脚本
 * Time: 2015/4/19 18:30:05
 ************************************************************/
DECLARE @name         NVARCHAR(200)  
DECLARE @fileName     NVARCHAR(200)  
DECLARE @path         NVARCHAR(500)  
DECLARE @restorePath  NVARCHAR(500)   
DECLARE @dbServer     NVARCHAR(500) 
DECLARE @dbUserName   NVARCHAR(500)   
DECLARE @dbUserPwd    NVARCHAR(500)   
--数据库备份文件存放路径
SET @path = 'E:\05_DataBase_bakup\' 
--数据库还原后的文件存放路径  
SET @restorePath = 'E:\db2008\'   
--还原到的数据库服务器
SET @dbServer = '192.168.0.7,1501\SQL2008'   
--还原到的数据库用户名       
SET @dbUserName = 'netuser'  
--还原到的数据库用户密码        
SET @dbUserPwd = 'netuser2008'        
DECLARE cursors     CURSOR  
FOR
    --查询集合
    --注意：这里将数据库文件的物理路径替换掉了一部分，只保留数据库名称    
    SELECT [name],
           REPLACE  (REPLACE(FILENAME, 'E:\01.数据库\', ''), '.mdf', '') AS 
           [fileName]
    FROM   [sysdatabases]
    WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb')  
             
 OPEN cursors                       
 FETCH NEXT FROM cursors INTO @name,@fileName                                                                 
WHILE @@fetch_status = 0
BEGIN
    --遍历集合
    PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd +
    ' -Q "RESTORE DATABASE [' + @name + '] FROM DISK = ''' + @path + @name +
    '.bak'' WITH  FILE = 1,  MOVE N''' + @fileName + ''' TO N''' + @restorePath 
    + @name
    + '.mdf'',  MOVE N''' + @fileName + '_log'' TO N''' + @restorePath + @name +
    '.LDF'', NOUNLOAD,  REPLACE,STATS = 10' 
    FETCH NEXT FROM cursors INTO @name,@fileName
END
 CLOSE cursors                
 DEALLOCATE cursors