Friday, 1 June 2012

SQLCMD and BACKUP - watch out for this

Yesterday, I was working creating a rake task for backing up a database.  As I am still pretty new to ruby I decided to see if I could get the backup code working with sqlcmd from the command line first.  I created a backupdb.sql file to do the backup and then called it from the command line.

This is what the backupdb.sql looked like:

BACKUP DATABASE $(database)  
    TO DISK = $(backupfilename)

Not much to it!
I then executed the following sqlcmd command:

sqlcmd -S  localhost -i BackupDb.sql -v database="mydb" -v backupfilename="mydb.bak"

When I just specified the database and backupfilename the backup worked fine with the backup file being written to the default SQL Server backup folder.  I then changed the backupfilename to include a path as I wanted to specify where the backup file should be saved. This is the command I executed:

sqlcmd -S  localhost -i BackupDb.sql -v database="mydb" -v backupfilename="E:\sqlbackups\mydb.bak"

This time I got the following error:
Msg 102, Level 15, State 1, Server ANDREW-PC, Line 3 Incorrect syntax near 'E:'.
After a lot of fiddling around I figured out what I needed to do to fix the problem. The problem was that the backupfilename needs to be unicode.  See below for the fixed backupdb.sql

BACKUP DATABASE $(database)  
    TO DISK = N'$(backupfilename)'

No comments:

Post a Comment