backup file with datetime stamp

Question

Post reply

backup file with datetime stamp

SqlSavy

SSCarpal Tunnel

Points: 4065
More actions
October 28, 2013 at 10:04 am

#395731

How to create backup files with datatime stamps this is to retain 2 days backups on the disk
BACKUP 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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

DECLARE @sqlcmd nvarchar(4000)

SELECT @sqlcmd = '

BACKUP DATABASE [DBNAME] TO

DISK = N''D:\BACKUPs\DBNAME\DBNAME_1_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_2_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_3_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_4_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_5_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_6_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_7_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak'',

DISK = N''D:\BACKUPs\DBNAME\DBNAME_8_' + CAST(CAST(getdate() AS DATE) AS NVARCHAR(100)) + '.bak''

WITH RETAINDAYS = 2,NOFORMAT, NOINIT,

NAME = N''DBNAME-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

GO'

--PRINT @sqlcmd

EXEC (@sqlcmd)

MCITP SQL 2005, MCSA SQL 2012

SqlSavy SSCarpal Tunnel Points: 4065 More actions · Answer 2

SqlSavy

SSCarpal Tunnel

Points: 4065

October 29, 2013 at 5:33 am

#1662292

Thanks a lot 🙂

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

In addition, attached script also can be handy for backup and restore. However little modification is required as per the requirement. This is for log backup you can also use it for full backup.

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/71918/

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/72321/

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Sqlsavy (10/28/2013)
How to create backup files with datatime stamps this is to retain 2 days backups on the disk
BACKUP 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SqlSavy SSCarpal Tunnel Points: 4065 More actions · Answer 5

Is it possible to delete old backups using SQL backup script with RETAINDAYS ?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Sqlsavy (11/4/2013)
Is it possible to delete old backups using SQL backup script with RETAINDAYS ?

No and apologies for the confusion on that.

Normally when I see folks with a RETAINDAYS limit set during the backup creation, they also have something that automatically deletes data sometimes even before a new and viable backup has been made. That's what I was warning against and failed to be clear on that subject.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)