/*******************完整备份作业*******************/
--完整备份,每周一次
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

------解决方案--------------------
SQL code

-- 建文件backup.sql, 内容:
backup database [数据库名] to disk='[备份文件名]' with format,init

-- 建批处理文件auto_backup.bat, 内容:
sqlcmd -S [服务器名] -U sa -P [密码] -i [backup.sql]

-- 建windows计划任务, 定期调用执行auto_backup.bat

------解决方案--------------------
探讨

sql server 2005 express 的数据库要求每天做数据备份，但是安装的也不完整的，不能直接做备份，想用bat写个批处理文件后，在做个计划任务，请教那个bat文件怎么写呢？


------解决方案--------------------

SQL code

-- 建文件backup.sql, 内容:
USE pcglxt
GO
declare @str varchar(100)
set @str='E:\databak\pcglxt'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak'
BACKUP DATABASE [pcglxt] TO DISK=@str
WITH RETAINDAYS=15,NOFORMAT,NOINIT,
NAME=N'pcglxt完整备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO

-- 建批处理文件auto_backup.bat, 内容:
sqlcmd -S [服务器名] -U sa -P [密码] -i [backup.sql]

-- 建windows计划任务, 定期调用执行auto_backup.bat