December 29th, 2006 by Andrew Chen
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
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.
Related Posts:
MySQL Replication and MS SQL Log ShippingThe Best Way to Backup SQL ServerAn ASP.NET setting has been detected that does not apply in Integrated managed pipeline modeRestore the latest database backup automatically and xp_dirtreeError Reading Packet From Server Error Reading Log Entry Server_Errno=1236
Posted in Programming, SQL Server | 11,994 views
Loading ...
RSS feed
| Trackback URI
Thanks. Simple and solved my problem.
I also had the same problem. I added the” SQL Service Account” to the “Administrators” group and voilà! Thank you Andrew.