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.databases
where database_id>4    -- 跳过系统库
order by database_id
go

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

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 @RESULT
101     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 END
108 
109 TRUNCATE TABLE #DATABASE
110 DROP TABLE #DATABASE;
111 
112 --END-------------------------------------------------------------------------------------------------- 

View Code

 

转载于:https://www.cnblogs.com/hrzhao/p/4428252.html