[分享]数据库备份脚本/对指定文件夹下数据库全备生成恢复脚本 100perfectaction]

发布时间:2016-12-9 23:28:57 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[分享]数据库备份脚本/对指定文件夹下数据库全备生成恢复脚本 100perfectaction]",主要涉及到[分享]数据库备份脚本/对指定文件夹下数据库全备生成恢复脚本 100perfectaction]方面的内容,对于[分享]数据库备份脚本/对指定文件夹下数据库全备生成恢复脚本 100perfectaction]感兴趣的同学可以参考一下。

写这三个脚本是因为: 1.在每台db server上配置备份时,可以写个job,然后执行统一脚本备份,方便规范 2.在做大量数据库迁移或升级时,可以方便生成恢复用的sql --sql2008备份T-sql 版本(支持sql2005) --此版本目前未提供删除指定日期过期备份功能 /* author:perfectaction @bak_path='D:\dbbak\'    --备份路径; @type=3                  --设置需要备份的库0为全部库1为系统库2为全部用户库3为指定库; @dbnames='pub,trace'     --需要备份的数据库,用,隔开,[email protected]=3时生效 @DBsingleFolder=1        --是否为每个库设置不同的文件夹,否,是; @baktype                 --备份类型0为全备,1为差异备,2为日志备份 */ set nocount on declare @bak_sql nvarchar(max),@del_sql nvarchar(max),@bak_path nvarchar(max),@baktype int declare @type int,@dbnames nvarchar(max),@overdueDay int,@DBsingleFolder int declare @dirtree table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int) declare @createfolder nvarchar(max),@delbackupfile nvarchar(max) -------以下为备份参数,可以修改------- select  @bak_path='D:\dbbak\' ,@type=1 ,@dbnames='TraceDB,db_01' ,@DBsingleFolder=1 ,@baktype=0 -------以上为备份参数,可以修改------ if right(isnull(@bak_path,''),1)!='\' set @[email protected]_path+'\' if isnull(@dbnames,'')!='' set @dbnames = ',[email protected]+',' declare @t table(id int identity(1,1) primary key,name nvarchar(max)) declare @sql nvarchar(max) set @sql = 'select name from sys.databases where name!=''tempdb'' and '+  case @type when 0 then '1=1' when 1 then 'database_id<=4' when 2 then 'database_id>4' when 3 then 'charindex('',''+Name+'','',[email protected]+''')>0' else '1>2' end insert into @t exec(@sql) select @bak_sql=isnull(@bak_sql,'')+char(13)+'backup database '+quotename(Name)+' to [email protected]_path+ case when @DBsingleFolder=0 then '' else Name+'\' end+ Name+ case when @baktype=0 then '_Full_' when @baktype=1 then '_Diff_'  when @baktype=2 then '_Log_' else null end+ replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+ case when @baktype=2 then '.trn' else '.bak' end +'''' from @t  insert into @dirtree exec('master.dbo.xp_dirtree [email protected]_path+''',0,1') select @createfolder=isnull(@createfolder,'')+'exec master.dbo.xp_cmdshell ''md [email protected]_path+''+name+''',no_output ' +case when @DBsingleFolder=0 then null else '' end+char(13) from @t as a left join @dirtree as b on a.name=b.subdirectory and b.files=0 and depth=1 where  b.id is null exec(@createfolder)  exec(@bak_sql) /* 如指定参数如下: select  @bak_path='D:\dbbak\' ,@type=1 ,@dbnames='TraceDB,db_01' ,@DBsingleFolder=1 ,@baktype=0 已在D:\dbbak\建立了三个文件夹,并在各文件夹生成了备份 产生消息: 已为数据库 'master',文件 'master' (位于文件 1 上)处理了 51752 页。 已为数据库 'master',文件 'mastlog' (位于文件 1 上)处理了 2 页。 BACKUP DATABASE 成功处理了 51754 页,花费 31.976 秒(13.258 MB/秒)。 已为数据库 'model',文件 'modeldev' (位于文件 1 上)处理了 176 页。 已为数据库 'model',文件 'modellog' (位于文件 1 上)处理了 2 页。 BACKUP DATABASE 成功处理了 178 页,花费 0.293 秒(4.962 MB/秒)。 已为数据库 'msdb',文件 'MSDBData' (位于文件 1 上)处理了 1040 页。 已为数据库 'msdb',文件 'MSDBLog' (位于文件 1 上)处理了 1 页。 BACKUP DATABASE 成功处理了 1041 页,花费 1.283 秒(6.646 MB/秒)。 */ /* --如果xp_cmdshell禁用,使用以下sql开启 exec sp_configure 'show advanced options', 1 go reconfigure go exec sp_configure 'xp_cmdshell', 1  go exec sp_configure 'show advanced options', 0 go reconfigure go */ ----对指定目录下的全部全备生成恢复数据库sql ----此版本sql只支持在sql server 2008下执行,但可以对sql2000/sql2005备份进行操作 ----author:perfectaction declare @pth_bak varchar(1000),@pth_data varchar(500),@pth_log varchar(500),@dbname varchar(256) --备份文件路径 set @pth_bak='d:\DBBak\db_test\db_test_Full_20090309_212938.bak' --还原后的数据库名 set @dbname = 'db_test' --还原后的数据文件路径 set @pth_data ='W:\Data\' --还原后的日志文件路径 set @pth_log ='X:\Log\' declare @t table(id int identity(1,1) primary key, LogicalName varchar(256),PhysicalName varchar(1000),Type varchar(2), FileGroupName varchar(256),Size bigint,MaxSize bigint,FileId int,reateLSN varchar(100), DropLSN varchar(100),UniqueId varchar(100),ReadOnlyLSN varchar(100),ReadWriteLSN varchar(100), BackupSizeInBytes bigint,SourceBlockSize bigint,FileGroupId int,LogGroupGUID varchar(300), DifferentialBaseLSN varchar(100),DifferentialBaseGUID varchar(300),IsReadOnly varchar(2), IsPresent varchar(2),TDEThumbprint varchar(100)) insert into @t exec('restore filelistonly from disk= [email protected]_bak+'''') declare @i int set @i=1 declare @LogicalName varchar(256),@PhysicalName varchar(1000),@Type varchar(2) print 'restore database '+ @dbname +' ' print 'from disk= '''+ @pth_bak +''' ' print 'with ' while @i<=(select MAX(id) from @t) begin select @LogicalName=LogicalName,@PhysicalName=PhysicalName,@Type=Type from @t where [email protected] if rtrim(upper(ISNULL(@Type,'')))='D' print 'move '''+ @LogicalName + ''' to '''  + @pth_data+reverse(left(reverse(@PhysicalName),charindex('\',reverse(@PhysicalName))-1))+''',' else print 'move '''+ @LogicalName + ''' to ''' + @pth_log+reverse(left(reverse(@PhysicalName),charindex('\',reverse(@PhysicalName))-1))+''',' set @i = @i + 1 end print 'recovery' /* restore database db_test  from disk= 'D:\dbbak\db_test\db_test_Full_20090309_212938.bak'  with  move 'db_test' to 'W:\Data\db_test.mdf', move 'db_test_1' to 'W:\Data\db_test_1.ndf', move 'db_test_2' to 'W:\Data\db_test_2.ndf', move 'db_test_3' to 'W:\Data\db_test_3.ndf', move 'db_test_4' to 'W:\Data\db_test_4.ndf', move 'db_test_5' to 'W:\Data\db_test_5.ndf', move 'db_test_6' to 'W:\Data\db_test_6.ndf', move 'db_test_7' to 'W:\Data\db_test_7.ndf', move 'db_test_log' to 'X:\Log\db_test_log.ldf', recovery go */ 如有问题,请指正。

----对指定目录下的全部全备生成恢复数据库sql ----此版本sql只支持在sql server 2008执行(但可以对sql2000/sql2005备份进行操作),只支持指定目录,未支持子目录 ----author:perfectaction set nocount on declare @pth_bak varchar(1000),@pth_data varchar(500),@pth_log varchar(500) declare @dbname varchar(256),@filetype nvarchar(10),@i int,@ii int declare @LogicalName varchar(256),@PhysicalName varchar(1000),@Type varchar(2) set @pth_bak='D:\DBBak\'     --备份文件所在路径 set @filetype = N'.bak' --备份文件的后缀 set @pth_data ='W:\Data\' --还原后的数据文件路径 set @pth_log ='X:\Log\' --还原后的日志文件路径 set @i = 1 set @ii=1 declare @t_1 table(id int identity(1,1) primary key,subdirectory varchar(500),depth int,files int) declare @t_2 table(id int identity(1,1) primary key,subdirectory varchar(500),depth int,files int) declare @t_3 table(BackupName varchar(500),BackupDescription varchar(500),BackupType varchar(500),ExpirationDate varchar(500), Compressed varchar(500),Position varchar(500),DeviceType varchar(500),UserName varchar(500),ServerName varchar(500), DatabaseName varchar(500),DatabaseVersion varchar(500),DatabaseCreationDate varchar(500),BackupSize varchar(500), FirstLSN varchar(500),LastLSN varchar(500),CheckpointLSN varchar(500),DatabaseBackupLSN varchar(500),BackupStartDate varchar(500), BackupFinishDate varchar(500),SortOrder varchar(500),CodePage varchar(500),UnicodeLocaleId varchar(500), UnicodeComparisonStyle varchar(500),CompatibilityLevel varchar(500),SoftwareVendorId varchar(500),SoftwareVersionMajor varchar(500), SoftwareVersionMinor varchar(500),SoftwareVersionBuild varchar(500),MachineName varchar(500),Flags varchar(500),BindingID varchar(500), RecoveryForkID varchar(500),Collation varchar(500),FamilyGUID varchar(500),HasBulkLoggedData varchar(500),IsSnapshot varchar(500), IsReadOnly varchar(500),IsSingleUser varchar(500),HasBackupChecksums varchar(500),IsDamaged varchar(500),BeginsLogChain varchar(500), HasIncompleteMetaData varchar(500),IsForceOffline varchar(500),IsCopyOnly varchar(500),FirstRecoveryForkID varchar(500), ForkPointLSN varchar(500),RecoveryModel varchar(500),DifferentialBaseLSN varchar(500),DifferentialBaseGUID varchar(500), BackupTypeDescription varchar(500),BackupSetGUID varchar(500),CompressedBackupSize varchar(500)) declare @t_4 table(id int identity(1,1) primary key, LogicalName varchar(256),PhysicalName varchar(1000),Type varchar(2), FileGroupName varchar(256),Size bigint,MaxSize bigint,FileId int,reateLSN varchar(100), DropLSN varchar(100),UniqueId varchar(100),ReadOnlyLSN varchar(100),ReadWriteLSN varchar(100), BackupSizeInBytes bigint,SourceBlockSize bigint,FileGroupId int,LogGroupGUID varchar(300), DifferentialBaseLSN varchar(100),DifferentialBaseGUID varchar(300),IsReadOnly varchar(2), IsPresent varchar(2),TDEThumbprint varchar(100)) insert into @t_1 exec ('exec master..xp_dirtree [email protected]_bak+''',0,1') insert into @t_2(subdirectory,depth,files) select subdirectory,depth,files from @t_1 where files=1 and right(subdirectory,4)[email protected] declare @subdirectory varchar(500) while @i <=(select MAX(id) from @t_2) begin select @ii = isnull(MAX(id)+1,1) from @t_4 delete from @t_3 delete from @t_4 select @subdirectory=subdirectory from @t_2 where [email protected] insert into @t_3 exec('restore headeronly from [email protected][email protected]+'''') select top 1 @dbname=DatabaseName from @t_3  insert into @t_4 exec('restore filelistonly from disk= [email protected][email protected]+'''') print 'restore database '+ @dbname +' ' print 'from disk= '''+ @pth_bak [email protected]+''' ' print 'with ' while @ii<=(select MAX(id) from @t_4) begin select @LogicalName=LogicalName,@PhysicalName=PhysicalName,@Type=Type from @t_4 where [email protected] if rtrim(upper(ISNULL(@Type,'')))='D' print 'move '''+ @LogicalName + ''' to '''  + @pth_data+reverse(left(reverse(@PhysicalName),charindex('\',reverse(@PhysicalName))-1))+''',' else print 'move '''+ @LogicalName + ''' to ''' + @pth_log+reverse(left(reverse(@PhysicalName),charindex('\',reverse(@PhysicalName))-1))+''',' set @ii = @ii + 1 end print 'recovery' print 'go' print '------------------------------------------------------' select @i = @i + 1 end --执行结果: /* restore database msdb  from disk= 'D:\dbbak\msdb_backup_2009_02_28_090419_0528336.bak'  with  move 'MSDBData' to 'W:\Data\MSDBData.mdf', move 'MSDBLog' to 'X:\Log\MSDBLog.ldf', recovery go ------------------------------------------------------ restore database db_test  from disk= 'D:\dbbak\db_test.bak'  with  move 'db_test' to 'W:\Data\db_test.mdf', move 'db_test_1' to 'W:\Data\db_test_1.ndf', move 'db_test_2' to 'W:\Data\db_test_2.ndf', move 'db_test_3' to 'W:\Data\db_test_3.ndf', move 'db_test_4' to 'W:\Data\db_test_4.ndf', move 'db_test_5' to 'W:\Data\db_test_5.ndf', move 'db_test_6' to 'W:\Data\db_test_6.ndf', move 'db_test_7' to 'W:\Data\db_test_7.ndf', move 'db_test_log' to 'X:\Log\db_test_log.ldf', recovery go */

第二个应该是: ----对指定目录下的指定全备生成恢复数据库sql

学习

好东东,谢[完美行动 化茧成蝶],收藏!

美美的贴要顶

大叔厉害

楼主好牛啊,学习!

謝謝共享.收藏.

learn

帮顶。

declare好长……

study...

学习

学习

study

ding

引用 6 楼 wzy_love_sly 的回复:大叔厉害 ...

up

顶,支持美美

太好了  非常非常感谢

如果 在sql2008 下 批量还原日志 该如何写脚本,日志间隔是30分钟一次 

新人,学习先。

上一篇:[分享]当您查询通过 SQL Server 2008 中使用 ORDER BY 子句的视图时, 结果仍中返回随机顺序的补丁 20perfectaction]
下一篇:商业银行BI应用现状与案例分析 10fredrickhu]

相关文章

相关评论