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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backup Plan

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

Posted - 2011-03-24 : 17:39:22
Your backup plan is flawed. You need to be performing SQL Server backups. Windows Server Backup is not enough to ensure that you get the databases back in the case of a failure.

You need to use BACKUP/RESTORE commands (or similar 3rd party products such as Red Gate's SQL Backup).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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
Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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
Go to Top of Page

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 active

quote:
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 Shaw
SQL Server MVP
Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-25 : 12:10:38
You can use my code with very little knowledge of SQL backups.

Here's how I have my production systems setup (are close to this at least): http://weblogs.sqlteam.com/tarad/archive/2008/06/30/SQL-Server-jobs-on-production-instances.aspx

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.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-25 : 16:15:24
I thought you had an article that went into detail about recovery models and transaction log backups. I could totally be wrong, but I thought there's an article of yours that you post to topics asking about bloated tlogs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -