What are the challenges you face when working across database platforms? Take the survey
Options

Backup issues with double quotes and brackets

schammerschammer Posts: 4
edited May 3, 2010 11:39PM in SQL Backup Previous Versions
Greetings,

There appear to be issues with SqlBackup working with databases whose names contain double quotes, or backing-up to .sqb files whose names contain square brackets.

Here's an example of the former
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [BJS_""_TEST] TO DISK = ''\\MyServer\Share\BJS_TEST.sqb'' WITH COMPRESSION = 2, INIT, VERIFY"'

Results in: "SQL error 911: Could not locate entry in sysdatabases for database 'BJS_ _TEST'. No entry found with that name. Make sure that the name is entered correctly."

The double quotes appear to have been stripped from the name.

Here's an example of the latter:
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [BJS_TEST] TO DISK = ''\\MyServer\Share\_bjsTest[me]_ok.sqb'' WITH COMPRESSION = 2, INIT, VERIFY"'

Results in: "Syntax error: 'me'_ok.sqb'' after '\\MyServer\Share\_bjsTest'"

The brackets appear to have been replaced with single quotes in the error message, though I'm not sure if that's the actual problem.

We can easily work around the problem with brackets in the filename, since we control the backup files, but working around the double quotes in our clients' database names will be more difficult.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    To work around the database name issue, depending on your situation, you may be able to get away with using the BACKUP DATABASES EXCLUDE .... syntax e.g.

    EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE [master, model, msdb, AdventureWorks, pubs, northwind, ...] TO DISK = [\\myserver\share\bjs_test\<TYPE>_<DATETIME yyyymmdd hhnn>.sqb] " '

    You can't use the <DATABASE> tag as it will use the double quotes in the database name to form the path/file name, so you can only use this to back up a single database at a time, which would be tedious if a lot of your database had that naming convention, resulting in a long exclusion list.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.