February 10, 2009 at 2:56 pm
Hello,
I have a quick question regarding restoring a database backup with additional transaction log backups.
I have a db backup that runs say at 5 p.m. and hourly log backups that run on the top of the hour...
SO..my backup set has 24 files in there...
Lets say i wanted to restore that FULL DB backup (taken at 5 p.m.) and apply the logs until 21:30 p.m.
I would use the following code...
USE master
GO
RESTORE DATABASE Test_DB
FROM DISK = '\\ServerName\G$\Test_DB.bak'
WITH NORECOVERY,
MOVE 'Test_DB_Data' TO 'E:\MSSQL.1\MSSQL\Data\Test_DB.mdf',
MOVE 'Test_DB_Log' TO 'F:\MSSQL.1\MSSQL\LOG\Test_DB_log.ldf',
STATS = 5
GO
RESTORE LOG Test_DB
FROM DISK = '\\ServerName\G$\Test_DB.bak'
WITH FILE = 2, STOPAT = '2009-02-09 21:30:00.000'
GO
RESTORE LOG Test_DB
FROM DISK = '\\ServerName\G$\Test_DB.bak'
WITH FILE = 3, STOPAT = '2009-02-09 21:30:00.000'
GO
RESTORE LOG Test_DB
FROM DISK = '\\ServerName\G$\Test_DB.bak'
WITH FILE = 4, STOPAT = '2009-02-09 21:30:00.000'
GO
RESTORE LOG Test_DB
FROM DISK = '\\ServerName\G$\Test_DB.bak'
WITH FILE = 5, STOPAT = '2009-02-09 21:30:00.000'
GO
RESTORE LOG Test_DB
FROM DISK = '\\ServerName\G$\Test_DB.bak'
WITH FILE = 6, STOPAT = '2009-02-09 21:30:00.000'
GO
Evey thing would restore just fine and when it hit that 5th log file restore it apparently does a RECOVERY option on the db to bring it back online...
SO...when the 6th log file restore attempts to run it errors out...with the following error message...
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Is this considered normal?
Reason I am asking is lets say I had a server issue and you had to restore the entire server to a P.I.T. I certainly wouldn't want to manually figure our which log file was in the time frame of my P.I.T. restore time frame...
I am trying to come up with a script that will automatically build the restore statements and one of the scenarios I wanted to test out was P.I.T. restores...
however the script looks at all the log file backups and appends them in order...
So..instead of stopping specifically at the log file restore that specifically pertains to that P.I.T. it builds the statement for ALL log file restores...but after it hits that P.I.T. I get nothing but those errors
Again I believe it is because the database is been restored to that P.I.T. and change to a RECOVERED status...and is erroring out on the remaining log file backup statements.
I thought it would just discard those statements but I guess it errors out...
Does anyone know if there is a different statement to use or do I just have to know which log file backup contains the log restore P.I.T.
I did look in BOL for some info but all I got out of that was the following
If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.
So to me that means exactly what I thought...that if the P.I.T. isn't inside that specific log backup file then it moves onto the next one...but I didn't see anything about what happens when you reach a log backup file that is after the P.I.T.
So that is what I am curious about...
Thanks!
Lee
February 10, 2009 at 3:09 pm
There are backup history tables in msdb that you can use to build an automatic PIT restore script. Since they have the backup time in them, as well as the type and file name and all that, it's pretty easy to build a proc that takes a database name and a datetime parameter for the stop-at, and have it generate your restore commands for you.
That might solve what you're looking for.
- 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
February 10, 2009 at 3:14 pm
GSquared (2/10/2009)
There are backup history tables in msdb that you can use to build an automatic PIT restore script. Since they have the backup time in them, as well as the type and file name and all that, it's pretty easy to build a proc that takes a database name and a datetime parameter for the stop-at, and have it generate your restore commands for you.That might solve what you're looking for.
Hi There,
I was aware of the tables...however since this is the first time I have run the code to actually do the restore to a P.I.T. I was curious if the error messages I was getting we common (maybe I didn't word it the best in the OP)...
I would assume so but again don't like to assume.
Thanks,
Lee
February 10, 2009 at 3:25 pm
If the point-in-time selected is in log file 5, then yeah, it would be usual. It finishes at that point, and doesn't need to go any further, so it's done.
- 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
February 11, 2009 at 9:21 am
GSquared (2/10/2009)
If the point-in-time selected is in log file 5, then yeah, it would be usual. It finishes at that point, and doesn't need to go any further, so it's done.
Thanks for the confirmation and I made the adjustment to my script...
To elaborate on the way and the why of how I wrote my script...I did it in a way that I don't have to use the tables in msdb and the script looks at the folder that holds the backup files specifically...
That way I can use the back files for restores on test servers from any point in time and they don't have to tied to the msdb. (hopefully that makes some sense)
Anyways thanks again GSquared for the info!
Lee
February 12, 2009 at 7:27 am
I built a proc at one point specifically for point-in-time restore from the production server to the test server, to have fully up-to-date data and schema, for testing migration scripts and such.
It used a linked server to query msdb on the other server, a CLR component to copy the appropriate files over, including the most recent full backup, the most recent diff backup, and any log backups since that, restored to the last log backup, then cleaned up the files out of the folder they had been copied to. Worked beautifully. And not a cursor/loop in it (I was proud of that), but I have to admit it was probably more work to build it than it was really worth.
Use whatever works 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply