Backup of Tail Log
-
Hello, could somebody help my confusion.
This is out of curiosity rather than emergency.
I am trying to backup and restore a SQL 2000 user database to a SQL 2005 system.
When I run the normal backup command I get the error:
"The tail of the log for the database "machines" 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."
So I go back to the prod SQL2000 server and run a log backup with NORECOVERY and then a full backup.
But when doing the full back up the obvious is realised that the production database has gone into recovery mode and is out of action.
Now I can just "with replace" the database on the test server but i was reading that in theory that could result in a data loss (I assume exists between log backups).
How do I perform this backup and restore without "replacing" and without putting the production DB in recovery mode?
thanks
-
UncleBoris (10/21/2009)
"The tail of the log for the database "machines" 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."You get this error when you are trying to restore over an existing database. Are you trying to replace a DB on the 2005 server with the DB from SQL 2000?
So I go back to the prod SQL2000 server and run a log backup with NORECOVERY and then a full backup.
It's not talking about the prod SQL 2000 database. It's talking about the log of the database that you're trying to overwrite, on the SQL 2005 server.
If you want to restore over an existing database and you don't care about what's in the DB that you're restoring over, use the WITH REPLACE clause in the restore command.
Now I can just "with replace" the database on the test server but i was reading that in theory that could result in a data loss (I assume exists between log backups).
The data loss refers to when you restore a backup over the database that was the source of the backup. Eg
Backup database1 on server1. Full database backup.
3 hours pass in which users are working on that data
You try to restore the backup taken 3 hours ago over the existing database1 on server1. If you do so, you will lose those 3 hours data.
If you're restoring over a database and you don't care about the data in that database, use WITH REPLACE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If you are restoring a full backup, it should not be a problem to use with replace on the test server.
-
UncleBoris. To backup and restore a database, use the following.
Backup your database using backup database ... command. That's all you need to do.
Next, restore your database to the test server using the with norecovery option. This puts the database into a state where you can restore transaction log files. If the database already exists on the test server, you will need to use the with replace option as SQL server will not overwrite an existing database without it.
Backup the log of the restored database. This can be to a file or to the bit bucket as the output is not needed. The transaction log needs to be in a fully backed up state before you can start to restore any transaction log backups.
Restore your transaction logs until:
* you reach the required log file
* you reach a desired point in time
* you reach a named transaction
use restore database ... with recovery to complete the restore process. SQL server assumes recovery unless you specify norecovery. I just feel this it makes the statement clearer.
Hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply