SQL Database Recovery Question 2

[Steve101]

I have been been asked to perform the recovery of a database as a disaster recovery exercise.

I have recovered an MDF file taken at the end of last week called MyDataBase.MDF, which represents the full backup taken at this time.

I have also recovered an LDF file taken on Tuesday evening, called MyDataBase.LDF, which represents an incrimental backup taken at this time.

Both of these files have been backed up and recovered to disk using a Veritas "Backup Exec" software agent, which takes copies of the MDF and LDF files.

Given that I have a primary data file dated 5th June 06, and a Transaction Log file dated 8th June 06, I am trying to understand how I "re-constitute" the database from these two files, such that I have a rolled forward version of the database as at 8th June 06.

Whats confusing me, is that I am not recovering from a backup version of the database created by the BACKUP command, so I cant restore using the SQL restore options.

Questions:
(a) I assume that I cannot use the SQL Restore capabilities, because the "backup files" are not in backup format. Is this correct?

(b) An LDF was also taken with the full backup MDF. Is this needed? Im assuming not.

(c) An approach I am considering is detaching the "production" database (in a test environment of course), renaming the files, then copying my two recovered MDF and LDF files into the same folder. I cannot see however how to "roll forward" any transactions that are in the log file but not incorporated in the data file of a few days earlier.

Not being a DBA, I might be missing something fundamental here. If someone could point me in the right direction here, I would be grateful,

TIA,

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

Using the files that you have you don't recover the files.

You can not take an mdf file from one day and an ldf file from another day and merge them together to get a database. SQL will not accept them.

In order to do roll forward logs you need to use the backup database and backup log commands within SQL Server to backup the database and logs to either disk or tape. With these backups you can then restore the database.

Basically the files that you have are useless to you. The only log file that you be able to use would be a log file that was backed up at the same time as the mdf file. These files would ONLY be usable if they were backed up when the SQL Service was stopped or the database was detached (a cold backup). If these files were backed up while SQL Server was running and the database was attached to the SQL Service then the backups are totally useless.

Sorry for the bad news.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steve101]

Thanks Denny,

Fortunately this is not a real situation; I am auditing an existing backup mechanism.

This mechanism seems to imply that by taking midweek backups of LDF files somehow facilitates a capability to re-constitute a database to the last transaction in the LDF from a prior saved full MDF and LDF copy pair. Can you definately confirm that this is NOT the case. I can relate to that, as I havnt seen any documentation to do this.

I may just be uncovering a big hole in the organisations DR procedures.

Also, as stated earlier, they are using a third party backup agent which does the file copies, using a "SQL Agent". Have you heard of "Backup Exec", and if so, could it be possible that they can re-constitute a database using a "Last Friday" database and applying the mid-week transaction log.

Suspect I may have identified a problem with the strategy here. Your final confirmation of this would be appreciated.

Thanks,


Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[SQLBill]

Nope, won't work. Someone has their ideas mixed up. You can only do that type of recovery by doing database and log BACKUPS. Not by copying the .mdf/.ldf files. If you try to restore the .mdf and a later .ldf you will get an error saying that the .ldf is not part of the .mdf (or something like that).

I use Backup Exec and have the SQL Agent (I don't use it). It can only be used to do a BACKUP DATABASE and/or BACKUP LOG command. That's not the same a backing up the .mdf/.ldf files.

You may want to go over the steps they actually follow. Maybe they are doing 'real' backups and not 'copying' the files.

But even if the backups ARE being done correctly, you need to restore the full backup and ALL transaction log backups done since the full backup was done.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

As SQLBill said, Nope it won't work.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steve101]

Thanks guys,
Your information has been most valuable. Unfortunately, whoever put together the backup mechanism over here is long gone. Fortunately, we are reviewing it.

One further question. It appears to me, that SQL Server has all of the capabilities to look after its backup scheduling (both full and incrimental) without the requirement for using a Backup Exec SQL Agent.

I am thinking along the lines of using SQL Server to schedule the backups to disk, and possibly using Backup Exec then to archive the .BAK files to tape; does this sound like a sensible approach.

Cheers,

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

Yep, that's exactly what many companies do. It's not my favorite method but it does work fine.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

That's how I do it. I use SQL Server to make full, differential, and log backups to disk and then Backup Exec 10d to copy the backup files to tape. If I have to restore, I will first try from disk, but if that doesn't work (or isn't available) I'll copy the backup files from tape to disk and restore those.

-SQLBill

Posting advice: FAQ481-4875

 

[Steve101]

Thanks guys for your help.

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)