SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding date timestamp to a backup file name


Adding date timestamp to a backup file name

Author
Message
rocky
rocky
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 246

I have a scheduled job that takes a backup using t-sql (it's not a maint plan job). How can I automatically add the date timestamp to the end of the backup file?

BACKUP DATABASE [TESTDB] TO DISK = N'C:\TESTDB\TESTDB_db_????????????.BAK'
WITH NOINIT ,
NOUNLOAD ,
DIFFERENTIAL ,
NAME = N'TESTDB backup',
SKIP ,
STATS = 10,
NOFORMAT


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95445 Visits: 38968

declare @backupfile nvarchar(2000)

set @backupfile = N'C:\TESTDB\TESTDB_db_' + replace(convert(nvarchar(50), getdate(), 120), ' ',':') + N'.BAK'

BACKUP DATABASE [TESTDB] TO DISK = @backupfile ...

I think. Give it a try, I wrote this off the top of my head.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
John Rowan
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13714 Visits: 4588

Or, if you want to remove the dashes as well:

DECLARE @BackupFileName varchar(100)

SET @BackupFileName = 'C:\temp\TestDB_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.BAK'

BACKUP DATABASE TestDB
TO DISK=@BackupFileName



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95445 Visits: 38968

John, True. Like I said, wrote it off the top of my head. At least I was trying to get read of the space in the datetime stamp. I don't like spaces in filenames if I can help it.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
John Rowan
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13714 Visits: 4588

Lynn, your version works perfect too. It's just a matter of preference (and the fact that I had already had mine ready to post and your post beat me to it). I figured that I would post anyhow just for variety's sake....



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
rocky
rocky
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 246

Thanks for the replies.

I don't need the seconds. How can I do just date, hour, minute.


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95445 Visits: 38968

Check BOL, CAST AND CONVERT. You will find the different format codes for converting dates to character strings there.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24790 Visits: 13698

try this:

set quoted_identifier off
declare @statement nvarchar(255)

select @statement = 'BACKUP database [dbname] to disk = '+"'" +
'\\servername\E$\fullpath\dbname_db_' +
CONVERT(varchar(4), datepart(yy, getdate()))+
right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)+
right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2)+
right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+
right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+
'.bak'+"'"

exec sp_executesql @statement

replacing with your desired server and pathname



---------------------------------------------------------------------
Anders Pedersen
Anders Pedersen
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3268 Visits: 902

The following gives you excatly the name as SQL would using maintenance plan. Does it for all databases so I do not have to create a new one when I add a database. And yes cursors are bad, but for this number of records, pfft! This is also set to do the backups to a subdirectoy for each database, and create that subdirectory if it does not work. I also have a seperate script to clean out those subdirectores. And yes, I don't use the maintenance plan wizards, and thank god for that after the last problems with SQL2005.

(note that this is for SQL2005 and will need minor adjustments to work with SQL2000)


set quoted_identifier off
go

declare @SQL varchar(max)
declare @runtime varchar(12)
select @runtime = convert(varchar(20),getdate(),112) + right('00' +convert(varchar(2),datepart(hh, getdate())),2) +right('00' +convert(varchar(2),datepart(mi, getdate())),2)

DECLARE DBNames CURSOR
READ_ONLY
FOR select name from sys.databases
where name not in ('anderstest' , 'tblCheck', 'Northwind', 'tempdb')
order by name

DECLARE @name sysname
OPEN DBNames

FETCH NEXT FROM DBNames INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @SQL = "EXECUTE master.dbo.xp_create_subdir N'M:\Backups\" + @name +"'"
exec (@SQL)

select @SQL = "Backup database [" + @name + "] TO DISK = N'M:\Backups\" + @name + "\" + @name +"_backup_" + @runtime + ".bak'" +
" WITH NOFORMAT, NOINIT,SKIP, REWIND, NOUNLOAD, STATS = 100"
exec (@SQL)
END
FETCH NEXT FROM DBNames INTO @name
END

CLOSE DBNames
DEALLOCATE DBNames
GO



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95445 Visits: 38968

Cursors are not bad, you just have to use them appropriately.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search