SQL Server 数据库批量备份及批量恢复脚本

测试数据库版本 SQL server 2012

批量备份脚本

设定好备份目录，以及备份文件名格式，此处我们备份目录为d:\backup 备份文件格式为{dbname}.bak

 1 DECLARE
 2       @FileName VARCHAR(200),
 3       @CurrentTime VARCHAR(50),
 4       @DBName VARCHAR(100),
 5       @SQL VARCHAR(1000)
 6 
 7 SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112)
 9 DECLARE CurDBName CURSOR FOR 
10     SELECT NAME FROM Master..SysDatabases where dbid>4
11 
12 OPEN CurDBName
13 FETCH NEXT FROM CurDBName INTO @DBName
14 WHILE @@FETCH_STATUS = 0
15 BEGIN
16     --Execute Backup
17     SET @FileName = 'd:\backup\' + @DBName 
18     SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
19      ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
20     EXEC(@SQL)
21 
22     --Get Next DataBase
23     FETCH NEXT FROM CurDBName INTO @DBName
24 END
25 
26 CLOSE CurDBName
27 DEALLOCATE CurDBName

SQL 中常用存储过程xp_cmdshell运行cmd命令

 1 USE master
 2 GO
 3 RECONFIGURE --先执行一次刷新，处理上次的配置
 4 GO
 5 EXEC sp_configure 'show advanced options',1 --启用xp_cmdshell的高级配置
 6 GO
 7 RECONFIGURE --刷新配置
 8 GO
 9 EXEC sp_configure 'xp_cmdshell',1  --打开xp_cmdshell,可以调用SQL系统之外的命令
10 GO
11 RECONFIGURE
12 GO

批量恢复数据库脚本

 1 declare
 2   @backpath varchar(2000),
 3   @execsql varchar(2000),
 4   @datapath varchar(2000),
 5   @lgname  varchar(64),
 6   @lgtype varchar(12),
 7   @lgfilename varchar(64),
 8   @i int
 9 begin
10   set @backpath='d:\backup\';
11   set @execsql='dir /b '+ @backpath +'\*.BAK';
12   set @datapath='d:\Data';
13 begin
14   if not exists (select * from sysobjects where name='backfiles')
15     create table backfiles(name varchar(2000));
16   end
17   truncate table backfiles
18   insert into backfiles exec xp_cmdshell @execsql
19  
20   DELETE backfiles WHERE upper(right(name,3))<>'BAK' OR NAME IS NULL
21  
22   declare @fileName varchar(100),
23           @dbName varchar(100)
24   
25   declare file_cur cursor for select name from backfiles;
26   open file_cur
27   fetch next from file_cur into @fileName
28   while @@fetch_status=0
29   begin
30     select @dbName=substring(@fileName,1,charindex('.bak',@filename)-1);
31 
32     ----------------------------
33     DECLARE @fileListTable TABLE (
34         [LogicalName]   NVARCHAR(128), 
35         [PhysicalName]   NVARCHAR(260), 
36         [Type]     CHAR(1), 
37         [FileGroupName]   NVARCHAR(128), 
38         [Size]     NUMERIC(20,0), 
39         [MaxSize]    NUMERIC(20,0), 
40         [FileID]    BIGINT, 
41         [CreateLSN]    NUMERIC(25,0), 
42         [DropLSN]    NUMERIC(25,0), 
43         [UniqueID]    UNIQUEIDENTIFIER, 
44         [ReadOnlyLSN]   NUMERIC(25,0), 
45         [ReadWriteLSN]   NUMERIC(25,0), 
46         [BackupSizeInBytes]  BIGINT, 
47         [SourceBlockSize]  INT, 
48         [FileGroupID]   INT, 
49         [LogGroupGUID]   UNIQUEIDENTIFIER, 
50         [DifferentialBaseLSN] NUMERIC(25,0), 
51         [DifferentialBaseGUID] UNIQUEIDENTIFIER, 
52         [IsReadOnly]   BIT, 
53         [IsPresent]    BIT, 
54         [TDEThumbprint]   VARBINARY(32) -- remove this column if using SQL 2005 
55     ) 
56     INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@backpath+@fileName+'''') 
57     set @execsql = '';
58     set @execsql ='restore database '+'"'+@dbName+'"' + ' from disk='+CHAR(39)+@backpath+@fileName+CHAR(39) +' with ' ;
59     -----------------
60     set @i = 1
61     declare bak_cur cursor for select 
62           [LogicalName]
63          ,reverse(substring(reverse([PhysicalName]),0,charindex('\',reverse([PhysicalName])))) as filen_ame
64          ,[Type] 
65        from @fileListTable
66     open bak_cur
67     fetch next from bak_cur into @lgname,@lgfilename,@lgtype
68     while @@FETCH_STATUS = 0
69     begin
70       /*
71       不考虑文件重名的情况，统一恢复到同一目录下 
73       */
74       set @execsql = @execsql + (case when @i = 1 then ' ' else ', ' end) + ' move '+CHAR(39)+@lgname+CHAR(39)+' to '+CHAR(39)+@datapath+'\'+ @lgfilename +CHAR(39)
75 
76       set  @i = @i + 1
77       fetch next from bak_cur into @lgname,@lgfilename,@lgtype
78     end
79     close bak_cur
80     deallocate bak_cur
81 
82     ----------------------------
83     print @execsql
84     exec (@execsql)
85     delete from  @fileListTable
86     fetch next from file_cur into @fileName
87   end
88   close file_cur
89   deallocate file_cur
90 end

 1 --关闭
 2 EXEC sp_configure 'show advanced options','1' --确保show advances options 的值为1,这样才可以执行xp_cmdshell为0的操作
 3 GO
 4 RECONFIGURE
 5 GO
 6 EXEC sp_configure 'xp_cmdshell',0 --关闭xp_cmdshell
 7 GO
 8 RECONFIGURE
 9 GO
10 EXEC sp_configure 'show advanced options','0' --关闭show advanced options
11 GO
12 RECONFIGURE
13 GO