SQLSERVER批量备份和恢复数据库

一个代账的客户有800多个客户，在一个数据库实例中有800多个数据库，需要进行批量迁移

• 开启在SQL中执行shell命令配置

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

• 备份

DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @BAKSQL VARCHAR(1000)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

DECLARE CurDBName CURSOR FOR 
    SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute Backup
    SET @FileName = 'E:\BAK\' + @DBName + '_' + @CurrentTime
    SET @BAKSQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@BAKSQL)

    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName

• 恢复

declare
   @backpath varchar(2000),
   @execsql varchar(2000),
   @datapath varchar(2000),
   @lgname  varchar(64),
   @lgtype varchar(12),
   @lgfilename varchar(64),
   @i int
 begin
   set @backpath='E:\BAK\';
   set @execsql='dir /b '+ @backpath +'\*.BAK';
set @datapath='E:\DATA';
begin
  if not exists (select * from sysobjects where name='backfiles')
    create table backfiles(name varchar(2000));
  end
  truncate table backfiles
  insert into backfiles exec xp_cmdshell @execsql
 
  DELETE backfiles WHERE upper(right(name,3))<>'BAK' OR NAME IS NULL
 
  declare @fileName varchar(100),
          @dbName varchar(100)
  
  declare file_cur cursor for select name from backfiles;
  open file_cur
  fetch next from file_cur into @fileName
  while @@fetch_status=0
  begin
    select @dbName=substring(@fileName,1,charindex('.bak',@filename)-1);

    ----------------------------
    DECLARE @fileListTable TABLE (
        [LogicalName]   NVARCHAR(128), 
        [PhysicalName]   NVARCHAR(260), 
        [Type]     CHAR(1), 
        [FileGroupName]   NVARCHAR(128), 
        [Size]     NUMERIC(20,0), 
        [MaxSize]    NUMERIC(20,0), 
        [FileID]    BIGINT, 
        [CreateLSN]    NUMERIC(25,0), 
        [DropLSN]    NUMERIC(25,0), 
        [UniqueID]    UNIQUEIDENTIFIER, 
        [ReadOnlyLSN]   NUMERIC(25,0), 
        [ReadWriteLSN]   NUMERIC(25,0), 
        [BackupSizeInBytes]  BIGINT, 
        [SourceBlockSize]  INT, 
        [FileGroupID]   INT, 
        [LogGroupGUID]   UNIQUEIDENTIFIER, 
        [DifferentialBaseLSN] NUMERIC(25,0), 
        [DifferentialBaseGUID] UNIQUEIDENTIFIER, 
        [IsReadOnly]   BIT, 
        [IsPresent]    BIT, 
        [TDEThumbprint]   VARBINARY(32) -- remove this column if using SQL 2005 
    ) 
    INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@backpath+@fileName+'''') 
    set @execsql = '';
    set @execsql ='restore database '+'"'+@dbName+'"' + ' from disk='+CHAR(39)+@backpath+@fileName+CHAR(39) +' with ' ;
    -----------------
    set @i = 1
    declare bak_cur cursor for select 
          [LogicalName]
         ,reverse(substring(reverse([PhysicalName]),0,charindex('\',reverse([PhysicalName])))) as filen_ame
         ,[Type] 
       from @fileListTable
    open bak_cur
    fetch next from bak_cur into @lgname,@lgfilename,@lgtype
    while @@FETCH_STATUS = 0
    begin
      /*
      不考虑文件重名的情况，统一恢复到同一目录下 
      */
      set @execsql = @execsql + (case when @i = 1 then ' ' else ', ' end) + ' move '+CHAR(39)+@lgname+CHAR(39)+' to '+CHAR(39)+@datapath+'\'+ @lgfilename +CHAR(39)

      set  @i = @i + 1
      fetch next from bak_cur into @lgname,@lgfilename,@lgtype
    end
    close bak_cur
    deallocate bak_cur

    ----------------------------
    print @execsql
    exec (@execsql)
    delete from  @fileListTable
    fetch next from file_cur into @fileName
  end
  close file_cur
  deallocate file_cur
end