February 4, 2005 at 10:33 pm
I need to create a script that will do a full backup of one database once a day and a tran log of the databse three times a day. I would also like to keep only three days worth of both.
Can anyone point me to a good script I can start with...
Thanks...
February 4, 2005 at 10:42 pm
Check out the database maintenance plan wizard that comes with Enterprise Manager. It can easily do what you need.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 4, 2005 at 10:48 pm
Thanks, I did but it didn't help. I need to build this into an Install Shield Program to run for different servers, for databse names, and to different directories depending on questions aswered in my installation program.
Thanks in advance if anyone knows of anything..
February 5, 2005 at 7:57 pm
Here is a script to get you started. You will have to also write a script to set up the jobs. Probably the best way to get a sample is to script out an existing job. Right click on an existing job in EM and select "All Task" -> "Generate Scipt".
This proc assumes that the parent folder exists and could use some more error checking:
create proc usp_BackupPlan @dbName varchar(50), @buType char(1), @folder nvarchar(150) as
declare @dateStamp nvarchar(20)
declare @date dateTime
declare @fileName nvarchar(250)
declare @command varchar(1000)
declare @ret int
declare @folderOK char(1)
declare @errors varchar(4000)
declare @buDesc nvarchar(150)
declare @oldFile varchar(250)
if right(@folder,1) <> '\' set @folder = @folder + '\'
--create timestamp for backup name
set @date = getDate()
set @dateStamp = convert(varchar,@date,120)
set @dateStamp = replace(@dateStamp,':','')
set @dateStamp = replace(@dateStamp,' ','')
set @dateStamp = replace(@dateStamp,'-','')
print @dateStamp
-- build file name
set @fileName = @dbName + '_' + @dateStamp
-- build folder if it doesn't exits
set @command = ' dir ' + @folder
exec @ret = master.dbo.xp_cmdshell @command
if @ret <> 0
begin
set @command = 'mkdir ' + @folder
exec @ret = master.dbo.xp_cmdshell @command
if (@ret <> 0)
set @errors = @errors + 'could not find location ' + @folder + '| '
else
set @folderOK = 'Y'
end
else
set @folderOK = 'Y'
if @folderOK = 'Y'
begin
--perform backup
if upper(@buType) = 'F'
begin
set @buDesc = N'Full ' + @FileName
set @fileName = @folder + @fileName + N'.bak'
BACKUP DATABASE @dbName TO DISK = @fileName WITH INIT , NAME = @buDesc
end
else
begin
set @buDesc = N'TX ' + @fileName
set @fileName = @folder + @fileName + '.trn'
BACKUP LOG @dbName TO DISK = @fileName WITH INIT, NAME = @buDesc
end
--delete old files
declare cOldFile cursor for select bmf.physical_device_name
from msdb.dbo.backupmediafamily bmf
inner join msdb.dbo.backupset bs
on bs.media_set_id = bmf.media_set_id
where bs.backup_finish_date between dateadd(d,-4,@date) and dateadd(d,-3,@date)
and bs.database_name = @dbName
open cOldFile
fetch next from coldFile into @oldFile
while @@fetch_status = 0
begin
set @command = 'del ' + @oldFile
exec master.dbo.xp_cmdshell @command
fetch next from coldFile into @oldFile
end
close cOldFile
deallocate cOldFile
end
print @errors
return
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 7, 2005 at 6:00 am
Thanks Kathi...
It looks like a winner!!!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply