Yikes! Crash! It finally happend.

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Yikes! Crash! It finally happend.

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 14:10:15

Last night around 9pm the hard drive that was hosting our SQL Server ERP Production database crashed, to say the least. We do full tape backups every night at 11pm, which means we can restore a full backup from 9/29 at 11pm. This means we lost all data for yesterday.

But wait! I could be a hero or a loser here. Back in December 2003 I started hourly transactional backups every hour from 6am to 7pm, which I put on a different hard drive which is ok. The TRX Log file is there, and it's almost 1GB.

I'm just looking for some advice on restoring these backups. Here's my plan...

Restore Full Backup from 9/29/11PM
Restore Trx Backup from 9/30/6am
Restore Trx Backup from 9/30/7am
Restore Trx Backup from 9/30/8am
Restore Trx Backup from 9/30/9am
etc.... up to the final restore of the TRX Backup at 9/30/7PM.

Is this right? Do I need to do anything in between these restores?

Can I just use EM for restoring these backups? Is that reliable?

Will it let me know if I do something out of sequence?

Me knees are shakin! Any advice/confirmation is greatly appreciated. I'm reading BOL Restore process now.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 14:21:42

Use Query Analyzer for this. Here are the steps:

RESTORE DATABASE DBName
FROM DISK = 'F:\FullBakup.BAK'
WITH NORECOVERY

RESTORE Log DBName
FROM DISK = 'F:\1stTlog.TRN'
WITH NORECOVERY

RESTORE Log DBName
FROM DISK = 'F:\2ndTlog.TRN'
WITH NORECOVERY

...

RESTORE Log DBName
FROM DISK = 'F:\LastTlog.TRN'
WITH RECOVERY, STOPAT = '09-30-2004 12:00 AM' --select time that is a minute or a couple of minutes before the crash

Verify data.

Now the STOPAT part is only if you were able to get to the last TLog backup that includes the minutes before the crash. If you don't have that, then the last statement will exclude the STOPAT part.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 14:51:02

OK, my backup file is one file with all of the hourly backups in it. How do I specify it to restore all of the backups in proper order?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 14:53:39

Eeks!

Why one file? You should be writing to multiple files.

You will need to use WITH FILE= in the queries. To find out which file to use inside the files, run RESTORE FILELISTONLY.

RESTORE FILELISTONLY
FROM DISK = '...'

Do that for each of your files. So are you now going to need WITH NORECOVERY, FILE = N and WITH RECOVERY, FILE = N

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 14:57:48

Well, I set this up as one job, to execute the hourly transaction log backups and append them to the same file. That's why one file. I have a process each night that initializes this backup file after the full backup.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 15:06:12

I did this...

RESTORE FILELISTONLY FROM DISK = 'D:\SQL Backups\Production Trx Log Backup'

What's FILELISTONLY telling me? I only see two rows returned, basically stating the physical address of the mdf and ldf db files.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 15:08:30

Here is what my hourly backup was doing from 6am-7pm...
BACKUP LOG [JDE_PRODUCTION] TO DISK = N'D:\SQL BACKUPS\Production Trx Log Backup' WITH NOINIT , NOUNLOAD , NAME = N'JDE_PRODUCTION Transaction Log Backup', NOSKIP , STATS = 10, DESCRIPTION = N'Hourly Backup of JDE Production Trx Log', NOFORMAT

I also had this process running every night at 11:45pm
BACKUP LOG [JDE_PRODUCTION] TO DISK = N'D:\SQL BACKUPS\Production Trx Log Backup' WITH INIT , NOUNLOAD , NAME = N'JDE Trx Log Backup - Init', NOSKIP , STATS = 10, DESCRIPTION = N'Trx Log Backup with INIT', NOFORMAT , NO_TRUNCATE

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:13:48

Ooops, I meant:

RESTORE HEADERONLY FROM DISK = '...'

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-01 : 15:23:00

I can't believe I'm saying this. I would actually do this in EM. :) In EM, you can just check that file as the one you want to restore. It will show you then entire history. You can click on all of them and it will just restore them in order. It's pretty simple. Learn how to do it right later when you aren't in an emergency.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:25:01

Fire Derrick! Lame ass DBA.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 15:25:19

OK, now I see the 15 backup files. Do I do this?...
RESTORE DATABASE [JDE_PRODUCTION] FROM DISK = 'D:\SQL Backups\Production Trx Log Backup' File = 1
WITH NORECOVERY

RESTORE DATABASE [JDE_PRODUCTION] FROM DISK = 'D:\SQL Backups\Production Trx Log Backup' File = 2
WITH NORECOVERY

RESTORE DATABASE [JDE_PRODUCTION] FROM DISK = 'D:\SQL Backups\Production Trx Log Backup' File = 3
WITH NORECOVERY

etc...

Then when I get to File 15 should I do the RESTORE LOG with the RECOVER option? The last backup was at 7pm, and the crash happend at 9pm. So I don't think I will be using STOPAT. We resign to the fact that we will lose 2 hours of data.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:26:01

Yes use WITH RECOVERY option on the last tlog backup.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 15:28:10

Unfortunately, when I look at it in EM I cannot see the datetime order of the files, & I know I need to restore these in the proper order.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:30:12

I think EM will handle the order for you.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 15:52:59

On my first restore, it told me I new to use the NORECOVERY option must be used because the previous restore did not use it. The previous restore was from the tape backup which did a recover. My command is as follows...

RESTORE LOG [JDE_PRODUCTION] FROM DISK = 'D:\SQL Backups\Production Trx Log Backup' WITH File = 1,
NORECOVERY

What am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:56:21

You first need to restore the full backup. The full backup restore command should specify WITH NORECOVERY as well.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 15:58:14

But it didn't. It was a standard recovery from Veritas backup software. Do I have to restore the full backup again or is there another way around this? Maybe I should try EM?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 15:59:56

I don't know how to tell Veritas software to not recovery it, so...

Move the full backup file to disk, then run RESTORE DATABASE...WITH NORECOVERY on it.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 16:02:08

No other way around this? The full backup restore takes most of the day.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-01 : 16:03:57

No. You either have to run the restore via Veritas and tell it not to recover it (if that's even possible) so that tlogs can be applied or copy the file from tape (which probably is faster than restoring from tape) then run the restore using RESTORE command.

BTW, this is why backups to disk is recommended then copying the file to tape.

Tara

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-10-01 : 16:09:03

Ugh. Gonna try EM real quick since we are gonna have to restore the whole thing again anyway. What about it Derrik?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources