SQL Server backup on network drive - Technology tips

Sunday, November 3, 2013

SQL Server backup on network drive

If you come across with a situation where you take backup and restore on hard drive that is easily done using SSMS but if it comes to network drive then it might give you tough time all the way.
Backup and restore database is easy activity using sql server management studio you can do it using single click on UI on SSMS or you can create scripts too to create back and restore it on hard drive .
In order to take sql server back up on network drive you can create a mapped drive if you want to create backup on network or restore it on network. But problem comes when you do not have permission to write on a network drive.
You will see error messages all the time while taking sql server backup on network share – reason is your sql server agent which is running under any name account does not have access on network share so either you configure another service account that has permission on network drive or path or you change service account name from services and tap sql server service and tap service account there define new account.
 
Key sequence:

Run  (command)>> Services.msc>>SQL Server Service>> Properties>> Log on>>Account Name

In this account name section you define any account that has access on network where you want to take backup or restore backup.
 By only defining authorized account name you can take backup on network share otherwise you probably end up with the smoke.
So one liner is – your sql server service account needs permission on specified network share in order to to take back up on network drive so define it.


\\NETWORKSHARE\BACKUPFILE
You need sql server service account full permission to access this resource if your service account does not have permission on this resource then you will fail to take back up.



4 comments:

Anonymous said...

this is fantastic about sql server
Juddie,
UAE

Anonymous said...

thank for giving me very informative solution for my issue.
John

Anonymous said...

great share for me right now.
Stephen

Anonymous said...

nobody has covered backup on network share like this.