/*******************完整备份作业*******************/ --完整备份,每周一次 USE Master GO declare @str varchar(100) set @str='D:\DBtext\jgj\DBABak\FullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak' BACKUP DATABASE [demo] TO DISK=@str WITH RETAINDAYS=15,NOFORMAT,NOINIT, NAME=N'Demo完整备份',SKIP,NOREWIND, NOUNLOAD,STATS=10 GO /*******************差异备份作业*******************/ --截断日志 USE Master GO BACKUP LOG Demo WITH NO_LOG GO --收缩日志文件 USE Demo GO DBCC SHRINKFILE (N'Demo_log',0,TRUNCATEONLY) GO --差异备份,每天一次 USE Master GO declare @str varchar(100) set @str='D:\DBtext\jgj\DBABak\DiffBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.diff' BACKUP DATABASE [Demo] TO DISK=@str WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT, NAME=N'Demo差异备份',SKIP,NOREWIND, NOUNLOAD,STATS=10 GO /******************日志备份作业*******************/ --日志备份,每小时一次 USE Demo GO declare @str varchar(100) set @str='D:\DBtext\jgj\DBABak\logbak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.trn' BACKUP LOG [Demo] TO DISK=@str WITH RETAINDAYS=3,NOFORMAT,NOINIT, NAME=N'Demo日志备份',SKIP,NOREWIND, NOUNLOAD,STATS=10 GO --删除过期的备份文件,每天两次 declare @str varchar(100),@dir varchar(100),@fileName varchar(30) set @dir='del D:\DBtext\jgj\DBABak\' set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8) set @str=@dir+'fullbak'+@filename+'*.bak' exec xp_cmdshell @str set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8) set @str=@dir+'diffbak'+@filename+'*.diff' exec xp_cmdshell @str set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8) set @str=@dir+'logbak'+@filename+'*.trn' exec xp_cmdshell @str
/**//********************************************************************************* * FielName : backup.sql * Function : 自动备份 * Author : Yahong<[email protected]> * Date : 2005-5-10 2005-5-19 2006-8-1 2007-09-18 * Version : 00 01 02 03 * * Re : * 2006-08-01 增加差异备份和完全备份两种情况,生成多个备份副本 * 2008-09-18 增加备份一个实例中的所有数据库的情况,并在备份后清除日志 * *********************************************************************************/use masterdeclare @DbName varchar(255),@dir varchar(256),@dir_db varchar(256), @verb varchar(256),@cmd varchar(256), @backup_name varchar(256),@dynamic_name varchar(10), @disk_name varchar(256),@copy nvarchar(100), @today datetime,@weekday int--建立网络连接 exec xp_cmdshell 'net use K: /delete' exec xp_cmdshell 'net use I: /delete'exec xp_cmdshell 'net use K: \\193.254.40.118\backup backup /user:Web\backup ' exec xp_cmdshell 'net use I: \\172.16.8.48\databackup backup /user:QA-SERVER-TEST\backup'--设定名字 set @today=getdate() set @dynamic_name=convert(varchar(10),@today,120)set @dir='K:\'+@dynamic_name set @dir_db=@dir+'Database' set @verb='mkdir '--建立目录 set @cmd=@verb+@dir_db exec xp_cmdshell @cmd declare cur_database cursor forward_only read_only for select name from sysdatabases where dbid>4 --系统数据库的dbid<=4open cur_database fetch next from cur_database into @DbNamewhile @@fetch_status=0 begin set @backup_name= @DbName+'_'+@dynamic_name set @disk_name=@dir_db+'\'+@backup_name+'.bak'
set @weekday= datepart(dw,@today) if (@weekday=6) --如果是周五,则进行完全备份 BACKUP DATABASE @DbName TO @backup_name else --其他时候进行差异备份 BACKUP DATABASE @DbName TO @backup_name with differential --清理日志 backup log @DbName with no_log --释放设备 exec sp_dropdevice @backup_name
--复制备份副本到其他地方 set @copy='copy '+@disk_name+' I:' exec xp_cmdshell @copy
--备份下一个数据库 fetch next from cur_database into @DbName end close cur_database deallocate cur_database--删除网络连接 exec xp_cmdshell 'net use K: /delete' exec xp_cmdshell 'net use I: /delete' /**//****************************************************************************** * * File Name : Restore.sql * Function : 数据库还原 * Author : Yahong<[email protected]> * Version : 00 * Date : 2007-09-18 * Re : * *******************************************************************************/ use masterdeclare @DbName varchar(255) --数据库的名字 ,@WholeFileName varchar(255) --完全备份的文件名 ,@DifferentFileName varchar(255) --差异备份的文件名 ,@MasterFileName varchar(255) --数据文件名,注意他们都是逻辑名称 ,@LogFileName varchar(255) --日志文件名 ,@TargetDir varchar(255) --还原后数据库文件所在的路径,如果没有指定该参数, --则必须存在与原数据库相同的路径declare @WholeDeviceName varchar(255) ,@DifferenctDeviceName varchar(255) ,@TargetMasterFileName varchar(255) ,@TargetLogFileName varchar(255)--建立网络链接 exec xp_cmdshell 'net use K: \\172.16.8.48\200709 backup /User:qa-server-test\backup'--在这里设置需要备份的文件等信息 set @DbName='CCTQA' --需要还原的数据库的名字,注意不要搞错了,否则 --覆盖了其他的数据库,可别说我没有提醒你 set @WholeFileName='CCTQA_2007-09-14.bak' --完全备份文件--以下4行如果没有,不要指定,把他们注释掉就行了 set @DifferentFileName='CCTQA_2007-09-17.bak' --最后一次差异备份文件 set @MasterFileName='CCTQA_Data' --数据文件 set @LogFileName='CCTQA_Log' --日志文件 set @TargetDir='D:\CCTQA\Databae' --目标路径--设置目标路径 set @TargetMasterFileName=@TargetDir+'\'+@MasterFileName set @TargetLogFileName=@TargetDir+'\'+@LogFileName--添加还原设备 set @WholeDeviceName=@DbName+'WholeDevice' set @WholeFileName='K:\'+@WholeFileName exec sp_addumpdevice 'disk',@WholeDeviceName,@WholeFileName--开始备份 if(isnull(@DifferentFileName,'')<>'') --如果具有差异备份的还原 begin --添加差异备份还原的设备 set @DifferenctDeviceName=@DbName+'DifferenctDevice' set @DifferentFileName='K:\'+@DifferentFileName exec sp_addumpdevice 'disk',@DifferenctDeviceName,@DifferentFileName --备份 if(isnull(@TargetDir,'')='') restore database @DbName from @WholeDeviceName with NORECOVERY else --如果还原后的数据库文件的路径与备份前的路径不一致 restore database @DbName from @WholeDeviceName with NORECOVERY, move @MasterFileName to @TargetMasterFileName, move @LogFileName to @TargetLogFileName restore database @DbName from @DifferenctDeviceName end else begin --只有完全备份的还原 if(isnull(@TargetDir,'')='') restore database @DbName from @WholeFileName else restore database @DbName from @WholeFileName with move @MasterFileName to @TargetMasterFileName, move @LogFileName to @TargetLogFileName end--释放备份设备 exec sp_dropdevice @WholeDeviceName if(isnull(@DifferentFileName,'')<>'') exec sp_dropdevice @DifferenctDeviceName--删除网络链接 exec xp_cmdshell 'net use K: /delete'/**//************************************************************************ * * File Name : ShrinkLog.sql * Function : 收缩数据库的日志文件 * Author :Yahong<[email protected]> * Version : 00 * Date : 2007-09-16 * Re : * *************************************************************************/-- --第一步:设置需要收缩的数据库,找到需要收缩数据文件 --use cctqa select Size/128 Size,Name from sysfiles/**//*declare @LogName varchar(255),@TargetSize int-- -- --第二步:设置需要收缩的日志文件的逻辑名字和收缩后的大小 --千万不要搞错了,选错了文件,有可能会丢失数据,那时候 --哭都哭不回来了。 -- --set @LogName='CCTQA_Log' set @TargetSize=1declare @str varchar(300), @DatabaseName varchar(255) set @DatabaseName=db_name()if(not exists(select * from sysfiles where name=@LogName)) begin set @str='没有找到日志文件'+@LogName raiserror(@str,0,1) end else begin declare @curSize int,@maxTime int set @maxTime=10 set @curSize=(select size from sysfiles where name=@LogName)/128 print '收缩之前的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB'
while (@curSize>@TargetSize) and (@maxTime>0) begin backup log @DatabaseName with no_log DBCC SHRINKFILE(@LogName,@TargetSize)
set @curSize=(select size from sysfiles where name=@LogName)/128 set @maxTime=@maxTime-1 end
set @curSize=(select size from sysfiles where name=@LogName)/128 print '收缩之后的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB' end */
要用远程数据库可以备份本地。 本地数据库也可以备份到远程。 --备份环境:把数据库服务器(192.168.1.8)的数据库(TEST)备份到(192.168.1.145)的C$下 --首先,做一个与客户端的映射 exec master..xp_cmdshell 'net use z: \\192.168.1.145\c$ "密码" /user:192.168.1.145\administrator' /*--说明: z: 是映射网络路径对应本机的盘符,与下面的备份对应 \\192.168.1.145\c$ 是要映射的网络路径 192.168.1.145\administrator 192.168.1.145是远程的计算机名,administrator是登陆的用户名 密码 上面指定的administrator用户的密码 --*/ --其次,进行数据库备份 backup database TEST to disk='z:\Test.bak' --最后.备份完成后删除映射 exec master..xp_cmdshell 'net use z: /delete' --来自网络 --以下代码放在作业里做调度,自动备份、自动删除4天前备份--创建映射 exec master..xp_cmdshell 'net use w: \DatabaseBackup$ "password"/user:Roy',NO_OUTPUT go -----2000用游标: declare @s nvarchar(200),@del nvarchar(200) select @s='',@del=''declare datebak cursor for select [bak]='backup database '+quotename(Name)+' to disk =''w:'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak'' with init', [del]='exec master..xp_cmdshell '' del w:'+Name+'_'+convert(varchar(8),getdate()-4,112)+'.bak'', no_output' from master..sysdatabases where dbid>4 --不备份系统数据库 open datebakfetch next from datebak into @s,@del while @@fetch_status=0 begin exec (@del) exec(@s) fetch next from datebak into @s,@del end close datebak deallocate datebak go --删除映射 exec master..xp_cmdshell 'net use w: /delete'go
--完整备份,每周一次
USE Master
GO
declare @str varchar(100)
set @str='D:\DBtext\jgj\DBABak\FullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak'
BACKUP DATABASE [demo] TO DISK=@str
WITH RETAINDAYS=15,NOFORMAT,NOINIT,
NAME=N'Demo完整备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO
/*******************差异备份作业*******************/
--截断日志
USE Master
GO
BACKUP LOG Demo WITH NO_LOG
GO
--收缩日志文件
USE Demo
GO
DBCC SHRINKFILE (N'Demo_log',0,TRUNCATEONLY)
GO
--差异备份,每天一次
USE Master
GO
declare @str varchar(100)
set @str='D:\DBtext\jgj\DBABak\DiffBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.diff'
BACKUP DATABASE [Demo] TO DISK=@str
WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,
NAME=N'Demo差异备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO
/******************日志备份作业*******************/
--日志备份,每小时一次
USE Demo
GO
declare @str varchar(100)
set @str='D:\DBtext\jgj\DBABak\logbak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.trn'
BACKUP LOG [Demo] TO DISK=@str
WITH RETAINDAYS=3,NOFORMAT,NOINIT,
NAME=N'Demo日志备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO
--删除过期的备份文件,每天两次
declare @str varchar(100),@dir varchar(100),@fileName varchar(30)
set @dir='del D:\DBtext\jgj\DBABak\'
set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8)
set @str=@dir+'fullbak'+@filename+'*.bak'
exec xp_cmdshell @str
set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8)
set @str=@dir+'diffbak'+@filename+'*.diff'
exec xp_cmdshell @str
set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8)
set @str=@dir+'logbak'+@filename+'*.trn'
exec xp_cmdshell @str
* FielName : backup.sql
* Function : 自动备份
* Author : Yahong<[email protected]>
* Date : 2005-5-10 2005-5-19 2006-8-1 2007-09-18
* Version : 00 01 02 03
*
* Re :
* 2006-08-01 增加差异备份和完全备份两种情况,生成多个备份副本
* 2008-09-18 增加备份一个实例中的所有数据库的情况,并在备份后清除日志
*
*********************************************************************************/use masterdeclare @DbName varchar(255),@dir varchar(256),@dir_db varchar(256),
@verb varchar(256),@cmd varchar(256),
@backup_name varchar(256),@dynamic_name varchar(10),
@disk_name varchar(256),@copy nvarchar(100),
@today datetime,@weekday int--建立网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'exec xp_cmdshell 'net use K: \\193.254.40.118\backup backup /user:Web\backup '
exec xp_cmdshell 'net use I: \\172.16.8.48\databackup backup /user:QA-SERVER-TEST\backup'--设定名字
set @today=getdate()
set @dynamic_name=convert(varchar(10),@today,120)set @dir='K:\'+@dynamic_name
set @dir_db=@dir+'Database'
set @verb='mkdir '--建立目录
set @cmd=@verb+@dir_db
exec xp_cmdshell @cmd
declare cur_database cursor forward_only read_only for
select name from sysdatabases
where dbid>4 --系统数据库的dbid<=4open cur_database fetch next from cur_database
into @DbNamewhile @@fetch_status=0
begin
set @backup_name= @DbName+'_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name+'.bak'
--添加备份设备
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
set @weekday= datepart(dw,@today)
if (@weekday=6) --如果是周五,则进行完全备份
BACKUP DATABASE @DbName TO @backup_name
else --其他时候进行差异备份
BACKUP DATABASE @DbName TO @backup_name with differential --清理日志
backup log @DbName with no_log --释放设备
exec sp_dropdevice @backup_name
--复制备份副本到其他地方
set @copy='copy '+@disk_name+' I:'
exec xp_cmdshell @copy
--备份下一个数据库
fetch next from cur_database
into @DbName
end
close cur_database
deallocate cur_database--删除网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'
/**//******************************************************************************
*
* File Name : Restore.sql
* Function : 数据库还原
* Author : Yahong<[email protected]>
* Version : 00
* Date : 2007-09-18
* Re :
*
*******************************************************************************/
use masterdeclare
@DbName varchar(255) --数据库的名字
,@WholeFileName varchar(255) --完全备份的文件名
,@DifferentFileName varchar(255) --差异备份的文件名
,@MasterFileName varchar(255) --数据文件名,注意他们都是逻辑名称
,@LogFileName varchar(255) --日志文件名
,@TargetDir varchar(255) --还原后数据库文件所在的路径,如果没有指定该参数,
--则必须存在与原数据库相同的路径declare @WholeDeviceName varchar(255)
,@DifferenctDeviceName varchar(255)
,@TargetMasterFileName varchar(255)
,@TargetLogFileName varchar(255)--建立网络链接
exec xp_cmdshell 'net use K: \\172.16.8.48\200709 backup /User:qa-server-test\backup'--在这里设置需要备份的文件等信息
set @DbName='CCTQA' --需要还原的数据库的名字,注意不要搞错了,否则
--覆盖了其他的数据库,可别说我没有提醒你
set @WholeFileName='CCTQA_2007-09-14.bak' --完全备份文件--以下4行如果没有,不要指定,把他们注释掉就行了
set @DifferentFileName='CCTQA_2007-09-17.bak' --最后一次差异备份文件
set @MasterFileName='CCTQA_Data' --数据文件
set @LogFileName='CCTQA_Log' --日志文件
set @TargetDir='D:\CCTQA\Databae' --目标路径--设置目标路径
set @TargetMasterFileName=@TargetDir+'\'+@MasterFileName
set @TargetLogFileName=@TargetDir+'\'+@LogFileName--添加还原设备
set @WholeDeviceName=@DbName+'WholeDevice'
set @WholeFileName='K:\'+@WholeFileName
exec sp_addumpdevice 'disk',@WholeDeviceName,@WholeFileName--开始备份
if(isnull(@DifferentFileName,'')<>'') --如果具有差异备份的还原
begin
--添加差异备份还原的设备
set @DifferenctDeviceName=@DbName+'DifferenctDevice'
set @DifferentFileName='K:\'+@DifferentFileName
exec sp_addumpdevice 'disk',@DifferenctDeviceName,@DifferentFileName --备份
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeDeviceName
with NORECOVERY
else --如果还原后的数据库文件的路径与备份前的路径不一致
restore database @DbName from @WholeDeviceName
with NORECOVERY,
move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
restore database @DbName from @DifferenctDeviceName
end
else
begin --只有完全备份的还原
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeFileName
else
restore database @DbName from @WholeFileName
with move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
end--释放备份设备
exec sp_dropdevice @WholeDeviceName
if(isnull(@DifferentFileName,'')<>'')
exec sp_dropdevice @DifferenctDeviceName--删除网络链接
exec xp_cmdshell 'net use K: /delete'/**//************************************************************************
*
* File Name : ShrinkLog.sql
* Function : 收缩数据库的日志文件
* Author :Yahong<[email protected]>
* Version : 00
* Date : 2007-09-16
* Re :
*
*************************************************************************/--
--第一步:设置需要收缩的数据库,找到需要收缩数据文件
--use cctqa
select Size/128 Size,Name from sysfiles/**//*declare @LogName varchar(255),@TargetSize int--
--
--第二步:设置需要收缩的日志文件的逻辑名字和收缩后的大小
--千万不要搞错了,选错了文件,有可能会丢失数据,那时候
--哭都哭不回来了。
--
--set @LogName='CCTQA_Log'
set @TargetSize=1declare @str varchar(300), @DatabaseName varchar(255)
set @DatabaseName=db_name()if(not exists(select * from sysfiles where name=@LogName))
begin
set @str='没有找到日志文件'+@LogName
raiserror(@str,0,1)
end else
begin
declare @curSize int,@maxTime int
set @maxTime=10
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收缩之前的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB'
while (@curSize>@TargetSize) and (@maxTime>0)
begin
backup log @DatabaseName with no_log
DBCC SHRINKFILE(@LogName,@TargetSize)
set @curSize=(select size from sysfiles where name=@LogName)/128
set @maxTime=@maxTime-1
end
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收缩之后的日志文件的大小是:'+cast(@curSize as varchar(10))+'MB'
end
*/
本地数据库也可以备份到远程。 --备份环境:把数据库服务器(192.168.1.8)的数据库(TEST)备份到(192.168.1.145)的C$下 --首先,做一个与客户端的映射
exec master..xp_cmdshell
'net use z: \\192.168.1.145\c$ "密码" /user:192.168.1.145\administrator'
/*--说明:
z: 是映射网络路径对应本机的盘符,与下面的备份对应
\\192.168.1.145\c$ 是要映射的网络路径
192.168.1.145\administrator
192.168.1.145是远程的计算机名,administrator是登陆的用户名
密码 上面指定的administrator用户的密码
--*/
--其次,进行数据库备份
backup database TEST to disk='z:\Test.bak' --最后.备份完成后删除映射
exec master..xp_cmdshell 'net use z: /delete'
--来自网络
--以下代码放在作业里做调度,自动备份、自动删除4天前备份--创建映射
exec master..xp_cmdshell 'net use w: \DatabaseBackup$ "password"/user:Roy',NO_OUTPUT
go
-----2000用游标:
declare @s nvarchar(200),@del nvarchar(200)
select @s='',@del=''declare datebak cursor for
select
[bak]='backup database '+quotename(Name)+' to disk =''w:'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak'' with init',
[del]='exec master..xp_cmdshell '' del w:'+Name+'_'+convert(varchar(8),getdate()-4,112)+'.bak'', no_output'
from master..sysdatabases where dbid>4 --不备份系统数据库
open datebakfetch next from datebak into @s,@del
while @@fetch_status=0
begin
exec (@del)
exec(@s)
fetch next from datebak into @s,@del
end
close datebak
deallocate datebak
go
--删除映射
exec master..xp_cmdshell 'net use w: /delete'go
BACKUP DATABASE DBName TO DISK='盘符:\路径\备份文件名.bak'
RESTORE FILELISTONLY FROM DISK='盘符:\路径\备份文件名.bak' 更多参考:http://blog.csdn.net/htl258/category/583021.aspx