-----------------------------批量备份数据-------------------------------------------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 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) ENDGOUse masterGO/*=============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>4OPEN db_infoFETCH NEXT FROM db_info INTO @dbnameWHILE @@FETCH_STATUS=0 begin EXEC master.dbo.Usp_BackUp_DataBase @dbname,@backup_path FETCH NEXT FROM db_info INTO @dbname ENDclose 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 OUTPUT ====== ===========================================================================*/CREATE PROC Usp_Check_DriveExists( @RestoreDataPath nvarchar(200) ,@ResultCount int OUTPUT) ASBEGIN--Check Restore Path and Size >1000Mif 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 endelse if(LEN(@RestoreDataPath)>1) AND CHARINDEX(':',@RestoreDataPath)=0 begin set @errorinfo=N'还原路径错误:'+@RestoreDataPath+N'，必须包含":" 号' Raiserror 50001 @errorinfo set @ResultCount= 0 return end set @ResultCount= 1endGO Use masterGO/*=================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 ) ASBEGINset nocount ondeclare @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 intset @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 endelse 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 infoif 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 NameSELECT TOP 1 @dbname=databasename FROM @BakHeaderInfoif exists (select 1 from master.sys.databases with(nolock) where name=@dbname) begin set @errorinfo=N'数据库：'+@dbname+N'已经存在，不能还原' Raiserror 50001 @errorinfo Goto ExitFLag endDECLARE @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 @BakFileListOPEN db_fileset @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,@PhysicalNameWHILE @@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_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 ====== =====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 ) ASBEGINset nocount onDECLARE @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 >1000MDECLARE @checkdrive intSET @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 ,1DELETE FROM @Dir WHERE charindex('.bak',BackDBFileName)=0if not exists (select top 1 1 from @Dir) begin Raiserror 50001 N'在提供的路径下没有找到合符要求的备份文件' Goto ExitFLag enddeclare db_file Cursor Local Static Read_Only Forward_Onlyforselect BackDBFileName from @DirOpen db_fileFetch Next from db_file into @BackUpFileNamewhile @@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 endClose db_filedeallocate db_fileExitFLag:set nocount offend
注：非原创，忘记该脚本原始出处