Can we backup a SQL database that is on SAN with traditional backup method?
-
Hi All,
Can we backup a SQL database that is on SAN with traditional backup method rather than using the Snap Manager for SQL (SMSQL)?
My SAN vendor is saying that I shouldn't because we can only use one application taking care of the transaction log, otherwise things can get pretty stuffed up. He hasn't given me any example of what might happen, but just a written email from them stating that we shouldn't.
I know that one concern would be - how to bring it back online in case of a DRP situation. But, I think we can get around this as follows:
- Take a snapshot of the database (including truncate transaction log),
- Then take a traditional backup of the database, and then
- Take another snapshot of the database.
The only time this will get stuffed up is when we have a database that is constantly being written to. In this scenario, there is a risk of losing some transactions when we try to restore it.
Any ideas guys?
Thanks heaps in advance.
-
Is traditional backup means native sql server backup?
If the answers is yes...
You can take take native backup for SAN attached databases... I do that everyday...
You can take native backup as well as snap manager backup without breaking the tlog sequence as long as your SAN snap manager supports sql backups....
Why snap manager truncates log?
Truncate the tlog means you lost your recoverabilily (point in time recovery) unless you have taken differential immediately.
MohammedU
Microsoft SQL Server MVP -
Yep, by traditional methods I meant the native SQL backup method.
we truncate the log immediately after taking a snapshot, to avoid the transaction log from filling up and in turn having to increase the LUN space.
BTW, which SAN are you using? we are using NetApp, and I have checked again that NetApp's Snap Manager for SQL does not support the SQL backups. It doesn't show them in the restore tab.
-
We use HP...
SAN backups will not showup in restore tab...
SAN backups are done by freezing the db and copying the ldf and mdf files not like backup files...
I don't see any reason to truncate the log to manage it...
you should run the tlog backup every 30/60 minutes or so ...
MohammedU
Microsoft SQL Server MVP -
So, do you reckon if we weren't truncating the transaction I could actually use the traditional method without stuffing up anything in regards to DRP?
-
I'd be very wary on relying on any SAN based backups for recovery, most do not fully support COW and in a disaster/failure a recovery may well return inconsistant databases. I'd seriously suggest you stick to native sql backups and get your san based backups to backup the sql backups. There's a series of whitepapers under the heading "Always On" somewhere on the ms sql server site which give a basic understanding of the subject area.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Good Day Colin - could you translate the acronym COW please (Iit would be rather ridiculous to google it) ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I had no problem googling it: http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-24,GGLG:en&q=SQL+Backup+COW+SAN
COW = Copy on Write
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Silly me ... I only did SAN, COW ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I believe a SAN COW would be a database that fills the whole SAN by itself.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
We've never had any problems with doing traditional SQL backups on our SANs. We just set our maintenance plans to delete backups after X number of days and Transaction logs after 1 day. The backup files save just fine.
Of course, after that point, our Server Admin then make a tape backup of the SAN drive that they in turn keep for X number of days so we have double-redundancy in our backup plans.
-
Rudy, I do apologise for using stupid acronyms - I've done quite a bit of san work of late, including snia ( http://www.snia.org ) certification so I have loads more acronyms to throw into conversations < grin >
The main issue with many san based snapshots or replications is that the process of freezing the devices and backing up doesn't allow the writes across multiple databases/files/devices to complete which means when the databases are recovered they may be inconsitant. A simple one db + one t log might not be so bad but in a case where a business process spans a number of databases the "snap" may occur before the write for the whole transaction has completed. Most dr tests use "controlled " tests which do not fully replicate a true failure - Different vendors have different solutions, most claim to do things they probably can't, but you'll never know until the fatal day when it doesn't !!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Sorry for chiming in late on this too, guys, but we are in the process of installing our SAN right now and our vendor is talking about the same thing...eliminating log-shipping and relying on the SAN backup, which, he did say, would take a backup of the drives and files on the drives and truncate the logs and put them in a separate folder. In case of disaster, they said that we could just attach our standy server to the SAN array and bring everything back up. This doesn't sound right to me. Am I missing something? What backup strategy should we employ with the new SAN? Should we continue to do our native SQL backups with log-shipping? Can someone give a detailed explanation or point me in the direction of some reading, please? Thanks.
Chris
-
Can't speak for SQL Server, but when we had backups on DB/2 and Oracle, they did do a copy/split of the disks on the SAN and there was a brief pause on the db while they did this. The split was then moved to tape and once it was finished, they disposed of it.
Not sure of the technical process since the SAN guys handled it, but it worked. I prefer the native SQL backups and leave the SAN guys out of it. They've got enough stuff to worry about and I can't chance their backup gives me an inconsistency.
-
SAN backups freez the db befor it takes the snap of the db files and later normalizes...
Always sql native backups are more reliable...If you make backups faster and the backup file smaller you can use third party tools like LiteSpeed ...
MohammedU
Microsoft SQL Server MVP
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply