Adding date timestamp to a backup file name
-
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 TestDB
TO <A href="mailto
ISK=@BackupFileName">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.
-
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
---------------------------------------------------------------------
-
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
-
Cursors are not bad, you just have to use them appropriately.
-
This is what I have written and using it for a while. I have scheduled this as job and I also use it on adhoc basis.
CREATE PROCEDURE dbo.SP_DBA_BACKUP_PROC_FULL
AS
BEGIN
/************* ALTERd BY: Raman Gupta *************/
DECLARE @NAME VARCHAR(500)
DeCLARE @DBNAME VARCHAR(100)
DECLARE @FILENAME VARCHAR(1000)
DECLARE @TIMEALTERD AS VARCHAR(10)
DECLARE @HOUR SMALLINT
DECLARE @MINUTES SMALLINT
DECLARE @CMD VARCHAR(1000)
DECLARE @ZIP_FILENAME VARCHAR(1000)
DECLARE @vMinutes VARCHAR(5)
DECLARE @DriveLetter VARCHAR(5)
DECLARE @FolderPath VARCHAR(200)
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)
INSERT INTO #disk_free_space
EXEC master..xp_fixedDrives
IF EXISTS (SELECT 1 FROM #disk_free_space WHERE DriveLetter='N')
SELECT @DriveLetter='N:'
ELSE
SELECT @DriveLetter='Z:'
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#TempFolderExists]'))
DROP TABLE #TempFolderExists
CREATE TABLE #TempFolderExists(FileExists BIT ,FileFolder BIT ,ParentDirectory TINYINT)
SET @HOUR = DATEPART(hh, GETDATE())
SET @MINUTES = DATEPART(mi, GETDATE())
SET @vMinutes=CASE WHEN @MINUTES BETWEEN 0 AND 9 THEN ('0'+CONVERT(VARCHAR(1),@MINUTES))
ELSE CONVERT(VARCHAR(2), @MINUTES)
END
BEGIN
IF (@HOUR IN (0)) and (@MINUTES Between 0 and 29)
SET @TIMEALTERD = '12'+@vMinutes+'AM'
IF (@HOUR IN (0)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '12'+@vMinutes+'AM'
ELSE IF (@HOUR IN (1)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '01'+@vMinutes+'AM'
ELSE IF (@HOUR IN (1)) and (@MINUTES between 30 and 59)
SET @TIMEALTERD = '01'+@vMinutes+'AM'
ELSE IF (@HOUR IN (2)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '02'+@vMinutes+'AM'
ELSE IF (@HOUR IN (2)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '02'+@vMinutes+'AM'
ELSE IF (@HOUR IN (3)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '03'+@vMinutes+'AM'
ELSE IF (@HOUR IN (3)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '03'+@vMinutes+'AM'
ELSE IF (@HOUR IN (4)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '04'+@vMinutes+'AM'
ELSE IF (@HOUR IN (4)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '04'+@vMinutes+'AM'
ELSE IF (@HOUR IN (5)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '05'+@vMinutes+'AM'
ELSE IF (@HOUR IN (5)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '05'+@vMinutes+'AM'
ELSE IF (@HOUR IN (6)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '06'+@vMinutes+'AM'
ELSE IF (@HOUR IN (6)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '06'+@vMinutes+'AM'
ELSE IF (@HOUR IN (7)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '07'+@vMinutes+'AM'
ELSE IF (@HOUR IN (7)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '07'+@vMinutes+'AM'
ELSE IF (@HOUR IN (8)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '08'+@vMinutes+'AM'
ELSE IF (@HOUR IN (8)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '08'+@vMinutes+'AM'
ELSE IF (@HOUR IN (9)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '09'+@vMinutes+'AM'
ELSE IF (@HOUR IN (9)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '09'+@vMinutes+'AM'
ELSE IF (@HOUR IN (10)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '10'+@vMinutes+'AM'
ELSE IF (@HOUR IN (10)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '10'+@vMinutes+'AM'
ELSE IF (@HOUR IN (11)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '11'+@vMinutes+'AM'
ELSE IF (@HOUR IN (11)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '11'+@vMinutes+'AM'
ELSE IF (@HOUR IN (12)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '12'+@vMinutes+'PM'
ELSE IF (@HOUR IN (12)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '12'+@vMinutes+'PM'
ELSE IF (@HOUR IN (13)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '01'+@vMinutes+'PM'
ELSE IF (@HOUR IN (13)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '01'+@vMinutes+'PM'
ELSE IF (@HOUR IN (14)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '02'+@vMinutes+'PM'
ELSE IF (@HOUR IN (14)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '02'+@vMinutes+'PM'
ELSE IF (@HOUR IN (15)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '03'+@vMinutes+'PM'
ELSE IF (@HOUR IN (15)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '03'+@vMinutes+'PM'
ELSE IF (@HOUR IN (16)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '04'+@vMinutes+'PM'
ELSE IF (@HOUR IN (16)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '04'+@vMinutes+'PM'
ELSE IF (@HOUR IN (17)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '05'+@vMinutes+'PM'
ELSE IF (@HOUR IN (17)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '05'+@vMinutes+'PM'
ELSE IF (@HOUR IN (18)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '06'+@vMinutes+'PM'
ELSE IF (@HOUR IN (18)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '06'+@vMinutes+'PM'
ELSE IF (@HOUR IN (19)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '07'+@vMinutes+'PM'
ELSE IF (@HOUR IN (19)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '07'+@vMinutes+'PM'
ELSE IF (@HOUR IN (20)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '08'+@vMinutes+'PM'
ELSE IF (@HOUR IN (20)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '08'+@vMinutes+'PM'
ELSE IF (@HOUR IN (21)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '09'+@vMinutes+'PM'
ELSE IF (@HOUR IN (21)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '09'+@vMinutes+'PM'
ELSE IF (@HOUR IN (22)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '10'+@vMinutes+'PM'
ELSE IF (@HOUR IN (22)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '10'+@vMinutes+'PM'
ELSE IF (@HOUR IN (23)) and (@MINUTES between 0 and 29)
SET @TIMEALTERD = '11'+@vMinutes+'PM'
ELSE IF (@HOUR IN (23)) and (@MINUTES Between 30 and 59)
SET @TIMEALTERD = '11'+@vMinutes+'PM'
END
DECLARE C1 CURSOR FOR
SELECT Name FROM MASTER..SYSDATABASES (NOLOCK)
WHERE NAME NOT IN ('master','tempdb','model','msdb','pubs','Northwind')
FOR READ ONLY
OPEN C1
FETCH NEXT FROM C1 INTO @DBNAME
WHILE(@@FETCH_STATUS=0)
BEGIN
SET NOCOUNT ON
SET @FolderPath=@DriveLetter+'\SQL2000\BACKUP\DBs\'+@DBName
DELETE FROM #TempFolderExists
INSERT INTO #TempFolderExists(FileExists,FileFolder,ParentDirectory)
exec master..xp_fileexist @FolderPath
If (Select FileFolder FROM #TempFolderExists) = 0
BEGIN
SET @CMD='MD '+@FolderPath
EXEC master..XP_CMDSHELL @CMD
END
SET @CMD='DEL '+@DriveLetter+'\SQL2000\BACKUP\DBs\'+@DBNAME+'\'+'*.BAK'
EXEC MASTER..XP_CMDSHELL @CMD
PRINT 'BACKING UP....'+@DBNAME
SELECT @NAME=@DriveLetter+'\SQL2000\BACKUP\DBs\'+@DBNAME+'\'+@DBNAME+'_'+convert(varchar,getdate(),112)+'_'+SUBSTRING(DATENAME(DW,getdate()),1,3)+'_FULL_'+@TIMEALTERD+'.BAK'
BACKUP DATABASE @DBNAME TO DISK = @NAME WITH INIT , NOUNLOAD , NAME = @DBNAME, NOSKIP , STATS = 10, NOFORMAT
IF @@ERROR <> 0
BEGIN
RAISERROR ('Full backup for %d failed.', 16, 1,@DBNAME)
END
FETCH NEXT FROM C1 INTO @DBNAME
END
CLOSE C1
DEALLOCATE C1
DROP TABLE #TempFolderExists
DROP TABLE #disk_free_space
END
GO
-
Hello Anders,
can you post the script for cleaning up the directories too.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply