I recently had a very interesting experience. In one of my recent consultancy works, I was told by our client that they are going to take the backup of the database and will also a copy of it at the same time. I expressed that it was surely possible if they were going to use a mirror command. In addition, they told me that whenever they take two copies of the database, the size of the database, is always reduced. Now this was something not clear to me, I said it was not possible and so I asked them to show me the script.
Here was their script:
BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\AdventureWorks.bak' , DISK = N'E:\AdventureWorks.bak' WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup' GO
This script was very interesting to me. There is nothing wrong with it; however it does not duplicate your data. In fact, it splits your backup file in two parts and, when you restore, you will need both of these files. This was the reason why the size of the backup file was reduced whenever they took a backup on two places.
What they really needed was the following script wherein the full backup would be mirrored to another backup location:
BACKUP DATABASE [AdventureWorks] TO DISK = N'D:\AdventureWorks.bak' MIRROR TO DISK = N'E:\AdventureWorks.bak' WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup' GO
In this case, when you check the size of the backup, you will notice that its size is not reduced as well.
I have previously written on this subject in very much detail. Here is the in-depth article about the same topic:
SQL SERVER – Mirrored Backup and Restore and Split File Backup
Do you use this MIRROR TO command in your production environment?
Reference : Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Dear Pinal Sir,
I was download one database from internet but i
don’t know how to attach it to sql server so please tell me
thank you
Ashutosh S.
It it is a backup file try restoring it. If database file, attach it
let me make it clear
1- is my windows server where all the website and database is been hosted and is live .
2 -is my laptop.
ok
1-
if i am taking full backup from my laptop of database
backup db to disk = ‘1-C-drive ‘ it is running successfully
as per example
here {1} means——- server C drive path—–
and
2-
backup db to disk = ‘2-C-drive ‘it is throughing error
here {2} means—– laptop C drive path.——
How to create Database Backup using Triggers.
Sir,
I have Microsoft SQL Server 2008 R2 Standard edition that hosted Microsoft Dynamics AX 2009 databases.
IT Teams did not perform any backup on OS.
The databases was backing up in local drive by previous DBA.
I am planning to purchase an external hard drive 5 TB and connected to it via USB.
Then, performed the SQL Database backup.
Questions:
Would you advise and recommend the best options?
Thanks,
Edwin
Way we use WITH FROMATE, INIT command sir.
NICE
Thanks sir your experience and knowledge share is wonderful.
I am reading your article from last two months and i found an amazing things.
i am using sqlserver 2005 and is not working.
Backup mirroring is not available in this edition of SQL Server. See Books Online for more details on feature support in different SQL Server editions.
Vandana,
What is the output of Select @@version
This feature is avaible only in enterprise edition
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017
version is :9.00.5000.00
& product of “Microsoft SQL Server Express Edition”
so how can I do it
Using express you cannot. that’s limitation of free version. Only enterprise edition has this feature.