0

I'm getting this error 

Msg 3201, Level 16, State 1, Server server\instance, Line 1
Cannot open backup device '\\server\share\database.bak'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server server\instance, Line 1
BACKUP DATABASE is terminating abnormally.
  • I would recommend seeing the SQL Server error log for more details. –  Jul 25 '11 at 07:24
  • I don't think SQL server can cope with paths like \\server, you need to mount the folder on the server as a local drive letter or copy the backup file to the machine sql server is running on. – Ian Ringrose Jul 25 '11 at 08:50

1 Answers1

1

SQL Server does happily backup to UNC paths - I backup to UNCs extensively for SQL Server 2000 and later. For SQL Server 2005 or later I use Ola's excellent backup (and db maintenance) scripts (http://ola.hallengren.com/)

I'd suggest the first thing you need to do is verify the device is online (\server) by either pinging it, or browsing to it. Then you need to check the folder is available (\share)

If they are, then you need to check that the SQL Server service account (the database engine) has access to all of that. When you backup or restore, it'll use the service account that SQL Server itself runs under. This is where domain service accounts are far easier to use that Local System or Network - as well as being more secure etc etc

Just off topic, backing up to a network device is typically slower than backing up to "local" discs, whether locally attached or SAN. This means that you restores will be slower too - something to bear in mind, or test at least. A happy compromise might be to backup locally, have a separate process to copy off to somewhere safer, and keep on the server for 24/48 hours or so.

Peter Schofield
  • 1,639
  • 9
  • 11