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

Question

How to 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 the 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 terminates abnormally.

Answer 1

Try using the destination destination folder and use the UNC path to back up from the server to the local computer.

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

Answer 2

This works for me, are you sure 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, must be the same machine, otherwise you need to map the drive to a location or use UNC paths

Answer 3

I also worked on this issue.

It is possible that a service running under SQL Server (the default network service) does not have permission to the specified folder.

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

The above should backup to the default backup folder; if this works without problems, it will be a problem.