MSSQL数库备份与还原脚本（多个库时很方便）

每次通过 Management Studio 的界面操作备份或还原数据库，对于单个数据库还好，要是一次要做多个。那就还是用脚本快些，下面有两段脚本分享一下。

===========================================================================
备份
===========================================================================

生成备份脚本的脚本：

d:\databak\为存在目录

SELECT 'BACKUP DATABASE ' + name + ' TO DISK = N''d:\databak\' + name + '.bak''  WITH NOFORMAT, NOINIT,  NAME = N''' + name + '-完整 数据库 备份'',  SKIP, NOREWIND, NOUNLOAD, STATS = 10'FROM sys.databaseswhere database_id>4  -- 跳过系统库order by database_idgo

执行后生成如下脚本，复制如下脚本将正式执行备份：

BACKUP DATABASE   DataBaseName TO DISK = N'd:\databak\DataBaseName.bak'   WITH NOFORMAT, NOINIT,     NAME = N'DataBaseName-完整 数据库 备份',   SKIP, NOREWIND, NOUNLOAD, STATS = 10

===========================================================================
还原
===========================================================================

生成还原脚本的脚本：

请先填写参数表：  

源路径，目的路径，数据库名列表，是否直接还原（@是否执行）

 1 --START-------------------------------------------------------------------------------------------------- 2 USE master 3 GO 4 declare @srcPath varchar(500); 5 declare @tarPath varchar(500); 6 declare @是否执行 int; 7  8 CREATE TABLE #DATABASE( 9   id int identity(1,1), 10   name varchar(255) 11 ) 12 --参数表--可同时多个库------------------------------------------- 13 INSERT INTO #DATABASE(name) 14 SELECT 'DataBaseName0' 15 --UNION ALL SELECT 'DataBaseName1' 16 --UNION ALL SELECT 'DataBaseName2' 17 --UNION ALL SELECT 'DataBaseName3' 18 --UNION ALL SELECT 'DataBaseName4' 19  20 ---路径---------------------------------------------- 21 SET @是否执行 = 1;--是否直接执行，若否，只打印还原语句 22 SET @srcPath = 'G:\DBDATA\'; 23 SET @tarPath = 'G:\SQLData\SQL00\'; 24 --参数表End--------------------------------------------------- 25  26 DECLARE @newLine varchar(500); 27 SET @newLine = CHAR(10) --+ CHAR(13); 28 DECLARE @dbName varchar(500); 29 DECLARE @fName varchar(500); 30  31 -------------WHILE 32 DECLARE @I INT; 33 SELECT @I = MAX(id) FROM #DATABASE; 34 WHILE @I IS NOT NULL 35 BEGIN 36  37   SELECT @dbName = name FROM #DATABASE WHERE id = @I; 38    39   CREATE TABLE #TABLE( 40     LogicalName VARCHAR(255), 41     PhysicalName VARCHAR(255), 42     Type VARCHAR(255), 43     FileGroupName VARCHAR(255), 44     Size BIGINT,--NUMERIC 45     MaxSize BIGINT,--NUMERIC 46     FileId BIGINT, 47     CreateLSN BIGINT, 48     DropLSN BIGINT, 49     UniqueId VARCHAR(255), 50     ReadOnlyLSN BIGINT, 51     ReadWriteLSN BIGINT, 52     BackupSizeInBytes BIGINT, 53     SourceBlockSize BIGINT, 54     FileGroupId BIGINT, 55     LogGroupGUID VARCHAR(255),-- 56     DifferentialBaseLSN VARCHAR(255), 57     DifferentialBaseGUID VARCHAR(255), 58     IsReadOnly BIGINT, 59     IsPresent BIGINT, 60     TDEThumbprint VARCHAR(255) 61   ) 62  63   declare @sql varchar(1000); 64   set @sql = 'RESTORE FILELISTONLY FROM DISK = N'''+@srcPath+@dbName+'.bak''' 65   insert into #TABLE exec (@sql) 66   --RESTORE FILELISTONLY FROM DISK = N'G:\DBDATA\20150316_YN_WB\MTNOH_AAA_Resource2.bak'  67   declare @logicalName_d varchar(500); 68   declare @logicalName_l varchar(500); 69   --set @logicalName_d = 'MTNOH_AAA_Resource'; 70   --set @logicalName_l = 'MTNOH_AAA_Resource_log'; 71   SELECT @logicalName_d = LogicalName FROM #TABLE WHERE [Type] = 'D'; 72   SELECT @logicalName_l = LogicalName FROM #TABLE WHERE [Type] = 'L'; 73  74   set @logicalName_d = case when @logicalName_d IS NULL THEN @dbName ELSE @logicalName_d END; 75   set @logicalName_l = case when @logicalName_l IS NULL THEN @dbName+'_log' ELSE @logicalName_l END; 76   set @fName = @dbName + '.bak'; 77  78   create table #temp( 79     dbName varchar(500), 80     fName varchar(500), 81     srcPath varchar(500), 82     tarPath varchar(500) 83   ) 84   declare @RESULT varchar(8000); 85   insert into #temp select @dbName,@fName,@srcPath,@tarPath; 86  87   SELECT @RESULT = @newLine  88     + CASE WHEN @是否执行 = 1 THEN '' ELSE 'USE master ' END 89     + @newLine + ' RESTORE DATABASE ' +@dbName 90     + @newLine +' FROM DISK = '''+@srcPath+fName+'''' 91     + @newLine + ' WITH MOVE '''+@logicalName_d+''' TO '''+tarPath+dbName+'.mdf'',' 92     + @newLine + ' MOVE '''+@logicalName_l+''' TO '''+tarPath+dbName+'_log.ldf'',' 93     + @newLine + ' STATS = 10, REPLACE ' 94     + @newLine + CASE WHEN @是否执行 = 1 THEN '' ELSE ' GO ' END 95     from #temp; 96      97   PRINT @RESULT; 98   IF @是否执行 = 1 99     EXEC(@RESULT);100   --select @RESULT101   TRUNCATE TABLE #temp;102   DROP TABLE #temp;103   TRUNCATE TABLE #TABLE;104   drop table #TABLE;105   DELETE #DATABASE WHERE id = @I;106   SELECT @I = MAX(id) FROM #DATABASE;107 END108 109 TRUNCATE TABLE #DATABASE110 DROP TABLE #DATABASE;111 112 --END-------------------------------------------------------------------------------------------------- 

原标题：MSSQL数库备份与还原脚本（多个库时很方便）

关键词：MSSQL