October 6, 2009 at 12:53 pm
i am creating a backup job for a database.
scheduled once everyday.
i want to keep one week worth of backup in the backup folder(k:\backup).
i am using step command below.but its not working
backup database xxxx to disk = 'K:\backup\xxxx.bak'
can you pls provide the command.
October 6, 2009 at 12:59 pm
Can you provide any errors from the SQL Logs or Windows Event Logs that appear when the step is attempted?
Is the 'K:\' drive a local disk or network mapped drive?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 6, 2009 at 1:00 pm
You say you want to store one weeks of daily backups. So how do you make sure that you are not overwriting the existing file. What is the error you are getting? You can find the full details of how to back up in Books on Line. Just check for BACKUP DATABASE Statement in BOL.
-Roy
October 6, 2009 at 2:24 pm
Try xp_delete_file. Alternatively, you can write a CLR.
Read the article and comments for usage but something like this should work for you. Refer also to maintenance plans which is also discussed in the article.
EXECUTE master.dbo.xp_delete_file 0,N'E:\Database_Backups',N'bak', -7,1
This is to delete backup files (0) from the E:\Database_Backups directory, with the file extension of bak that are older than 7 days and do it recursively (1).
Let me know if the -7 does not work for you and I'll check what we have at work. I believe you can also pass a specific date such as dateadd(dd, -7, getdate()) via a variable.
http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx
October 6, 2009 at 5:10 pm
i have done this through maintananceplan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply