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

Question

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.

Solution 1

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'

Solution 2

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

Solution 3

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.

Comments

[salvationishere]

I typed it in there, and yes I can get there.

[SQLMenace]

is the DB server your local machine? If not then it won't be able to see and you need a UNC path then

[salvationishere]

no, DB server is not my local machine. Problem is that I don't have copy permissions on server machine.

[salvationishere]

You won't believe this, but I can't share any of my local folders! I can't believe this! They have everything locked down. So maybe there is no solution for me?!

[Andriy M]

The former is a system stored procedure used to invoke shell commands (typically, to run external programs). And the latter is a Windows native console ftp client program. You could (try to) use it to upload an already saved backup file to some accessible ftp server (then of course to download it to your machine).