• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2118
  • Last Modified:

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.
0
tresearch
Asked:
tresearch
1 Solution
 
simonsabinCommented:
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.
0
 
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.
0
 
arnoldCommented:
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?
http://msdn.microsoft.com/en-us/library/cc917680.aspx
http://technet.microsoft.com/en-us/library/ms175511.aspx
http://technet.microsoft.com/en-us/library/ms175876.aspx

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:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/f5d42c70-3bef-4e0c-b800-cc556c39377d
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tresearchAuthor Commented:
arnold: You can't backup a snapshot.
0
 
arnoldCommented:
My mistake, thought that it was part of the options.
misread the process of the backup that mentions taking a snapshot for the backup
.
http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/

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?
http://technet.microsoft.com/en-us/library/ms189270.aspx
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..
0
 
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.
0
 
arnoldCommented:
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.
0
 
Jim P.Commented:
Another thought is to step back a level.

My last company used a company called http://faclonstor.com 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.
0
 
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: http://www.falconstor.com/

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.

http://www.veeam.com/vmware-esx-backup.html
0
 
arnoldCommented:
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.

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now