每次通过 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
====================================================================
还原
====================================================================
生成还原脚本的脚本:
请先填写参数表:
源路径,目的路径,数据库名列表,是否直接还原(@是否执行)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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--------------------------------------------------------------------------------------------------