Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cz
Starting Member
19 Posts |
 Posted - 2010-09-24 : 16:37:21
|
| Hi,Say I have a primary and mirroring databases in high performance mode, and the mirroring database is on NORECOVERY mode. Is it possible to backup the database on the mirroring node? The reason is that we would not like overhead on the Primary node when doing backup.Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cz
Starting Member
19 Posts |
Posted - 2010-09-24 : 16:58:18
|
| We use NetApp and a tool SnapManager to do SAN snapshot backup. But one of the issues is that it seems impossible to restore one of our 30+ databases – the restoration has to be all databases or none. Another working around is to put different databases on different disk/LUNs, but that would increase complexity.What other tools would you recommend, Tara? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-25 : 03:33:36
|
| We get questions here from people trying to restore databases which were backed up using database-direct-to-tape and the restores have failed. Far too many things to go wrong IMHO - SQL Server has a service pack update, is the backup software still working correctly?Backup database to disk, then sweep the disk backup files to tape.Added benefit is you can restore from recent backups, that are still on disk, without having to find the tape and wait for restore-from-tape (and, in your case, get all-or-nothing from tape)Disk backup files will be smaller than original database files, possibly significantly smaller if you are using compression during backup, so your tape usage should be less too. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-09-25 : 11:10:38
|
| Using Netapp SnapManager - there is no overhead for the backup. That utility performs a SAN snapshot and tells SQL Server the backups have been completed. It takes just a few seconds for each LUN that is configured.As you have found out though, there are certain limitations with using this. First, you cannot have system databases and user databases on the same LUN. If you have any system databases on the same LUN as the user databases, SnapManager will perform a normal (native) backup for all databases on that LUN.Second, you cannot have multiple SnapManager processes running at the same time. So, if you have multiple LUNs for your user databases - they cannot be backed up at the same time. Nor can you backup the transaction logs at the same time - which also have to be done through SnapManager.And finally, you can restore a single database - you don't have to restore every database on the LUN. The issue here is that to do so, SnapManager has to make a copy of the database from the snapshot, and then restores that copy. When you restore everything on the LUN, the process is simply a replace of the existing LUN using the snapshot.With all of that said, I would not recommend using SnapManager at all. Use SQL Server native backups, Litespeed, SQL Backup or - if all you need is compression Hyperbac. Using these tools gives you more options - and are easier to manage.Last thing - using SnapManager, if you wanted to restore a copy to another server - that server must be using Netapp storage. If not, it is not possible. Using native tools, it's just a copy of the backup files to the other host.Jeff |
|
|
|
|
|
|
|
|
backup the mirroring database?