SQLServer 批量备份与还原

1、SQLServer 批量备份与还原SQLServer 批量备份与还原备份与还原是数据库避不开的主题，而作为DBA，经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求；在现在都讲究自动化管理的时代，传统的界面操作备份还原的做法不仅浪费时间和精力，而且还很容易出错，并且这次完成后，下次再有这样的要求，必须又重头开始（估计做5次就能做得人狂吐）；于是，我们需要一套应对这种频繁操作、耗时、耗精力的通用处理方法，所以以下批处理脚本就诞生了。脚本主要的功能：1. 备份一个服务器上的所有数据库（当然你也可以选择），备份文件按数据库名+日期生成，以.bak 结尾；2. 将所有的备份文件还原到一台新

2、机器上；3. 验证磁盘和路径的正确性；说明：脚本合适 SQLServer 2005 & 2008 版本；批量备份数据库：-批量备份数据-Use masterGO/*=Usp_BackUp_DataBase=BackUp Sigle DataBase =Ken.Guo =2010.9.10 =Version: 2005 & 2008 SQL Server =EXEC Usp_BackUp_DataBase MyDB,D:BackUp =*/CREATE PROCdbo.Usp_BackUp_DataBaseDatabaseName nvarchar(200),Path nvarchar(200)

3、ASBEGINDECLAREfn varchar(200),sql varchar(1000)SETfn=Path+(case when right(Path,1) +DatabaseName+_+convert(char(8),getdate(),112)+_+replace(convert(char(8),getdate(),108),:,)+.baksetsql=backup database +DatabaseName+ to disk = N+fn+ -SELECT sqlEXEC(sql)ENDGOUse masterGO/*=BackUp Mutile DataBase=*/ D

4、ECLAREdbname nvarchar(200),backup_path nvarchar(200)SETbackup_path=D:BackUpDECLARE db_info CURSORLOCALSTATICREAD_ONLYFORWARD_ONLYFOR-根据查询，添加其他筛选条件SELECTnameFROM master.sys.databases WITH(NOLOCK)WHEREdatabase_id4OPEN db_infoFETCH NEXT FROM db_info INTOdbnameWHILEFETCH_STATUS=0beginEXEC http:/www.wend

5、/doc/e0a8b2f6d4d8d15abf234e25.htmlp_BackUp_DataBase dbname,backup_pathFETCH NEXT FROM db_info INTOdbnameENDclose db_infodeallocate db_info-BackUp DataBase End-检查还原磁盘：Use masterGO/*=Check Restore Path Drives Exists=Ken.Guo =2010.9.10 =EXEC Usp_Check_DriveExists RestoreDataPath,ResultCount OU

6、TPUT = */CREATE PROC Usp_Check_DriveExists(RestoreDataPath nvarchar(200),ResultCount int OUTPUT)ASBEGIN-Check Restore Path and Size 1000Mif CHARINDEX(:,RestoreDataPath)0beginDECLAREDrive nvarchar(10),errorinfo nvarchar(500)DECLAREDriveList TABLE(Drive nvarchar(10),DSize bigint)INSERT INTODriveListEX

7、EC master.dbo.xp_fixeddrivesSETDrive=Left(RestoreDataPath,CHARINDEX(:,RestoreDataPath)-1)if not exists(SELECT*FROMDriveListWHEREDrive=DriveAND DSize1024)beginseterrorinfo=N找不到还原磁盘:+Drive+N ，或者磁盘剩余空间小于1GRAISERROR50001errorinfosetResultCount=0returnendendelse if(LEN(RestoreDataPath)1) AND CHARINDEX(:,

8、RestoreDataPath)=0beginseterrorinfo=N还原路径错误:+RestoreDataPath+N，必须包含: 号Raiserror50001errorinfosetResultCount=0returnendsetResultCount=1endGO还原单个数据库：Use masterGO/*=Usp_RestoreDataBaseFormPath= =Restore Single DataBase From a Back File =Ken.Guo =2010.9.10 =Version: 2005 & 2008 SQL Server =Usp_RestoreDa

9、taBaseFormPath D:databackdbcenter.bak,D:Data,0 =Key Point Info: =-Restore HeaderOnly from disk=D:dataxx.bak-Restore FileListOnly from disk=D:dataxx.bak= =*/CREATE PROC Usp_RestoreDataBaseFormPath(DatabBaseBakPath nvarchar(400),RestoreDataPath nvarchar(400)=, -RESTORE DATABASE PATHIsRun smallint=0- 0

10、 PRINT 1 run)ASBEGINset nocount ondeclaredbname nvarchar(200),SQL nvarchar(4000),DirSQL nvarchar(1000),errorinfo nvarchar(30 0)-add path if (RestoreDataPath is not null) and len(RestoreDataPath)1and (right(RestoreDataPath,1)setRestoreDataPath=RestoreDataPath+declarecheckdrive intsetcheckdrive=1exec

11、/doc/e0a8b2f6d4d8d15abf234e25.htmlp_Check_DriveExists RestoreDataPath,checkdrive outputif(checkdriveGoto ExitFLagDECLAREBakFileList TABLE( LogicalName nvarchar(128),PhysicalName nvarchar(260)DECLAREBakHeaderInfo TABLE(DatabaseName nvarchar(128)if Charindex(Microsoft SQL Server

12、 2008,VERSION)0begin-SQL Server 2008DECLAREBakFileList2008TABLE( 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,ReadOnlyL

13、SN 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 INTOBakFil

14、eList2008EXEC sp_executesql NRestore FileListOnly From Disk=DatabBaseBakPath,NDatabBaseBakPath nvarchar(260),DatabBaseBakPathDECLAREBakHeaderInfo2008TABLE(BackupName nvarchar(128),BackupDescription nvarchar(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,POSITION smallint,DeviceT

15、ype 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,BackupFi

16、nishDate 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,RecoveryFork

17、ID 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,ForkPo

18、intLSN 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 INTOBakHeaderInfo2008EXEC sp_executesql NRestore HeaderOnl

19、y From Disk=DatabBaseBakPath,NDatabBaseBakPath nvarchar(260),DatabBaseBakPathinsert intoBakHeaderInfo(DatabaseName)select DatabaseName fromBakHeaderInfo2008insert intoBakFileList(LogicalName ,PhysicalName)select LogicalName ,PhysicalName fromBakFileList2008endelsebegin-SQL Server 2005DECLAREBakFileL

20、ist2005TABLE(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) NUL

21、L,BackupSizeInBytes bigint,SourceBlockSize int,FileGroupID int,LogGroupGUID uniqueidentifier NULL,DifferentialBaseLSN numeric(25,0) NULL,DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit)INSERT INTOBakFileList2005EXEC sp_executesql NRestore FileListOnly From Disk=DatabBaseBakPath,ND

22、atabBaseBakPat h nvarchar(260),DatabBaseBakPathDECLAREBakHeaderInfo2005TABLE(BackupName nvarchar(128),BackupDescription nvarchar(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,POSITION smallint,DeviceType tinyint,UserName nvarchar(128),ServerName nvarchar(128),DatabaseName nvarc

23、har(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,Uni

24、codeComparisonStyle 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,HasBul

25、kLoggedData 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 numer

26、ic(25,0) NULL,DifferentialBaseGUID uniqueidentifier,BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier NULL)INSERT INTOBakHeaderInfo2005EXEC sp_executesql NRestore HeaderOnly From Disk=DatabBaseBakPath,NDatabBaseBakPathnvarchar(260),DatabBaseBakPathinsert intoBakHeaderInfo(DatabaseNam

27、e)select DatabaseName fromBakHeaderInfo2005insert intoBakFileList(LogicalName ,PhysicalName)select LogicalName ,PhysicalName fromBakFileList2005end-Check back file infoif not exists (select1fromBakFileList) OR not exists (select1fromBakHeaderInfo)beginseterrorinfo=N取不到备份文件:+DatabBaseBakPath+N 的信息，请检

28、查备份文件是否正确或者版本是否兼容Raiserror50001errorinfoGoto ExitFLagend-Get DataBase NameSELECT TOP1dbname=databasename FROMBakHeaderInfoif exists (select1from master.sys.databases with(nolock) where name=dbname)beginseterrorinfo=N数据库：+dbname+N已经存在，不能还原Raiserror50001errorinfoGoto ExitFLagendDECLARELogicalName nvar

29、char(200),PhysicalName nvarchar(400),pos int ,endpos int,LastPhysicalName nvarchar(400)DECLARE db_file CURSORLOCALREAD_ONLYFORWARD_ONLYSTATICFORSELECTLogicalName,PhysicalNameFROMBakFileListOPEN db_filesetDirSQL=setSQL=+NRESTORE DATABASE +QUOTENAME(dbname)+ from disk=N+DatabBaseBakPa th+setSQL=SQL+ch

30、ar(13)+Char(10)+N WITH FILE=1 FETCH NEXT FROM db_file INTOLogicalName,PhysicalNameWHILEFETCH_STATUS=0begin-Get DB PhysicalNamesetendpos=0while CHARINDEX(,PhysicalName)0beginsetpos=CHARINDEX(,PhysicalName,endpos)if(pos=0)break;setendpos=pos+1;end-create new db pathif(len(RestoreDataPath)1)beginsetPhy

31、sicalName=RestoreDataPath+dbname+SUBSTRING(PhysicalName,endpos,LE N(PhysicalName)-endpos+1)setDirSQL=NEXEC master.sys.xp_create_subdir N+RestoreDataPath+dbname+ENDelsebeginif len(DirSQL)if(len(DirSQL)setDirSQL=NEXEC master.sys.xp_create_subdir N+SUBSTRING(PhysicalName,1,endpo s-1)+elsesetDirSQL=DirS

32、QL+char(13)+NEXEC master.sys.xp_create_subdir N+SUBSTRING(Phys icalName,1,endpos-1)+-Check Drivessetcheckdrive=1exec /doc/e0a8b2f6d4d8d15abf234e25.htmlp_Check_DriveExists PhysicalName,checkdrive outputif(checkdriveGoto ExitFLagsetLastPhysicalName=SUBSTRING(PhysicalName,1,endpo

33、s-1);ENDsetSQL=SQL+char(13)+Char(10)+N ,Move N+LogicalName+ TO N+PhysicalName+FETCH NEXT FROM db_file INTOLogicalName,PhysicalNameendsetSQL=SQL+char(13)+Char(10)+N ,NOUNLOAD,Recovery,STATS = 10if(IsRun=0)print( DirSQL+char(13)+char(10)+GO+char(13)+Char(10)+SQL+char(13)elsebeginprint(-Begin Restore D

34、atabase:+dbname+-)exec(DirSQL)exec(SQL)print(-End Restore Database:+dbname+-+char(13)endclose db_filedeallocate db_fileExitFLag:set nocount offend批量还原数据库：Use masterGO/*=Usp_RestoreMuiteDataBaseFromPath=Restore Mutite DataBase File From a Path =Ken.Guo =2010.9.10 =Version: 2005 & 2008 SQL Server =EXE

35、C 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)ASBEGINset nocount onDECLAREBackUpFileName nvarchar(200),DbName nvarchar(200),errorinfo nvarchar(400)IF not exists(SELECT1FROM cedures WITH(NOLOCK)WHER