March 16, 2011 at 11:29 am
SQL 2000 (8.0.760)
Database: Apollo
Full Recovery
Logs taken every 4 hours
I am trying to restore this to the most recent time possible. But I have some questions with regards to the recovery state options listed below:
* Leave the db ready to use by rolling back uncommitted transaction (RESTORE WITH RECOVERY)
* Leave the db non-operatinal, and do not roll back uncommitted transactions. (RESTORE WITH NORECOVERY)
*RESTORE WITH STANDBY
So let me know if I have this straight...
I would use the WITH RECOVERY on the most recent LOG restore to bring the database back into a RECOVERED state where users can then access it.
VS
Using the NO RECOVERY would leave the database in a recoverING state (locking out users) allowing me to apply a TAIL OF THE LOG Restore (which would use the WITH RECOVERY option) b/c that would be the last LOG to restore and I would want all users to have access again after that.
At what point in these steps do I take the TAIL OF THE LOG backup? I'd imagine that would be the first step right? Also, if I were to take a tail log backup first thing will that effect the LSN? In other words will the system be smart enough to know if I take a tail log backup that IT (the tail log) will be the last log file to be restored thus using the WITH RECOVERY after it?
Thanks
March 16, 2011 at 11:46 am
You will definitely need to do the tail log backup before you start restoring, unless you're restoring to a different server/instance, or to a different database name. Otherwise, you'll overwrite the log when you do the first restore step.
You restore the full backup with norecovery, then all log backups up to the last one with norecovery, then the last log backup with recovery.
It's been a while since I used SQL 2000 or Enterprise Manager (if that's what you're using, based on the forum you posted in), but in Management Studio for 2005/2008, if you right-click a database and select Tasks-Restore, it will give you a list of the restorable backups on the wizard screen, and you can simply either type in a point-in-time to restore to, or select "Most Recent Time Possible", or even just check a box next to the file you want to restore last, and then either run it or click "Script", and it'll take care of the details for you. If you are using Enterprise Manager, and it doesn't have that option (I don't remember whether it does or not), get a copy of Management Studio Express (it's free), and see if that'll handle it for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 11:49 am
GSquared (3/16/2011)
You will definitely need to do the tail log backup before you start restoring, unless you're restoring to a different server/instance, or to a different database name. Otherwise, you'll overwrite the log when you do the first restore step.You restore the full backup with norecovery, then all log backups up to the last one with norecovery, then the last log backup with recovery.
It's been a while since I used SQL 2000 or Enterprise Manager (if that's what you're using, based on the forum you posted in), but in Management Studio for 2005/2008, if you right-click a database and select Tasks-Restore, it will give you a list of the restorable backups on the wizard screen, and you can simply either type in a point-in-time to restore to, or select "Most Recent Time Possible", or even just check a box next to the file you want to restore last, and then either run it or click "Script", and it'll take care of the details for you. If you are using Enterprise Manager, and it doesn't have that option (I don't remember whether it does or not), get a copy of Management Studio Express (it's free), and see if that'll handle it for you.
Yeah I'm using SSMS. But you say "it'll take care of the details for you"...does that mean it will perform a tail of the log also?
March 16, 2011 at 11:53 am
Vertigo44 (3/16/2011)
GSquared (3/16/2011)
You will definitely need to do the tail log backup before you start restoring, unless you're restoring to a different server/instance, or to a different database name. Otherwise, you'll overwrite the log when you do the first restore step.You restore the full backup with norecovery, then all log backups up to the last one with norecovery, then the last log backup with recovery.
It's been a while since I used SQL 2000 or Enterprise Manager (if that's what you're using, based on the forum you posted in), but in Management Studio for 2005/2008, if you right-click a database and select Tasks-Restore, it will give you a list of the restorable backups on the wizard screen, and you can simply either type in a point-in-time to restore to, or select "Most Recent Time Possible", or even just check a box next to the file you want to restore last, and then either run it or click "Script", and it'll take care of the details for you. If you are using Enterprise Manager, and it doesn't have that option (I don't remember whether it does or not), get a copy of Management Studio Express (it's free), and see if that'll handle it for you.
Yeah I'm using SSMS. But you say "it'll take care of the details for you"...does that mean it will perform a tail of the log also?
No. You have to do that first. I mean it'll take care of which files to restore, in what sequence, and using which recovery options.
I found it very educational to use the Create Script button on the wizard and see what it came up with, when I was first learning how to do point-in-time restores.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 11:56 am
Got it! Thanks!
I was scripting out to a new windows each of the options to see where the difference was. That's when i noticed the lack of norecovery at the final statement which I assume means the system will default to recovery.
March 16, 2011 at 12:03 pm
Vertigo44 (3/16/2011)
Got it! Thanks!I was scripting out to a new windows each of the options to see where the difference was. That's when i noticed the lack of norecovery at the final statement which I assume means the system will default to recovery.
It should. I prefer explicit code vs implicit, so I would add the recovery option to it. But that's a personal preference, not a requirement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 2:31 pm
Ah Crap!
Here's what happend.
I restored using this...
RESTORE DATABASE [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_db_201103152300.BAK' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103160000.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103160400.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103160800.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103161200.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103161600.TRN' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Then realized the data that was accidently deleted a day ago would be RE-deleted if I ran all transaction to present like I just did. So the second time around I used the stop at and got this error.
Ideas why?
RESTORE DATABASE [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_db_201103132300.BAK' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103140000.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103140400.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103140800.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103141200.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103141600.TRN' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103142000.TRN' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'2011-03-14T16:22:32'
GO
10 percent restored.
20 percent restored.
30 percent restored.
40 percent restored.
50 percent restored.
60 percent restored.
70 percent restored.
80 percent restored.
90 percent restored.
100 percent restored.
Processed 103608 pages for database 'Apollo', file 'apolloData' on file 1.
Processed 1 pages for database 'Apollo', file 'apollolog' on file 1.
RESTORE DATABASE successfully processed 103609 pages in 104.355 seconds (8.133 MB/sec).
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103140000.TRN'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103140400.TRN'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103140800.TRN'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103141200.TRN'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_tlog_201103141600.TRN'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 0, Line 1
The log in this backup set begins at LSN 2283000003592900001, which is too late to apply to the database. An earlier log backup that includes LSN 2283000002336000001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
March 16, 2011 at 2:34 pm
Do your log backups get purged periodically? Or archived offsite or something like that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 2:38 pm
We use Tivolli on some of our servers. But that only does a copy. I literally ran these a few mins apart. I'm thinking the first restore process may have a lock on the .trn files? Is that possible/plausable?
March 16, 2011 at 2:40 pm
The error is that the device is not available. Are the files where you're expecting them to be? Check the folder referenced in your restore commands and see if the files are there. Also check the error log, as per the error message (it says there's more info there).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 2:49 pm
Database was stuck in Restoring state so I ran this
RESTORE DATABASE [Apollo] FROM DISK = N'e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Apollo\Apollo_db_201103152300.BAK' WITH FILE = 1, REPLACE, RECOVERY, NOUNLOAD, STATS = 10
GO
10 percent restored.
20 percent restored.
30 percent restored.
40 percent restored.
50 percent restored.
60 percent restored.
70 percent restored.
80 percent restored.
90 percent restored.
100 percent restored.
Processed 103888 pages for database 'Apollo', file 'apolloData' on file 1.
Processed 1 pages for database 'Apollo', file 'apollolog' on file 1.
RESTORE DATABASE successfully processed 103889 pages in 100.485 seconds (8.469 MB/sec).
Now how can I manually apply the
March 21, 2011 at 1:19 pm
I restored the database back to 3/16 full but never loaded any logs. I then told the database to recover. Now I can't find the 3/16 transaction logs. Does SQL server delete them from the file system knowing the database was recovered without them therefore it thinks they aren't needed?
Is there a way to apply transactionl logs after issuing the recovery statement?
March 21, 2011 at 1:34 pm
Vertigo44 (3/21/2011)
Does SQL server delete them from the file system knowing the database was recovered without them therefore it thinks they aren't needed?
Absolutely not. Database restored in no way means backups aren't needed. If they're gone some process or person deleted them
Is there a way to apply transactionl logs after issuing the recovery statement?
No. Drop the database and start the restore again from the full.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2011 at 4:49 am
GilaMonster (3/21/2011)
Vertigo44 (3/21/2011)
Does SQL server delete them from the file system knowing the database was recovered without them therefore it thinks they aren't needed?Absolutely not. Database restored in no way means backups aren't needed. If they're gone some process or person deleted them
Is there a way to apply transactionl logs after issuing the recovery statement?
No. Drop the database and start the restore again from the full.
Gila,
Being that this is a 2000 database I can't just open up the maintenance tasks and see what they are setup to do. However, I did discover that the jobs that were created to run full and log backups were also doing a cleanup after 4 days! This is why I came back from the weekend to find those backups missing I believe. We have tape backup and I am looking into that now.
Thanks!
March 22, 2011 at 5:04 am
Vertigo44 (3/22/2011)
However, I did discover that the jobs that were created to run full and log backups were also doing a cleanup after 4 days! This is why I came back from the weekend to find those backups missing I believe. We have tape backup and I am looking into that now.
Yup, that'll do it. Thanks for the followup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply