locked
Backup using UNC path with Date-Time filename RRS feed

  • Question


  • This simple UNC backup script works:

    BACKUP DATABASE dbname TO DISK = N'\\huge\c$\backup\dbname 20101022.bak'

    But this doesnt:

    DECLARE @FileName AS VARCHAR(100)

    DECLARE @Date AS CHAR(8)

    SET @Date = CONVERT(CHAR(8), GETDATE(), 112)

    SET @FileName = 'N''\\huge\c$\backup\dbname ' +  @Date + '.bak'

    BACKUP DATABASE dbname TO DISK = @FileName

    Why ? What is wrong with sending N` prefix in a stirng ?

    Is it possible to make UNC backups with dynamic filenames ?

    Friday, October 22, 2010 8:53 AM

Answers

  • N was inside the single quotes. Modified script looks like this

     

    DECLARE @FileName AS VARCHAR(100)
    DECLARE @Date AS CHAR(8)
    SET @Date = CONVERT(CHAR(8), GETDATE(), 112)
    SET @FileName = N'''\\huge\c$\backup\dbname' + @Date + '.bak'''
    BACKUP DATABASE dbname TO DISK = @FileName
    

     

    Just an FYI. Adding a print statement (in this case PRINT @FileName) helps you troubleshoot the issue quickly.

     




    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Friday, October 22, 2010 12:01 PM