Post reply

sql db backup t-sql

  • May 7, 2014 at 4:19 am

    #285385

    Hi all.

    I would like to create the following small piece of code:

    Backup a sql db

    Create the .bak file in this format:

    DBName_YYYYMMDDHHMM.bak (for example)

    I have created this code:

    declare @backupfile nvarchar(2000)

    set @backupfile = N'W:\Backup\360_prod_' + replace(convert(nvarchar(50), getdate(), 120), ' ',':') + N'.BAK'

    BACKUP DATABASE [DBName] TO DISK = @backupfile WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'DBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    I get this error:

    Msg 3201, Level 16, State 1, Line 5

    Cannot open backup device 'W:\Backup\DBName_2014-05-07:10:34:15.BAK'. Operating system error 123(failed to retrieve text for this error. Reason: 15105).

    Msg 3013, Level 16, State 1, Line 5

    BACKUP DATABASE is terminating abnormally.

    What is wrong?

    Thanks in advance.

  • May 7, 2014 at 4:43 am

    #1711414

    DECLARE @backupfile NVARCHAR(512) = N'W:\Backup\360_prod_'

    + REPLACE(REPLACE(REPLACE(CONVERT(varchar(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')

    + N'.BAK';

    BACKUP DATABASE DBName

    TO DISK = @backupfile

    WITH COPY_ONLY, INIT, SKIP, CHECKSUM, COMPRESSION, NAME = N'DBName-Full Database Backup';

    RESTORE VERIFYONLY

    FROM DISK = @backupfile;

  • May 7, 2014 at 4:50 am

    #1711418

    If Drive W: is just a share, SQL Server can't see it. It needs to be allocated to SQL Server as a resource.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 7, 2014 at 5:02 am

    #1711422

    niklasrene (5/7/2014)

    What is wrong?

    The colons in the time portion of the filename are not valid path\file name characters

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply