space given in DB name and backup failure

  • There seems to be an extra space in the database name given when it was created.

    The backup job is failing with the following error message:

    BACKUP failed to complete the command BACKUP DATABASE [safe_PreTMS ] TO DISK = N'S:\Full\safe_PreTMS \safe_PreTMS _db_201310021607.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

    Please observe the space in the above command. When I remove the space and run the backup, it happens without any issue.

    I am unable to remove the space in the DB name as well.

    Can someone help me fix this issue

  • You can rename the database to something other than the original name, then rename again to the original name without the space, eg.

    USE [master]

    sp_renamedb 'safe_PreTMS ', 'blah'

    GO

    sp_renamedb 'blah', 'safe_PreTMS'

    GO

    Obviously check to ensure your application is updated to refer to the new name and redo the backup job.

  • some may consider the extra space a security feature :crazy:

    Simplest way is to modify the job step command and remove the space.

    If you cannot rename the database (which may need application modification):

    If you generate the statements on the fly, replace all special chars with e.g. "_" in the part where that script determines the file path / name

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (10/2/2013)


    some may consider the extra space a security feature :crazy:

    Simplest way is to modify the job step command and remove the space.

    If you cannot rename the database (which may need application modification):

    If you generate the statements on the fly, replace all special chars with e.g. "_" in the part where that script determines the file path / name

    The maintenance plan runs for all database backups. Its a generic job.

    Do you have any more inputs.

  • I haven't used the builtin maintenance plan since early sql2000.

    I use sql server backup devices. http://technet.microsoft.com/en-us/library/aa173665(v=SQL.80).aspx

    Maybe that's a path you may want to investigate to overcome this issue.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Benki Chendu (10/3/2013)


    The maintenance plan runs for all database backups. Its a generic job.

    Do you have any more inputs.

    Try to use double quotes around the file names:

    BACKUP DATABASE [safe_PreTMS ] TO DISK = N'"S:\Full\safe_PreTMS \safe_PreTMS _db_201310021607.BAK"' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

    Or like this:

    BACKUP DATABASE [safe_PreTMS ] TO DISK = N'S:\Full\"safe_PreTMS \safe_PreTMS _db_201310021607.BAK"' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

    _____________
    Code for TallyGenerator

  • I like Sergiy's advice.

  • Steve Jones - SSC Editor (10/22/2013)


    I like Sergiy's advice.

    I better like this:

    generate the statements on the fly, replace all special chars with e.g. "_" in the part where that script determines the file path / name

    Definitely less problems with generating/managing/using the file all the way.

    _____________
    Code for TallyGenerator

  • use double quotes

    backup database "Tfs_Products Pipeline Automation" to disk='c:\dump\db\Tfs_Products_Pipeline_AutomationDump1.bak';

Viewing 9 posts - 1 through 8 (of 8 total)

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