Sqlsavy (10/28/2013)
How to create backup files with datatime stamps this is to retain 2 days backups on the diskBACKUP DATABASE [DBNAME] TO
DISK = N'D:\BACKUPs\DBNAME\DBNAME_1.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_2.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_3.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_4.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_5.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_6.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_7.bak',
DISK = N'D:\BACKUPs\DBNAME\DBNAME_8.bak'
WITH RETAINDAYS = 2,NOFORMAT, NOINIT,
NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
Thanks!
I can't tell you when it will happen nor what the circumstances will be but there will come a day when you find out that it's a real mistake to automatically delete backup files automatically or even by date. I strongly recommend keeping at least 1 backup file for each of two days even if those files are a week old. For example, we went through a move from one building to another. The servers were down from 5PM on a Friday night 'til 5AM the following Monday morning. If we had a maintance plan or automatic deletes that simply deleted anything older than two days and had a backup failure on a corrupt database, we would have had to spend the day recalling a backup tape or two from offsite remote storage.
--Jeff Moden
Change is inevitable... Change for the better is not.