space given in DB name and backup failure

Question

space given in DB name and backup failure

Benki Chendu

SSCrazy Eights

Points: 8051
More actions
October 2, 2013 at 3:19 pm

#298437

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

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

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

Andrew G SSChampion Points: 12809 More actions · Answer 1

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.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

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

Benki Chendu SSCrazy Eights Points: 8051 More actions · Answer 3

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.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

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

Sergiy SSC Guru Points: 110208 More actions · Answer 5

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 736494

October 22, 2013 at 9:50 pm

#1660788

I like Sergiy's advice.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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

hussaini SSC Enthusiast Points: 143 More actions · Answer 8

use double quotes

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