Tag Archives: Restore Database

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]

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:

MIrror

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

http://Extreme-Advice.com

http://www.sqlhub.com

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

Split SQL Server Full backup in multiple files for faster backup

Split SQL Server Full backup in multiple files for faster backup

I had a situation yesterday to download one full database backup from the data-center to our local network. I have taken compressed backup of our database which was around 90 GB and download 90 GB over internet is still a big thing. I had no other choice but to download only.

We have our own FTP between our data-center and our office and I had to use it only. There is a chance that after downloading few GBs If I get a single packet loss in internet, I would have to start from zero all over again. I was not in situation to install DAP or this kind of software which provides “resume” service if downloading gets stopped due to any reason.

So, I have decided to split backup files in 500MB chunk so at any give time, If I lost internet connection, I loss maximum 500MB data and I can afford to start it again. Think If I start downloading of 90 GB and internet lost for few seconds after I completed downloading of 85GB, I would not be in position to spend that much time again.

Splitting backup in multiple files is not only convenient for storing and transferring but it is faster as well. Let us see how we can take SQL Server backup in multiple files.

Herewith, I am going to take full backup of Adventureworks2012 database in one .BAK file only.

SET STATISTICS TIME ON
BACKUP DATABASE AdventureWorks2012
TO DISK = 'E:\SQL2012DataFiles\Adventureworks2012.bak'
SET STATISTICS TIME OFF
GO

Let us see how much time SQL Server has taken to get this backup in one file.

SingleBackup

Now let us take backup in multiple files:

SET STATISTICS TIME ON

BACKUP DATABASE AdventureWorks2012
TO
DISK = 'E:\SQL2012DataFiles\Adventureworks2012_1.bak'
,DISK = 'E:\SQL2012DataFiles\Adventureworks2012_2.bak'
,DISK = 'E:\SQL2012DataFiles\Adventureworks2012_3.bak'

SET STATISTICS TIME OFF
GO

I have taken backup of Adventureworks2012 database in three files, here is the statistics.

MultipleBackup

You can see backup in multiple file is faster then previous attempt. However, you can’t see major difference as this database is small, you can see major difference if you are having database with few GBs.

Now, it would be interesting to see, how we can restore database from multiple files. Here is the script for the same:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012]
FROM
DISK = N'E:\SQL2012DataFiles\AdventureWorks2012_1.bak'
, DISK = N'E:\SQL2012DataFiles\AdventureWorks2012_2.bak'
, DISK = N'E:\SQL2012DataFiles\AdventureWorks2012_3.bak'
WITH FILE = 2, NOUNLOAD, STATS = 5
GO

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)

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

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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