原文: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 = N'E:\05_DataBase_bakup\'; --数据库服务器 SET @dbServer = N'.'; --数据库用户名 SET @dbUserName = N'sa'; --数据库用户密码 SET @dbUserPwd = N'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);
DECLARE @mdfName NVARCHAR(500);
DECLARE @ldfName NVARCHAR(500);
--数据库备份文件存放路径
SET @path = N'd:\DBBAK\';
--数据库还原后的文件存放路径
SET @restorePath = N'D:\PMSDB\';
--还原到的数据库服务器
SET @dbServer = N'127.0.0.1';
--还原到的数据库用户名
SET @dbUserName = N'sa';
--还原到的数据库用户密码
SET @dbUserPwd = N'xxxxxxx';
DECLARE cursors CURSOR FOR
--查询集合
--注意：这里将数据库文件的物理路径替换掉了一部分，只保留数据库名称
SELECT [name],
REPLACE(REPLACE(filename, 'D:\DBBAK\', ''), '.mdf', '') AS [fileName]
FROM [sysdatabases]
WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb', 'DWDiagnostics', 'DWConfiguration', 'DWQueue', 'BIReport',
'distribution', 'ReportServer', 'ReportServerTempDB'
)
ORDER BY name;
OPEN cursors;
FETCH NEXT FROM cursors
INTO @name,
@fileName;
WHILE @@fetch_status = 0
BEGIN
SELECT @mdfName = f.name
FROM sysaltfiles f
INNER JOIN sysdatabases d
ON (f.dbid = d.dbid)
AND d.name = @name
AND CHARINDEX('MDF', f.filename) > 0;
SELECT @ldfName = f.name
FROM sysaltfiles f
INNER JOIN sysdatabases d
ON (f.dbid = d.dbid)
AND d.name = @name
AND CHARINDEX('LDF', f.filename) > 0;
--遍历集合
PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd + ' -Q "RESTORE DATABASE [' + @name
+ '] FROM DISK = ''' + @path + '[20170720]' + @name + '.bak'' WITH FILE = 1, MOVE N''' + @mdfName
+ ''' TO N''' + @restorePath + @name + '.mdf'', MOVE N''' + @ldfName + ''' TO N''' + @restorePath + @name
+ '.LDF'', NOUNLOAD, REPLACE,STATS = 10';
FETCH NEXT FROM cursors
INTO @name,
@fileName;
END;
CLOSE cursors;
DEALLOCATE cursors;
