none
Backup with Timestamp

    Question

  • I wrote this code to create a database backup with timestamp:

    create proc admin.createBackUp @location varchar(50)as
    declare @time datetime
    set @time=GETDATE()
    declare @t varchar(50)
    set @t=CONVERT(varchar(50),@time)
    declare @b varchar(50)
    set @b=@location+@t+'.bak'
    BACKUP DATABASE [project] TO  DISK =@b  WITH NOFORMAT, NOINIT,  NAME = N'project-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    

     

    To execute the procedure, I used this code:

    admin.createBackUp 'c:\'
    

     

    It generates the following message:

    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    51 percent processed.
    60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    Processed 624 pages for database 'project', file 'WHERE TO GO' on file 1.
    100 percent processed.
    Processed 3 pages for database 'project', file 'WHERE TO GO_log' on file 1.
    BACKUP DATABASE successfully processed 627 pages in 3.087 seconds (1.584 MB/sec).
    
    

     

    This is the output file:

     

    As you can see, it has many issues:

    • Its name is truncated.
    • It has no extension.
    • It has zero size!

    So, ...?

    • Edited by Humdy Sunday, September 18, 2011 3:34 PM
    Sunday, September 18, 2011 3:33 PM

Answers

  • The datetime format you are using has a colon in the file name, which is not legal.  The example below replaces the colons with spaces:

    CREATE PROC admin.createBackUp 
    	@location varchar(50)
    AS
    DECLARE @b varchar(255);
    
    SET @b=@location+REPLACE(CONVERT(varchar(50),GETDATE()), ':', ' ')+'.bak';
    
    BACKUP DATABASE [MyDatabase] 
    TO  DISK =@b  
    WITH 
    	NOFORMAT
    	,NOINIT
    	,NAME = N'project-Full Database Backup'
    	,SKIP
    	,STATS = 10;
    	
    RETURN @@ERROR;
    GO
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Humdy Sunday, September 18, 2011 4:26 PM
    Sunday, September 18, 2011 4:08 PM
    Moderator

All replies

  • The datetime format you are using has a colon in the file name, which is not legal.  The example below replaces the colons with spaces:

    CREATE PROC admin.createBackUp 
    	@location varchar(50)
    AS
    DECLARE @b varchar(255);
    
    SET @b=@location+REPLACE(CONVERT(varchar(50),GETDATE()), ':', ' ')+'.bak';
    
    BACKUP DATABASE [MyDatabase] 
    TO  DISK =@b  
    WITH 
    	NOFORMAT
    	,NOINIT
    	,NAME = N'project-Full Database Backup'
    	,SKIP
    	,STATS = 10;
    	
    RETURN @@ERROR;
    GO
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Humdy Sunday, September 18, 2011 4:26 PM
    Sunday, September 18, 2011 4:08 PM
    Moderator
  • ... abd here's a simple stored procedure (along with link to Ola's procedures) that has might be helpful: http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    Monday, September 19, 2011 9:49 AM
    Moderator