December 16, 2008 at 10:56 am
OK...I was working on a script / automated way to restore a folder of SQL backups (in the event of server failure)
the Scenario would be similar to a server going down and having to rebuild from scratch...
I came up with a script that would read in the meta data about the backup file and from that you could generate a restore script for each backup file...
I tried testing it on SQL 2005 x64 server and have gotten the following error...
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "Spec" has not been backed up. Use BACKUP LOG
WITH NORECOVERY to backup the log if it contains work you do not want to lose.
Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just
overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I am running a simple statement that has worked for me on SQL 2000
USE master
GO
RESTORE DATABASE Test_Lee_db
FROM DISK = '\\FS000111\sqlbackup\Test_Lee\Test_Lee_db.bak'
WITH NORECOVERY,
MOVE 'Lee_Data' TO 'E:\MSSQL.1\MSSQL\Data\Lee.mdf',
MOVE 'Lee_Log' TO 'F:\MSSQL.1\MSSQL\LOG\Lee_log.ldf',
STATS = 5
GO
RESTORE LOG Test_Lee_db
FROM DISK = '\\FS000111\sqlbackup\Test_Lee\Test_Lee_db.bak'
WITH FILE = 2,
NORECOVERY
GO
RESTORE LOG Test_Lee_db
FROM DISK = '\\FS000111\sqlbackup\Test_Lee\Test_Lee_db.bak'
WITH FILE = 3,
NORECOVERY
GO
RESTORE LOG Test_Lee_db
FROM DISK = '\\FS000111\sqlbackup\Test_Lee\Test_Lee_db.bak'
WITH FILE = 4,
NORECOVERY
GO
RESTORE DATABASE Spec WITH RECOVERY
GO
QUESTION
In the event of a failure, you can't "backup the log tail"...so based on what I have above it won't work in SQL 2005. However if I were to change my code from NORECOVERY to REPLACE it obviously works because it will just replace what ever is in its place.
HOW can I take a backup and attempt to restore the full backup and all the transaction log backups to the point of the last backup?
Because at the moment, what i have isn't working at ALL...and it is irritating me to no end 😀
Thanks in advance!!!
Lee
December 16, 2008 at 11:12 am
Lee Hart (12/16/2008)
In the event of a failure, you can't "backup the log tail"
Depends on the disaster. If the log drive is accessible and SQL is running, you can.
HOW can I take a backup and attempt to restore the full backup and all the transaction log backups to the point of the last backup?
Either backup the tail of the log as the last step or use REPLACE (indicating that you don't care about what's curently there)
Or you can drop the old DB and then restore (in which case there's no DB to restore over)
p.s. can you edit your original post and fix the wrapping please?
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
December 16, 2008 at 11:25 am
Hi Gail,
Thanks for the reply and I did fix my first post, my bad!
Anyways...let me expand a little bit. Lets say that either the server crashes...you loose a C:\ drive (OS) or just one Db goes suspect on you...
Basically you are left with your full backup and all the transaction log backups...that you need to restore from...
In the above scenario you more than likely didn't have the chance to "back up the tail end of the log" thus you would be in a similar situation I am testing out and learning from right now.
After I dropped my current test database I was trying to restore over the top of, the code worked as I previously expected it would...
So basically what I learned is that it you plan on overwriting a database, you need to "backup the tail of the log" to complete the "backup" to which you can then use that backup to overwrite the in place DB with all the logs in sequence...
In a disaster recovery type situation, the previous database shouldn't exist or if it does you would probably drop / delete it so you can proceed with the restore of the backup and the adjoining log files either till the last log backup or to a STOPAT point in time.
Does that sound about right or am I still missing something obvious...
Thanks again Gail for the message.
Lee
December 16, 2008 at 11:34 am
Lee Hart (12/16/2008)
Anyways...let me expand a little bit. Lets say that either the server crashes...you loose a C:\ drive (OS) or just one Db goes suspect on you...
Well, if the C drive crashes, you'll have to restore to a different server anyway. If the DB's corrupt there's still a chance that the tail of the log can be backed up. It depends what caused the DB to be suspect. If the log drive's present and the log is readable, the tail can still be backed up in most cases.
So basically what I learned is that it you plan on overwriting a database, you need to "backup the tail of the log" to complete the "backup" to which you can then use that backup to overwrite the in place DB with all the logs in sequence...
In a disaster recovery type situation, the previous database shouldn't exist or if it does you would probably drop / delete it so you can proceed with the restore of the backup and the adjoining log files either till the last log backup or to a STOPAT point in time.
Does that sound about right or am I still missing something obvious...
Sounds about right.
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
December 16, 2008 at 11:38 am
Thanks for the reply Gail...I guess I was trying to set up a scenario that you needed to recover from completely...
I think I tainted my own test by having the previous database sitting out there and then running the specific code (and not indicating the REPLACE option)...which lead to my frustrations...
I generally have to break something before I start to understand it 😀
Thanks again,
lee
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply