Tag Archives: database backup

Mirror backup to take full backup at multiple location in SQL Server

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]


BACKUP DATABASE AdventureWorks2012

TO DISK = 'E:\Temp_Testing\AdventureWorks2012.bak'

MIRROR TO DISK = '\\WinServ2008R2\DBBackupTesting\AdventureWorks2012.bak'



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)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah



Note: Microsoft Books online is a default reference of all articles.

Find Database backup history in SQL Server

Find Database backup history in SQL Server

question-markI had one article yesterday which finds out database restore history by querying some of the system table resides in MSDB database. After reading that article, I got question in my “Quick Learn” forum as well as in Twitter too that how can we find database backup history?

In fact, we needs to find database restore history only in rare case but it is mandatory for DBA to keep a track of the backup history. I have already written article which provides TSQL script which will cater the need of finding database backup history. Click here to see the script.

BTW, recently I have started one section in “Quick Learn” forum where you can come and ask me any SQL Server related question. Click here to ask me any SQL Server or .NET related question.

We have experts working in different Microsoft Technology and if you are facing any issues in .NET, SQL Server, Windows Azure or open source technology, do raise your question here from this URL.

BTW, the main idea to start “Quick Learn” is to provide the stage to learn/share short tips related to any technology so if you are having good tips to share, you are more than welcome at “Quick Learn”  and at the same time, if you want to learn some short tips, do drop by at “Quick Learn” .

I welcome you all to register there in “Quick Learn“. here is the link for registration.

Reference: Ritesh Shah



Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Do I need backup of my database?

If you are working as a DBA or you have responsibility to maintain your SQL Server database up & running, take my words, you have NO OPTION of full database backup.
Recently I came to know that one person is not taking backup just because he has RAID and Mirroring setup so he is least worried about backup. Believe me, no matter what redundant hardware / software and high availability setup you have, you MUST take backup regularly because even good and up-to-date disaster recovery system could get failed. So, you never know, when and how do you need your latest backup file.
Let us discuss why database backup is really that much important.
Suppose you have high availability solutions like RAID or server clustering but what if you hardware gets corrupt and may be complete disk array get corrupt? You have to build up your database from backup only.
Sometime, catastrophic events or natural disasters like flooding, earth quake etc. could damage your hardware or may be complete data center and if you have no backup at other location, you have no place to go for.
May be sometime Security issues comes to the picture and somebody intentionally or unintentionally damage you data which could be affected adversely and in that situation you might look for the latest database backup to restore that data.
Sometime your OS or even your SQL Server gets corrupted and you might need to restore data from backup itself.
In short, I would HIGHLY recommend database backup as “Prevention is always better than cure”. So, keep strong backup policies with full database backup, differential database backup and transaction log backup, depends on your need and business policies.
BTW, I am not against the high availability concepts like mirroring, clustering, replication or log shipping. You should implement those too as per your business needs but also MUST keep strong backup policies.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah