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.

Post a Comment