Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

backup file with datetime stamp Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
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!
Post #1509003
Posted Monday, October 28, 2013 10:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 316, Visits: 591
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
Post #1509040
Posted Tuesday, October 29, 2013 5:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
Thanks a lot :)
Post #1509284
Posted Wednesday, October 30, 2013 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1509775
Posted Wednesday, October 30, 2013 6:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 35,609, Visits: 32,200
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509993
Posted Monday, November 4, 2013 8:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
Is it possible to delete old backups using SQL backup script with RETAINDAYS ?
Post #1511150
Posted Monday, November 4, 2013 10:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 35,609, Visits: 32,200
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511203
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse