| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2011-03-24 : 17:37:13
|
| OK, need a reality check on my backup plan:OS is on Drive 1 (which is mirrored RAID 1).Data (MDF) is on Drive 2 (which is RAID 6, can lose up to 2 disks).Log Files are kept on Drive 1 so it's not on the same drive as the MDF.Backup is done every 30 minutes using Windows Server Backup (which uses VSS) which is backed up to Drive 3. If Drive 1 (OS) goes down, I restore the whole drive with WSB.If Drive 2 goes down I restore the MDF with WSB, and then use the log file from drive 1 to recover it fully.If drive 3 goes down, I just replace it.So, I think I have all my bases covered, but just wanted a reality check in case I missed anything. Greg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-24 : 18:11:11
|
quote:
If Drive 1 (OS) goes down, I restore the whole drive with WSB.If Drive 2 goes down I restore the MDF with WSB, and then use the log file from drive 1 to recover it fully.
In both cases SQL is likely to mark the DB suspect as soon as it starts up. The mdf and ldf are a matched pair, you cannot take an mdf from one time, an ldf from another and expect it to work. In fact, a consistent backup of the two using WSB is unlikely unless it integrates with SQL to freeze IOs during the backup.A SQL backup strategy involves SQL backups, full, differential if needed and log backups for point-in-time recovery.--Gail ShawSQL Server MVP |
 |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-03-24 : 22:18:13
|
| OK, this is the test I just ran:At 9PM, I run a backup job which includes the SQL Server mdf. This is using Windows Server Backup (included with Windows Server 2008) which is different from NT backup. At 9:02, I add data to a table.I then take the database offline, and rename the database to something else to simulate a database failure. If I try to take it back online, it of course fails.I then run a restore job from the backup at 9:00. It restores the MDF file. I then bring the database back online successfully.I then open up the table where I made the changes, and like magic, the changes are already there. Meaning that SQL server somehow recognized that the database was out of synch and reapplied the transactions from the log.So, I don't get where this restore process is flawed as it seems to work. (I am just trying to understand the issue!)Greg |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-24 : 22:21:14
|
| It is flawed because there is no guarantee that you'll be able to repeat that same process with success. You must use SQL Server backups in order to guarantee that your databases are recoverable. It is flawed because the MDF file is open by SQL Server, so there is no guarantee of its state. In order for file backups to work successfully is for the service to be stopped or for the database to be detached. Backing up the open file is just a waste of time due to this because it is so dangerous to rely on it.Only SQL Server backups should be used to recover the databases.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-03-24 : 22:40:16
|
| OK. As a note, I tried restoring an ldf from a backup and matching it with a current mdf, and that did not work at all.Greg |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-25 : 01:48:08
|
quote:
Originally posted by gregoryagu
I then open up the table where I made the changes, and like magic, the changes are already there. Meaning that SQL server somehow recognized that the database was out of synch and reapplied the transactions from the log.
It's called crash recovery. It's the process SQL uses when starting up to ensure that the database is consistent. It requires that the log records are still in the log and still in a state where SQL thinks they are activequote:
So, I don't get where this restore process is flawed as it seems to work (I am just trying to understand the issue!)
It worked this time, because the time between the log file and the mdf that you restored was small. It will NOT work if there's been a long time, long enough that SQL has marked the log records inactive and overwritable. If you would like, I'll try a scenario on my own machine later on (not windows backup, just copy-replace) and show you just how bad things can go.Let's put it this way. If you're happy playing russian roulette with your database and data, then use this 'backup' plan. Just know that it's shear chance if you can recover from anything serious using it. --Gail ShawSQL Server MVP |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-03-25 : 11:34:06
|
| Hi Gail,Thanks for the data, I agree with you entirely. I somehow had the idea that the logfile was a sort of backup in itself which could be stored on a seperate hard drive and used along with an earlier backup. It's there some sort of primer on doing native backups? I saw Tara has written some backup related stored procedures and was wondering if there was a walkthrough on how to use them. I am a Silverlight programmer trying to pinch hit on setting up the SQL Server backups and I really don't know what I am doing.Greg |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2011-03-25 : 12:29:45
|
| Perfect, that is just what I was looking for. Thank you very much.Greg |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-25 : 16:03:59
|
quote:
Originally posted by tkizer
Depending upon if you want point in time capabilities, you may just want to schedule full backups and use SIMPLE recovery model. Gail has an article that goes into detail about that.
I do?--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-26 : 06:52:20
|
| Yes, log backups and why not to shrink logs. Doesn't really cover full backups in simple recovery though. More what to do and what not to do with the logs.Not necessarily something for a complete beginner. I need to rewrite it also...--Gail ShawSQL Server MVP |
|
|
|
|
Backup Plan