How to backup a mirror SQL Server database or database snapshot

We are using SQL Server 2008 database mirroring between two servers that are on opposite coasts over a VPN connection. This is working well for us.

At the principal location, we perform your typical full backups and transaction log backups.

The mirror location is not set for automatic failover, but we do have instances where we make it the principal to be able to serve our users.

Recently, we had a major power situation at our principal location. The SQL Server was powered down unexpectedly. We found ourselves in a situation where the mirror database was unable to be recovered. We are not sure why, but the reason for it is not the thrust of my question. The fact is that it is possible to lose our principal and then find ourselves in a position where our mirror database cannot be brought online.

At this point, we would need to restore from backups. The problem is that the backups are still at the principal location. We could copy them across the VPN connection, but that would more than double our bandwidth requirements, something we'd like to avoid if we could.

What we really would like to be able to do is have some other process that runs at the mirror location and makes a backup of the mirror database. However, SQL Server will not let you make a backup of a mirror database (because it is always in "Restoring..." mode). We can create a snapshot of the mirror, but that's not a true backup and you cannot backup a snapshot.

You also cannot use the Copy Database wizard to create a copy of a snapshot (it will let you try, but then fail when actually running).

So, long story short, we need a method to backup either a mirror database or a database snapshot. We're open to third party tools, but prefer to not change HOW we are currently mirroring the database (using SQL Server database mirroring).

Looking for ideas. Thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can't.

Whats more whats the point, if you can't bring your secondary on line then backing it up will unlikley to have help you.

If you couldn't bring your mirror online I would suggest it is due to a storage issue which needs to be looked into.

If you changed to log shipping then you have a different story.

As for bandwidth if you compress you backups is that such a big problem.
tresearchAuthor Commented:
simonsabin: Well the point would be to do regular backups /before/ the mirror gets in trouble. I don't suspect a disk issue in this case, fyi, but thank you.

We do compress the backups and they take about four hours to transfer over the wire. We were hoping for a better RPO (around 1 hour). However, that's our fallback at this point.

When you snapshot a mirror, you can read the data. It just seems that if you can read the data, you should be able to back it up.
Which SQL server version do you have?  The enterprise version provides for a way to take a snapshot of the mirror which can then be accessed/backed up.
My guess you were not going through the forcibly bring up the mirror db?

The SQL books online is a useful resource.

I think the issue is that the snapshot name is used, when restoring, you have to change the database name.
The following might be on point for you:
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

tresearchAuthor Commented:
arnold: You can't backup a snapshot.
My mistake, thought that it was part of the options.
misread the process of the backup that mentions taking a snapshot for the backup

When you tried to bring the mirrored DB up, did you go through changing the status of the mirror on the mirrored server to indicate that the connection was severed and then force the mirror online?
This will mean that when the former principal comes back online, the connection will not be reestablished. if you have a witness, the failover would occur automatically.  The issue with a witness, is that if the two nodes are down, the database is inaccessible even if the principal server is active..
tresearchAuthor Commented:
arnold: That's exactly what we did, only the process returned an error, only that's not really the focus of my question here. Just wanting to add an additional layer of protection in case the mirror itself has a problem.
When the principal was unavailable to normally failover the database, the forcible mechanism to bring the mirror up while severing the mirroring connection should have done the trick unless the mirror was out of sync. Have dealt with application that could not work/update the principal database while mirroring was enabled, it is possible that someone severed the mirroring and did not reestablish it.

Maintaining a backup of the principal DB is the right approach, but the backup should not be stored on the same sql server you are backing up.

Depending on your backup plan, i.e. one full daily with transaction logs through the day or one full backup weekly with differential backups daily and transaction log backup through out the day.

Backing up the database accross the WAN i.e. site A is the mirror and site B is the principal, you would have the backup go to site A from the current principal while site A's maintenance job will backup the database when it is the principal to site B.  Depending on when you are doing these backups, the impact on the WAN bandwidth could be tolerated.  The SQL 2005/2008, using the SQL Writer and shadow volume copy will not lock the database for the duration of the backup as they have in the earlier 7/2000 versions.
Jim P.Commented:
Another thought is to step back a level.

My last company used a company called that could do continous disk level backups of drives including SQL server databases.

You can also replicate it continously or on a scheduled basis. With a "DR db" and the sp_help_revlogin I could have the DB's up in an hour and the rest of the apps ready in 3 hours. That was for 6 instances and 80+ databases.

It may be an idea to look at.
tresearchAuthor Commented:
arnold: Thanks for your good advice. That's essentially what we are trying to accomplish but with lower bandwidth usage (since the database is already mirrored over the WAN, why copy the same data a second time).

Your thoughts about the mirror getting broken could really be the cause of the issue. We had found out that our alerting system wasn't working to inform us when our mirroring was getting behind.

jimpen: I think you're on the right track with a third party option. Your URL didn't work for some reason, but I found it here:

I think the solution we are going to go with is with a backup tool we already have called Veeam. Our mirror SQL Server is a virtual machine running on vmware ESX 3.5 and Veeam can make hot backups (supports VSS) of the whole virtual machine, making differential passes on a schedule. This is looking promising for us. If our mirror database is somehow unrecoverable, we'll have various restore points we can go back to.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
For DR you have to have accessible data.  I.e. mirroring is good for a quick/planned failover.
If the principal server goes down or the location becomes in accessible, the forced recovery of the mirrored database is the only option with the possibility of data loss depending on the synchronization model for the mirror.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.