T-SQL Backup Database command for file path with spaces?

6
votes

How do I write a T-SQL backup database command to specify a file containing spaces? Here is what I have:

BACKUP DATABASE AMDMetrics TO DISK = 'C:\Documents and Settings\daultrd\My Documents\DatabaseBackups\AMD_METRICS.DAT'

And this is the error I get:

Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'C:\Documents and Settings\daultrd\My Documents\DatabaseBackups\AMD_METRICS.DAT'. Operating system error 3(The system cannot find the path specified.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

3
tsqlfilebackup
Have you created the DatabaseBackups folder under My Documents and verified that the path is correct? – Joe Stefanelli
Joe, can u look at my comments below and advise? – salvationishere

3 Answers

2
votes

Try sharing your intended destination folder and using a UNC path to backup from the server to your local machine.

BACKUP DATABASE AMDMetrics 
    TO DISK = '\\YourMachineName\SharedFolderName\AMD_METRICS.DAT'
1
votes

This works for me, are you sure that the directory is correct?

backup database master to disk = 'c:\Test Me\master.bak'


Processed 41728 pages for database 'master', file 'master' on file 1.
Processed 5 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 41733 pages 
in 22.911 seconds (14.230 MB/sec).

copy and paste this into explorer and see if you can get there C:\Documents and Settings\daultrd\My Documents\DatabaseBackups

This of course has to be the same machine, otherwise you need to map a drive to the location or use UNC paths

1
votes

I was working through this issue as well.

It's possibly that the Service that SQL Server is running under (Network Service by Default) doesn't have permission to the folder specified.

BACKUP DATABASE master TO DISK = 'master1.bak' WITH INIT

The above should backup to the default backup folder if that works with no problem it'll be the problem stated.