SQL Server Backup Device Error or Device off-line

Today I encountered a problem when I try to back up some databases manually. It gave me this error message:

• Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device, Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

I followed the error message and found

<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>

an article on Microsoft support web site that talks about it. It says you will get this error if you try to backup database to a network drive and if the service account that SQL Server is running on doesn’t have the necessary permission. But I wasn’t backing up the databases to a network drive. I was backing it up to a local drive, the same drive that data file is located and SQL server was running without any problems. I compared it with other servers because the other servers have no problems backing up databases. It took me a while to notice the domain of the SQL service account on the server was different from those of the others. It was pretty hard to notice when the two domain names were close. Like Siusic/andrew and Siusie/andrew, so I changed the service account but after I changed it and tried to restart SQL server it didn’t run. It was a headache. After closer look I found the account I changed to had no administrator permission on the server. So I add it to the local administrators group and reboot the server. After that the problem is solved

Experience tells if the service account name is popular don’t use it on multiple domains. One thing I still don’t understand is if SQL server is running, reading and writing data without any problems then why it cannot do a backup operation. Backing up database is also a write access to the disk. I want to replicate the problem and see what the reason is. If I get time to do this I will let you know. But I guess I will be too lazy to get on it. Problem is solved after all.