Search This Blog & Web

Tuesday, April 10, 2012

Creating a backup on map network drive using SQL SERVER


Few days ago I have assigned a task to move backup from one place to another (network) place to increase drive space. I am so much curious about why we need to move backups to network path after every few days to save space. I have started searching on that and find few solutions.
1-     Make a policy to take regular backups on regular intervals.
2-     Create a map network drive on your drive.
3-     Open your backup folder and transfer to map network drive.
But again this is the same way I am using before. Only difference is now I don’t need to open network path every time and remember its password to open destination folder there. There is another technique that you can copy backup directly on your network path but your SQL Server service must also configured as network service. Query for that is as mentioned in attached picture.



BACKUP DATABASE Foo TO DISK = '\\myserver\myshare\foo.bak' WITH INIT

Now I have different options to take my backups and move to network path
1-     Make a policy to take regular backups on regular intervals.
2-     Make a job with this command for each backup that run one time daily at free time.

 And last option that I have is
1-     Make a policy to take regular backups on regular intervals.
2-     Create a windows task through task scheduler and create a batch file to use command that copy data from your local drive to a network path

robocopy E:\Daily-Backup\AdvDB1 \\192.168.1.102\Adv *.bak /S /MOV

One important notice is you need administrator group rights for any of this option.

4 comments:

decent said...

hi...
could u teach me more about this..
im trying to do backup with robocopy too..but i dont know how's ur command need to be implement?
plz do contact me..really2 need ur help here...
decentpuppet91@gmail.com

Shamas DBA said...

Check your network path rights and network access..

Prasad V.A. said...

Hi,

I have done the robocopy and its working . Is it possible to delete from the original location with an command line.

Shamas DBA said...

Hi Parsad,

I have not test this myself but here is a link that can help.

http://www.mssqltips.com/sqlservertip/1618/using-the-forfiles-command-to-delete-sql-server-backups/