Mirror backup to take full backup at multiple location in SQL Server
Sometime back, one company environmental invited me to investigate some performance related issues there were facing. While investigating issues, I seen their backup strategy. They were taking full backup everyday as they were having database near to 150GB and after taking full backup, they were transferring full backup to external drive as well as in network path by using xcopy command in batch file. This incident forced me to write this article.
Recently we have talked about splitting full database backup in multiple file in one of my previous article. Today I want to show one more useful command “Mirror” for database full backup which helps DBA to copy database full backup file to different location.
Generally we tend to take full backup at regular interval and copy it somewhere else, may be in network location or may be in external drive attached to server. If you are copying backup manually to another location, “Mirror” command is helpful to you, if you have SQL Server 2005 or later version.
All you need to manage is permission. SQL Server service account/login suppose to have proper permission on the drive/network path to write something there.
I am talking full database backup of my AdventureWorks2012 database into local “E” drive as well as at network path. My SQL Server service account has full permission for write in my network path.
USE [master] GO BACKUP DATABASE AdventureWorks2012 TO DISK = 'E:\Temp_Testing\AdventureWorks2012.bak' MIRROR TO DISK = '\\WinServ2008R2\DBBackupTesting\AdventureWorks2012.bak' WITH FORMAT GO
After executing the above command, I come across following message:
Keep updating yourself with technology enhancement will always makes you more productive and efficient.
Knowledge is power!!!!
some more reading about backup & restore:
Find database backup history in SQL Server (Click Here)
Find database restore date in SQL Server (Click Here)
Backup SQL Server database with encrypted data column/table and restore it somewhere else (Click Here)
Split SQL Server Full backup in multiple files for faster backup (Click Here)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.