3

I used to have SQL Server 2005 installed on my server. In my maintenance plan, the backup name format was like this: mydatabase_backup_yyyymmddhhss.bak.

Now I am using SQL Server 2012 but I am getting a totally different backup name format. I would like to have the same format name as 2005.

How can I do this?

  • 1
    When you take backup from SQL server 2012 maintenance plan your backup file name will be like this mydatabase_backup_yyyy_mm_dd_hh_ss.bak right – sairam May 20 '14 at 10:49
1

You would need to build a custom backup script which uses dynamic SQL to build the name of the .bak file. The maintenance plan could execute this with the 'Execute T-SQL Statement Task' item.

The T-SQL would look something like this

Select @SQL = 'BACKUP DATABASE [' + @DBName + '] TO  DISK = N''' + @Path + '\' + @DBName + '_backup_' + @Date + '.bak'' WITH NOFORMAT, INIT,  NAME = N''' + @DBName + '_backup_' + @Date + ''', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 '

EXEC SP_ExecuteSql @SQLToExecute = @SQL
1

Below is the script to get DB backup as per your format. This can be scheduled with SQL Agent Job

DECLARE @FOLDERPATH VARCHAR(500)
DECLARE @FOLDERPATHWITHNAME VARCHAR(500)
Declare @NAME Varchar(200)
Declare @DATE Varchar(200)
Declare @TIME Varchar(200)
Declare @TIME1 Varchar(200)

-------------------------------------------------------    
--JUST NEED TO UPDATE BELOW PATH FOR DB BACKUP LOCATION
-------------------------------------------------------

Set @FOLDERPATH = 'D:\Temp\Dsingh\'

select @TIME1 = Convert(time, getdate())

select @DATE = (select convert(varchar,getDate(),112))

select @TIME = ((select Convert(Varchar, DATEPART(hh, @TIME1))) 
               + (Select CONVERT(Varchar, DATEPART(ss, @TIME1))))

Select @NAME = 'mydatabase_backup' + '_' + @DATE + @TIME

Set @FOLDERPATHWITHNAME = @FOLDERPATH + @NAME + '.bak'

EXECUTE master.dbo.xp_create_subdir @FOLDERPATH

BACKUP DATABASE [TEST_1_Smarttrack_03202014] 
       TO  DISK = @FOLDERPATHWITHNAME WITH NOFORMAT, NOINIT,  
       NAME = @NAME, SKIP, REWIND, NOUNLOAD,  STATS = 10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.