How to create daily backup with unique name in sql server

Question

I want to make full database backup of my server's all databases with unique name daily. For that I have an idea to keep timestamp which will make database copy separate. Suppose there is a database on server named ABCD then it should be backuped like:

ABCD_21_03_2013
ABCD_22_03_2013

How can I do this. I don't know much about these types of SQL Backup JOBS.

Answer 1

To create a daily backup with a name such as Filename_MM_DD_YYYY:

1. In SSMS, right-click the database you want to backup
2. Select Tasks | Back Up
3. In the dialog, select the type and location of the backup
4. At the top of this dialog, select the Script Action to Job option in the Script drop down menu
5. A New Job dialog is opened and the first step creates a database backup Go to the fist step and click Edit

Existing code looks like:

BACKUP DATABASE [AdventureWorks2012] TO  DISK = N'E:\Test\AdventureWorks.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Replace it to be

DECLARE @SQLStatement VARCHAR(2000) 
SET @SQLStatement = 'E:\Test\AdventureWorks_' + CONVERT(nvarchar(30), GETDATE(), 110) +'.bak' 
BACKUP DATABASE [AdventureWorks2012] TO  DISK = @SQLStatement

6 Save the job

The database backups will be named:

AdventureWorks_07-29-2013

AdventureWorks_07-30-2013

AdventureWorks_07-31-2013

Now all you have to do is make the right schedule

Answer 2

Automatic backup of all databases on the server.

About Jobs:

http://msdn.microsoft.com/en-us/library/ms190268.aspx

Query:

SET NOCOUNT ON;

DECLARE 
      @FileName NVARCHAR(1024)
    , @DBName NVARCHAR(256)
    , @PathName NVARCHAR(256)
    , @Message NVARCHAR(2048)
    , @IsCompressed BIT

SELECT 
      @PathName = 'D:\BACKUP\'
    , @IsCompressed = 1 

DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR  
    SELECT
          sd.name
        , file_path = @PathName + FileDate + '_' + name + '.bak'
    FROM sys.databases sd
    CROSS JOIN (
        SELECT FileDate = 'ABCD_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '_')
    ) fd
    WHERE sd.state_desc != 'OFFLINE'
        AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb')
    ORDER BY sd.name 

OPEN db

FETCH NEXT FROM db INTO 
      @DBName
    , @FileName  

WHILE @@FETCH_STATUS = 0 BEGIN 

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
    RAISERROR (@Message, 0, 1) WITH NOWAIT

    SELECT @SQL = 
    'BACKUP DATABASE [' + @DBName + ']
    TO DISK = N''' + @FileName + '''
    WITH FORMAT, ' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION, ' ELSE '' END + N'INIT, STATS = 15;' 

    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM db INTO 
          @DBName
        , @FileName 

END   

CLOSE db   
DEALLOCATE db

Output:

BACKUP DATABASE [AdventureWorks2008R2]
    TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2.bak'
    WITH FORMAT, COMPRESSION, INIT, STATS = 15;

BACKUP DATABASE [AdventureWorks2008R2_Live]
    TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2_Live.bak'
    WITH FORMAT, COMPRESSION, INIT, STATS = 15;

BACKUP DATABASE [AdventureWorks2012]
    TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2012.bak'
    WITH FORMAT, COMPRESSION, INIT, STATS = 15;

Results:

2013-05-24 09:54:34: AdventureWorks2008R2
15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
75 percent processed.
90 percent processed.
Processed 23416 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 1 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 23417 pages in 4.052 seconds (45.148 MB/sec).

.....

Answer 3

I think, best way to perform schedule back-up is to create Job. Add your back-up job and schedule that on particular date and time.

How to create job

Answer 4

Some third-party backup programs, for example: EMS SQL Backup, allow setting templates for backup file names. Timestamp, server instance name, database names and other info can be added to file name.

Answer 5

Thanks for the posts I just want to share a small update I have made on the script to make log database backups and automatically skip all databases with recovery model = simple which does not allow log backups. Hope it helps... And yes, Mr. Ravi is right the best approach is to create a job, I've created a SP and am running from a job.

CREATE PROCEDURE sp_logbackup 
AS
SET NOCOUNT ON;
DECLARE 
  @FileName NVARCHAR(1024)
, @DBName NVARCHAR(256)
, @PathName NVARCHAR(256)
, @Message NVARCHAR(2048)
, @IsCompressed BIT
SELECT 
  @PathName = '\\myServer\...' 
, @IsCompressed = 1 
DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR  
SELECT
      sd.name
    , file_path = @PathName + name + '_' + FileDate + '.trn'
FROM sys.databases sd
CROSS JOIN (
    SELECT FileDate = REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-','_'),':',''),' ','') 
) fd
WHERE sd.state_desc != 'OFFLINE'
    AND sd.recovery_model != 3
    AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY sd.name 
OPEN db
FETCH NEXT FROM db INTO 
  @DBName
, @FileName  
WHILE @@FETCH_STATUS = 0 BEGIN 
DECLARE @SQL NVARCHAR(MAX)
SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
RAISERROR (@Message, 0, 1) WITH NOWAIT

SELECT @SQL = 
'BACKUP LOG [' + @DBName + ']
TO DISK = N''' + @FileName + '''
WITH FORMAT, NAME = N''' + @DBName + ''', SKIP, REWIND, NOUNLOAD, STATS = 10;' 

EXEC sys.sp_executesql @SQL
FETCH NEXT FROM db INTO 
      @DBName
    , @FileName 
END   
CLOSE db   
DEALLOCATE db