Author |
Topic |
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2011-12-22 : 06:45:44
|
I am trying to backup sql server database on a network drive. I have mapped the network, But i am getting the error Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'Z:\NTT\NTT_22_.BAK'. Operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. any help will be appreciated. |
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2011-12-22 : 10:28:14
|
You may want to use a fileshare pathname (\\fileserver\share\....) instead of a mapped drive letter. Also check the ACL's between the sql server and the fileserver - usually a sysengr or system admin can help you with that determination. |
|
|
X002548
Not Just a Number
15586 Posts |
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-12-22 : 19:40:35
|
Backups are run in the context of the account running the SQL Server service. You need to grant access on the share to that account before it will work.If SQL Server is not running with a domain service account - then it will not work.When backing up over the network, you have to use the UNC path. The service account doesn't know anything about mapped drives and cannot use them. You also need to make sure the network between the SQL Server system and the backup server is very good. Any blips on the network will cause the backups to fail.Jeff |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-23 : 00:27:46
|
FWIW we never backup across the network. We backup locally and then MOVE, or COPY, the file to the network location. Then if the network connection fails for any reason at least we have the local copy of the backup. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-12-23 : 02:00:37
|
You have to make the target of your backup a UNC name: \\fileserver\share\folder\backupfileWe do daily full backups of several hundred SQL Servers and thousands of databases to file shares, as well as transaction log backups every 15 minutes to the same file shares.Your network has to be stable and a fast NIC on the SQL Server, like 1 GBit/sec, is best for good backup speed.Your target file server must have very good disk and NIC performance, especially if you are backing up multiple SQL Servers. We use file servers dedicated just for SQL Server backups with fast local disk arrays, with 10 to 20 15K drives in each array, and up to four 1GBit/Sec NICs.If your databases are large, use backup compression, which is available in SQL 2008 Enterprise or Developer Editions or SQL 2008 R2 Standard, Enterprise or Developer Editions.For versions of SQL Server without built-in backup compression, you can use a third party backup compression utility, like Litespeed or Redgate Backup.Using SQL 2008 Enterprise Edition with backup compression I can backup a 350 GB database to a file share in 15 to 20 minutes.CODO ERGO SUM |
|
|
auladali
Starting Member
3 Posts |
Posted - 2014-10-11 : 13:02:57
|
Hello guys,I have the same problem like 'sagitariusmzi', I use the fileshare pathname but still the same issue.I can access the fileshare pathname with no problem but SQL server can't.Please could you help.yes we can. |
|
|
auladali
Starting Member
3 Posts |
Posted - 2014-10-11 : 14:22:14
|
Hi,So finally I have the solution.SQL SERVER SERVICE and SQL SERVER AGENT(SQL SERVER CONFIGURATION MANAGER) should be log on with the same username(local username with full access to your fileshare pathname).Regards.yes we can. |
|
|
auladali
Starting Member
3 Posts |
Posted - 2014-10-11 : 14:22:50
|
Hi,So finally I have the solution.SQL SERVER SERVICE and SQL SERVER AGENT(SQL SERVER CONFIGURATION MANAGER) should be log on with the same username(local username with full access to your fileshare pathname).Regards.yes we can. |
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-10-12 : 07:54:45
|
SQL Server agent does not need to have any permission on fileshare if you are not taking backup using SQL Server agent. And there is absolutely no bid deal in taking backup on network share it works fine subject to condition network and fileshare remains stable.Hope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|