SQLServer 批量备份与还原

备份与还原是数据库避不开的主题，而作为DBA，经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求；

在现在都讲究自动化管理的时代，传统的界面操作备份还原的做法不仅浪费时间和精力，而且还很容易出错，并且这次完成后，

下次再有这样的要求，必须又重头开始（估计做5次就能做得人狂吐）；于是，我们需要一套应对这种频繁操作、耗时、耗精力

的通用处理方法，所以以下批处理脚本就诞生了。

脚本主要的功能：

1. 备份一个服务器上的所有数据库（当然你也可以选择），备份文件按数据库名+日期生成，以.bak 结尾；

2. 将所有的备份文件还原到一台新机器上；

3. 验证磁盘和路径的正确性；

说明：

脚本合适 SQLServer 2005 & 2008 版本；

批量备份数据库：

-----------------------------批量备份数据-------------------------------------------

Use master

GO

/*=================Usp_BackUp_DataBase========================

  =====BackUp Sigle DataBase                            ======

  =====Ken.Guo                                          ======

  =====2010.9.10                                         ======

  =====Version: 2005 & 2008 SQL Server                  ======

  =====EXEC Usp_BackUp_DataBase 'MyDB','D:\\BackUp'      ======

  ============================================================

*/

CREATE PROC   [dbo].[Usp_BackUp_DataBase] @DatabaseName   nvarchar(200),@Path   nvarchar(200)   

AS  

  BEGIN 

  DECLARE   @fn   varchar(200)

           ,@sql   varchar(1000)   

  SET   @fn   =   @Path   +(case   when   right(@Path,1)   <>'\\'   then   '\\'   else   ''   end)   

  +@DatabaseName+'_'   

  +convert(char(8),getdate(),112)+'_'   

 +replace(convert(char(8),getdate(),108),':','')   

  +'.bak'   

  set   @sql   =   'backup   database   '+@DatabaseName   +   '   to   disk   =   N'''   +   @fn   +   ''''   

  --SELECT @sql 

  EXEC(@sql)  

  END

GO

Use master

GO

/*=============BackUp Mutile DataBase=========================*/

DECLARE @dbname nvarchar(200)

       ,@backup_path nvarchar(200)

SET @backup_path='D:\\BackUp\\'

DECLARE db_info CURSOR 

    LOCAL 

    STATIC 

    READ_ONLY 

    FORWARD_ONLY 

FOR --根据查询，添加其他筛选条件

  SELECT 

      name 

  FROM master.sys.databases WITH(NOLOCK) 

  WHERE 

      database_id>4

OPEN db_info

FETCH NEXT FROM db_info INTO @dbname

WHILE @@FETCH_STATUS=0

 begin

  EXEC master.dbo.Usp_BackUp_DataBase @dbname,@backup_path

  FETCH NEXT FROM db_info INTO @dbname

 END

close db_info

deallocate db_info

---------------------------------BackUp DataBase End------------------------------------

检查还原磁盘：

Use master

GO

/*=================Check Restore Path Drives Exists==========================

  =====Ken.Guo                                                         ======

  =====2010.9.10                                                        ======

  =====EXEC Usp_Check_DriveExists @RestoreDataPath,@ResultCount OUTPUT ======

  ===========================================================================

*/

CREATE PROC Usp_Check_DriveExists(

      @RestoreDataPath nvarchar(200)

     ,@ResultCount int OUTPUT) 

AS

BEGIN

--Check Restore Path and Size >1000M

if CHARINDEX(':',@RestoreDataPath)>0

  begin

    DECLARE @Drive nvarchar(10)

           ,@errorinfo nvarchar(500)

    DECLARE @DriveList TABLE 

    (    

         Drive nvarchar(10) 

        ,DSize bigint 

    )

    INSERT INTO @DriveList

     EXEC master.dbo.xp_fixeddrives

    SET @Drive=Left(@RestoreDataPath,CHARINDEX(':',@RestoreDataPath)-1)

    if not exists(SELECT 

                      * 

                  FROM  @DriveList 

                  WHERE 

                      Drive=@Drive 

                      AND DSize>1024

               )

      begin

       set @errorinfo=N'找不到还原磁盘:'+@Drive+N' ，或者磁盘剩余空间小于1G'

       RAISERROR 50001 @errorinfo 

       set @ResultCount=0

       return

      end

  end

else if(LEN(@RestoreDataPath)>1) AND CHARINDEX(':',@RestoreDataPath)=0

  begin

    set @errorinfo=N'还原路径错误:'+@RestoreDataPath+N'，必须包含":" 号'

    Raiserror 50001 @errorinfo   

    set @ResultCount= 0

    return 

  end

 set @ResultCount= 1

end

GO

还原单个数据库：

Use master

GO

/*=================Usp_RestoreDataBaseFormPath=======================================

  =====Restore Single DataBase From a Back File                                ======

  =====Ken.Guo                                                                 ======

  =====2010.9.10                                                                ======

  =====Version: 2005 & 2008 SQL Server                                         ======

  =====Usp_RestoreDataBaseFormPath 'D:\\databack\\dbcenter.bak','D:\\Data',0      ======

  =====Key Point Info:                                                         ======

  --Restore HeaderOnly  from disk='D:\\data\\xx.bak'

  --Restore FileListOnly from disk='D:\\data\\xx.bak'

  ===================================================================================

*/

CREATE PROC Usp_RestoreDataBaseFormPath

(@DatabBaseBakPath nvarchar(400),

 @RestoreDataPath nvarchar(400)='',  --RESTORE DATABASE PATH 

 @IsRun smallint=0 -- 0 PRINT  1 run 

) 

AS

BEGIN

set nocount on

declare @dbname nvarchar(200),@SQL nvarchar(4000),@DirSQL nvarchar(1000),@errorinfo nvarchar(300)

--add path \

if (@RestoreDataPath is not null) and len(@RestoreDataPath)>1 

   and (right(@RestoreDataPath,1)<>'\\')

   set @RestoreDataPath=@RestoreDataPath+'\\'

declare @checkdrive int

set @checkdrive=1

 exec master.dbo.Usp_Check_DriveExists @RestoreDataPath,@checkdrive output

 if(@checkdrive<>1)

    Goto ExitFLag 

DECLARE @BakFileList TABLE 

    (    LogicalName nvarchar(128)

        ,PhysicalName nvarchar(260)

    )

DECLARE @BakHeaderInfo TABLE

    (

        DatabaseName nvarchar(128)

    )

if Charindex('Microsoft SQL Server 2008',@@VERSION)>0

  begin

    --SQL Server 2008    

    DECLARE @BakFileList2008 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) NULL

        ,UniqueID uniqueidentifier

        ,ReadOnlyLSN numeric(25,0) NULL

        ,ReadWriteLSN numeric(25,0) NULL

        ,BackupSizeInBytes bigint

        ,SourceBlockSize int

        ,FileGroupID int

        ,LogGroupGUID uniqueidentifier NULL

        ,DifferentialBaseLSN numeric(25,0) NULL

        ,DifferentialBaseGUID uniqueidentifier

        ,IsReadOnly bit

        ,IsPresent bit

        ,TDEThumbprint varbinary(32)

      )    

     INSERT INTO @BakFileList2008        

       EXEC sp_executesql N'Restore FileListOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath 

    DECLARE @BakHeaderInfo2008 TABLE

    (

         BackupName nvarchar(128)

        ,BackupDescription nvarchar(255)

        ,BackupType smallint

        ,ExpirationDate datetime

        ,Compressed tinyint

        ,POSITION smallint

        ,DeviceType tinyint

        ,UserName nvarchar(128)

        ,ServerName nvarchar(128)

        ,DatabaseName nvarchar(128)

        ,DatabaseVersion int

        ,DatabaseCreationDate datetime

        ,BackupSize numeric(20,0)

        ,FirstLSN numeric(25,0)

        ,LastLSN numeric(25,0)

        ,CheckpointLSN numeric(25,0)

        ,DatabaseBackupLSN numeric(25,0)

        ,BackupStartDate datetime

        ,BackupFinishDate datetime

        ,SortOrder smallint

        ,CodePage smallint

        ,UnicodeLocaleId int

        ,UnicodeComparisonStyle int

        ,CompatibilityLevel tinyint

        ,SoftwareVendorId int

        ,SoftwareVersionMajor int

        ,SoftwareVersionMinor int

        ,SoftwareVersionBuild int

        ,MachineName nvarchar(128)

        ,Flags int

        ,BindingID uniqueidentifier

        ,RecoveryForkID uniqueidentifier

        ,COLLATION nvarchar(128)

        ,FamilyGUID uniqueidentifier

        ,HasBulkLoggedData bit

        ,IsSnapshot bit

        ,IsReadOnly bit

        ,IsSingleUser bit

        ,HasBackupChecksums bit

        ,IsDamaged bit

        ,BeginsLogChain bit

        ,HasIncompleteMetaData bit

        ,IsForceOffline bit

        ,IsCopyOnly bit

        ,FirstRecoveryForkID uniqueidentifier

        ,ForkPointLSN numeric(25,0) NULL

        ,RecoveryModel nvarchar(60)

        ,DifferentialBaseLSN numeric(25,0) NULL

        ,DifferentialBaseGUID uniqueidentifier

        ,BackupTypeDescription nvarchar(60)

        ,BackupSetGUID uniqueidentifier NULL

        ,CompressedBackupSize numeric(20,0)

    )           

    INSERT INTO @BakHeaderInfo2008        

       EXEC sp_executesql N'Restore HeaderOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath 

    insert into @BakHeaderInfo(DatabaseName)

    select DatabaseName from @BakHeaderInfo2008

    insert into @BakFileList(LogicalName ,PhysicalName)

    select  LogicalName ,PhysicalName from @BakFileList2008

  end

else

  begin

    --SQL Server 2005    

    DECLARE @BakFileList2005 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) NULL

        ,UniqueID uniqueidentifier

        ,ReadOnlyLSN numeric(25,0) NULL

        ,ReadWriteLSN numeric(25,0) NULL

        ,BackupSizeInBytes bigint

        ,SourceBlockSize int

        ,FileGroupID int

        ,LogGroupGUID uniqueidentifier NULL

        ,DifferentialBaseLSN numeric(25,0) NULL

        ,DifferentialBaseGUID uniqueidentifier

        ,IsReadOnly bit

        ,IsPresent bit

    )    

    INSERT INTO @BakFileList2005      

        EXEC sp_executesql N'Restore FileListOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath 

    DECLARE @BakHeaderInfo2005 TABLE 

    (

         BackupName nvarchar(128)

        ,BackupDescription nvarchar(255)

        ,BackupType smallint

        ,ExpirationDate datetime

        ,Compressed tinyint

        ,POSITION smallint

        ,DeviceType tinyint

        ,UserName nvarchar(128)

        ,ServerName nvarchar(128)

        ,DatabaseName nvarchar(128)

        ,DatabaseVersion int

        ,DatabaseCreationDate datetime

        ,BackupSize numeric(20,0)

        ,FirstLSN numeric(25,0)

        ,LastLSN numeric(25,0)

        ,CheckpointLSN numeric(25,0)

        ,DatabaseBackupLSN numeric(25,0)

        ,BackupStartDate datetime

        ,BackupFinishDate datetime

        ,SortOrder smallint

        ,CodePage smallint

        ,UnicodeLocaleId int

        ,UnicodeComparisonStyle int

        ,CompatibilityLevel tinyint

        ,SoftwareVendorId int

        ,SoftwareVersionMajor int

        ,SoftwareVersionMinor int

        ,SoftwareVersionBuild int

        ,MachineName nvarchar(128)

        ,Flags int

        ,BindingID uniqueidentifier

        ,RecoveryForkID uniqueidentifier

        ,COLLATION nvarchar(128)

        ,FamilyGUID uniqueidentifier

        ,HasBulkLoggedData bit

        ,IsSnapshot bit

        ,IsReadOnly bit

        ,IsSingleUser bit

        ,HasBackupChecksums bit

        ,IsDamaged bit

        ,BeginsLogChain bit

        ,HasIncompleteMetaData bit

        ,IsForceOffline bit

        ,IsCopyOnly bit

        ,FirstRecoveryForkID uniqueidentifier

        ,ForkPointLSN numeric(25,0) NULL

        ,RecoveryModel nvarchar(60)

        ,DifferentialBaseLSN numeric(25,0) NULL

        ,DifferentialBaseGUID uniqueidentifier

        ,BackupTypeDescription nvarchar(60)

        ,BackupSetGUID uniqueidentifier NULL

    )    

    INSERT INTO @BakHeaderInfo2005        

        EXEC sp_executesql N'Restore HeaderOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath 

    insert into @BakHeaderInfo(DatabaseName)

    select DatabaseName from @BakHeaderInfo2005

    insert into @BakFileList(LogicalName ,PhysicalName)

    select  LogicalName ,PhysicalName from @BakFileList2005

  end

--Check back file info

if not exists (select 1 from @BakFileList) OR not exists (select 1 from @BakHeaderInfo)

 begin

   set @errorinfo=N'取不到备份文件:'+@DatabBaseBakPath+N' 的信息，请检查备份文件是否正确或者版本是否兼容'

   Raiserror 50001 @errorinfo    

   Goto ExitFLag

 end

--Get DataBase Name

SELECT TOP 1 @dbname=databasename FROM @BakHeaderInfo

if exists (select 1 from master.sys.databases with(nolock) where name=@dbname)

     begin

       set @errorinfo=N'数据库：'+@dbname+N'已经存在，不能还原' 

       Raiserror 50001 @errorinfo  

       Goto ExitFLag

     end

DECLARE @LogicalName nvarchar(200),@PhysicalName nvarchar(400)

       ,@pos int ,@endpos int,@LastPhysicalName nvarchar(400)

DECLARE db_file CURSOR 

    LOCAL 

    READ_ONLY 

    FORWARD_ONLY 

    STATIC 

FOR

 SELECT 

     LogicalName

    ,PhysicalName  

 FROM @BakFileList

OPEN db_file

set @DirSQL=''

set @SQL=+N'RESTORE DATABASE '+QUOTENAME(@dbname)+' from disk=N'''+@DatabBaseBakPath+''''

set @SQL=@SQL+char(13)+Char(10)+N' WITH FILE=1 '

FETCH NEXT FROM db_file INTO @LogicalName,@PhysicalName

WHILE @@FETCH_STATUS=0

 begin

   ---Get DB PhysicalName

   set @endpos=0

   while CHARINDEX('\\',@PhysicalName)>0

    begin

      set @pos=CHARINDEX('\\',@PhysicalName,@endpos)

      if(@pos=0)

          break;

      set @endpos=@pos+1;

    end

   --create new db path

   if(len(@RestoreDataPath)>1)

      begin

          set @PhysicalName=@RestoreDataPath+@dbname+'\\'+SUBSTRING(@PhysicalName,@endpos,LEN(@PhysicalName)-@endpos+1)

          set @DirSQL=N'EXEC master.sys.xp_create_subdir N'''+@RestoreDataPath+@dbname+''''

       END

    else

      begin

        if len(@DirSQL)<1 OR (SUBSTRING(@PhysicalName,1,@endpos-1)<>@LastPhysicalName)

          if(len(@DirSQL)<1)

             set @DirSQL=N'EXEC master.sys.xp_create_subdir N'''+SUBSTRING(@PhysicalName,1,@endpos-1)+''''

          else

           set @DirSQL=@DirSQL+char(13)+N'EXEC master.sys.xp_create_subdir N'''+SUBSTRING(@PhysicalName,1,@endpos-1)+''''

         ---Check Drives

         set @checkdrive=1

         exec master.dbo.Usp_Check_DriveExists @PhysicalName,@checkdrive output

         if(@checkdrive<>1)

            Goto ExitFLag 

        set @LastPhysicalName=SUBSTRING(@PhysicalName,1,@endpos-1);

      END

    set @SQL=@SQL+char(13)+Char(10)+N' ,Move N'''+@LogicalName+''''+' TO N'''+@PhysicalName+''''

   FETCH NEXT FROM db_file INTO @LogicalName,@PhysicalName

 end

 set @SQL=@SQL+char(13)+Char(10)+N' ,NOUNLOAD,Recovery,STATS = 10'

if(@IsRun=0)

    print( @DirSQL+char(13)+char(10)+'GO'+char(13)+Char(10)+@SQL+char(13))

else

 begin

  print('-----------Begin Restore Database:'+@dbname+'------------------')

  exec(@DirSQL)

  exec(@SQL)

  print('-----------End Restore Database:'+@dbname+'---------------------'+char(13))

 end

 close db_file

 deallocate db_file

ExitFLag:

set nocount off

end

批量还原数据库：

Use master

GO

/*=================Usp_RestoreMuiteDataBaseFromPath========================

  =====Restore Mutite DataBase File From a Path                      ======

  =====Ken.Guo                                                       ======

  =====2010.9.10                                                      ======

  =====Version: 2005 & 2008 SQL Server                               ======

  =====EXEC Usp_RestoreMuiteDataBaseFromPath 'D:\\databack','',0      ======

  =========================================================================

*/

CREATE PROC Usp_RestoreMuiteDataBaseFromPath

( @DatabBaseBakPath nvarchar(400)

 ,@RestoreDataPath nvarchar(400)=''  --RESTORE DATABASE PATH 

 ,@IsRun smallint=0                   -- 0 PRINT 1 run 

) 

AS

BEGIN

set nocount on

DECLARE @BackUpFileName nvarchar(200) 

       ,@DbName nvarchar(200) 

       ,@errorinfo nvarchar(400)

IF not exists(SELECT 1 

              FROM master.sys.procedures WITH(NOLOCK) 

              WHERE 

                  name=N'Usp_RestoreDataBaseFormPath'  

           )

  begin

   Raiserror 50001 N'找不到存储过程SP_RestoreDataBaseFormPath '    

   Goto ExitFLag

  end

--add path \

if (@DatabBaseBakPath is not null) and len(@DatabBaseBakPath)>1 

   and (right(@DatabBaseBakPath,1)<>'\\')

 set @DatabBaseBakPath=@DatabBaseBakPath+'\\'

--Check Restore Path and Size >1000M

DECLARE @checkdrive int

SET @checkdrive=1

 EXEC master.dbo.Usp_Check_DriveExists @RestoreDataPath,@checkdrive OUTPUT

 IF(@checkdrive<>1)

    Goto ExitFLag 

DECLARE @Dir TABLE 

( 

     BackDBFileName nvarchar(100) 

    ,DEPTH int 

    ,[File] int 

)

INSERT INTO @Dir EXEC xp_dirtree @DatabBaseBakPath

                     ,1

                     ,1

DELETE FROM @Dir 

WHERE charindex('.bak',BackDBFileName)=0

if not exists (select top 1 1 from @Dir)

  begin

   Raiserror 50001 N'在提供的路径下没有找到合符要求的备份文件'    

   Goto ExitFLag

  end

declare db_file Cursor Local Static Read_Only Forward_Only

for

select BackDBFileName from @Dir

Open db_file

Fetch Next from db_file into @BackUpFileName

while @@FETCH_STATUS=0

 begin

  --Restore DataBase

  set @BackUpFileName=@DatabBaseBakPath+@BackUpFileName

  exec master.dbo.Usp_RestoreDataBaseFormPath @BackUpFileName,@RestoreDataPath,@IsRun

  Fetch Next from db_file into @BackUpFileName

 end

Close db_file

deallocate db_file

ExitFLag:

set nocount off

end