测试数据库版本 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