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
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.
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 TestDBTO DISK=@BackupFileName
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, 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....
Thanks for the replies.
I don't need the seconds. How can I do just date, hour, minute.
Check BOL, CAST AND CONVERT. You will find the different format codes for converting dates to character strings there.
set quoted_identifier offdeclare @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
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 offgo
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 CURSORREAD_ONLYFOR select name from sys.databaseswhere name not in ('anderstest' , 'tblCheck', 'Northwind', 'tempdb')order by name
DECLARE @name sysnameOPEN DBNames
FETCH NEXT FROM DBNames INTO @nameWHILE (@@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 @nameEND
CLOSE DBNamesDEALLOCATE DBNamesGO
Cursors are not bad, you just have to use them appropriately.