每次通過 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--------------------------------------------------------------------------------------------------