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 12»»

Adding date timestamp to a backup file name Expand / Collapse
Author
Message
Posted Tuesday, April 17, 2007 11:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 19, 2014 9:38 AM
Points: 92, Visits: 240

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

Post #359034
Posted Tuesday, April 17, 2007 11:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 23,270, Visits: 31,987

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.




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)
Post #359041
Posted Tuesday, April 17, 2007 12:03 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841

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
Post #359046
Posted Tuesday, April 17, 2007 12:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 23,270, Visits: 31,987

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.




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)
Post #359048
Posted Tuesday, April 17, 2007 12:11 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841

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
Post #359050
Posted Tuesday, April 17, 2007 2:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 19, 2014 9:38 AM
Points: 92, Visits: 240

Thanks for the replies.

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

Post #359082
Posted Tuesday, April 17, 2007 4:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 23,270, Visits: 31,987

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




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)
Post #359117
Posted Wednesday, April 18, 2007 5:38 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:52 PM
Points: 5,988, Visits: 12,923

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



---------------------------------------------------------------------

Post #359190
Posted Wednesday, April 18, 2007 6:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:58 AM
Points: 1,297, Visits: 765

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


 

Post #359204
Posted Wednesday, April 18, 2007 6:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 23,270, Visits: 31,987

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




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)
Post #359216
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse