May 29, 2010 at 3:01 pm
I’ve restored a full and diff backup (in norecovery), but I can’t restore the trans log backup stored in my BU folder. I get the error that
"The log in this backup set terminates at LSN 30000000031100001, which is too early to apply to the database. A more recent log backup that incluses LSN 30000000032600001 can be restored. RESTORE LOG is terminated abnormally.
So, I performed a RESTORE HEADERONLY
RESTORE HEADERONLY
FROM DISK = N'F:\BU\AdventureWorksDW_May29_2010_TRN.trn'
This returned 3 rows in a table and one of them has the LSN that is being asked for. It belongs to the file that I tried to restore. In fact each row is associated with the same .trn file, just an earlier version of it. But, because I have been using NOINT for all subsequent transaction log backups, I only have one .trn file in my backup foler and can’t get around the error. Any suggestions?
In the event there is no solution for recovering the transaction log, how do I restore the database, since it is in norecovery mode?
What table am I looking at when I use the RESTORE HEADERONLY command?
Thanks, Helen
May 30, 2010 at 3:40 am
From the error, I'd say that log backup was taken before the Diff, which means it's not needed for the restore. You should start with the first log backup after the differential ran.
If you have multiple tran log backups in the same file (not a really good idea), you need to specify which one to restore. The option you need is the FILE = option. See Books Online for details
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
May 30, 2010 at 7:08 am
Thanks for replying Gilamonster.
I have been saving to the same trans log using NOINT. so, there's only one trans file.
I just checked the last LSN in the dif file that I restored and found it had the same LastLSN as the one in the transaction log.
So, last question:
Is SQL Server 'smart', in that it won't let you restore a subsequent file if it recognizes that there is nothing additional that is in the file you are trying to restore that isn't already restored?
May 30, 2010 at 10:50 am
hxkresl (5/30/2010)
Thanks for replying Gilamonster.I have been saving to the same trans log using NOINT. so, there's only one trans file.
I would strongly suggest that you change your backups so that each tran log backup goes to its own individual file. Makes it easier to restore and less likely that a single file corruption will damage more than one backup
Is SQL Server 'smart', in that it won't let you restore a subsequent file if it recognizes that there is nothing additional that is in the file you are trying to restore that isn't already restored?
Yes for tran log backups, as you've seen from the error message that you got.
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
May 31, 2010 at 2:52 am
I've noted your recommendation to create seperate tran log backups. Got it.
To avoid the problem of having the same LastLSN in more than one backup I redid the practice backup & restore operation, making sure to INSERT a row of data into the AdventureWorksDW database between each backup. That went fine but restore not so well. Please take a look and tell me what I am doing wrong.
First, the steps I took before deleting the whole AdventureWorksDW database.
USE AdventureWorksDW
GO
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before FullBackUp')
-- Back up the AdventureWorksDW database
BACKUP DATABASE AdventureWorksDW
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH INIT, COMPRESSION;
GO
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before DiffBackUp')
--Back up the AdventureworksDW differential
BACKUP DATABASE [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_DIF.dif'
WITH DIFFERENTIAL, INIT
GO
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before TransBackUp')
-- Back up the AdventureWorksDW transaction log
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH INIT
GO
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before SecondTransBackUp')
--Back up the AdventureWorksDW transaction log (second)
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH INIT
GO
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before Restore')
--check table to make sure all values there: looked good.
SELECT * FROM dbo.DimDepartmentGroup
--using config manager I stopped instance, deleted database, and restarted instance.
Next, I began the restore.
USE master
GO
--Restore full
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY
GO
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "AdventureWorksDW" 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.
In response to that I tried to back up the tail, but because the database is no longer available it failed:
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH NORECOVERY
Msg 945, Level 14, State 2, Line 1
Database 'AdventureWorksDW' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
How can I begin to restore this database? I look forward to your reply as I am trying very hard to learn how to backup and restore a database recapturing all data!
Thank you.
May 31, 2010 at 3:26 am
Do your initial restore WITH REPLACE. That tells SQL that you're happy to overwrite the existing database.
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
May 31, 2010 at 9:40 am
When Gail stated that you should back up to individual files, one of the things this does is makes sure you don't do this:
-- Back up the AdventureWorksDW transaction log
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH INIT
GO
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before SecondTransBackUp')
--Back up the AdventureWorksDW transaction log (second)
BACKUP LOG [AdventureWorksDW]
TO DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH INIT
GO
In the above, you have just wiped out the previous log file backup. By doing so, you have lost the ability to restore to a point in time before the second transaction log backup was taken. Once you initialize the file, the data that was in the file previously is gone.
What you need to do is create a new file each time you create a backup, regardless of the type of backup being created. I use a timestamp for each file - that way I can visually identify the files in a folder and validate whether or not there are missing files.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 10:33 am
OK. I will not use WITH INIT or WITH NOINT when I create log files. Thank you, Jeffrey, for sharing the naming convention/best practice that works for you as I will most likely adopt it.
Nevertheless, the backups went without incident. Do you see why the first restore didn't work?
May 31, 2010 at 10:44 am
It's really not an issue using WITH INIT/NOINIT - it's the reuse of the same file name. It's fairly trivial to generate a file name based upon a date and pass that to the backup command. If you use the same file name, then you end up wiping out the previous data.
Since you are testing, that is okay - but for production code you don't want to wipe out that data. You want to be able to copy the backups to offline storage (tape, etc...) AND be able to use those files to recover in the event of a disaster. If you wipe out the files, you can only go back to your offline storage (if you have that available).
Working this way, you do have to build in a process to manage the files. You have to identify how much storage is available on the drive being used for backups - how many versions (days) worth of backups to keep and remove anything older than that. The idea is to have as much online and available for restore as is possible. Going to offline storage for a restore takes much more time - but, that is your DR solution in case you lose the data center.
As for why you failed before, yes - Gail already answered that. If you are restoring over an existing database you need to use the REPLACE option - or, you have to take a tail log backup before starting the restore. In your case, you can't take the tail log backup because you removed the database - so you just need to use the REPLACE option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 10:56 am
Making progress. Very grateful to this forum as I wouldn't touch a production db before getting this straight.
Now, in this second example that I gave, I wasn't trying to overwrite an existing database. I am trying to restore a database that has been "accidentally" deleted. If you read my notes you'll see I said "-using config manager I stopped instance, deleted database, and restarted instance." So the replace option doesn't fit the scenario, does it?
The problem restated is:
I have backed up a database with FULL, DIF, and TRN logs, in that order. I then stop the instance that the database resides on, delete it, and restart the instance. I then go to RESTORE the database with a standard restore command and I am told to back up the tail of the log. Since there is not database this doesn't work but I am unable to move forward.
May 31, 2010 at 11:01 am
Yes, the replace option exists because SQL Server still knows about the database. When the system was restarted, the database would show up as suspect in this scenario.
If you had actually deleted the database from SQL Server (without stopping), then the entry in SQL Server and the files would have been deleted. In that scenario, the replace wouldn't be needed.
If you just detach the database - the files are left and you would need the replace option or you would use the move option to move the files to a new location/name.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 11:22 am
Bear with me.
The database is gone. ie. There is no expandable "+" next to the database in Object Explorer, I can't open it. It is also gone from the MSSQL/DATA folder.
When I try to restore with REPLACE here is what happens. (I am leaving in with Norecovery as well because I have a dif and trn log to recover as well.)
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH REPLACE, NORECOVERY
GO
THE BELOW SUGGESTS I've given the wrong path...but it would be senseless for me to point it to the DATA folder as there is nothing to replace there.
Error that i get:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
If you look at my original post you will see that I have backed up properly (minus the best practice with transaction logs) and I have had no incident. I have deleted the original database at which point I try to restore. All the backups are present in my G:\BU\ folder however, SQL Server is asking me to back up the tail of the log.
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY
GO
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "AdventureWorksDW" 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.
May 31, 2010 at 11:36 am
The error is telling you that it cannot find the backup file you have specified. Validate the backup file actually exists where you think it does and the name is the same.
If that is correct, then validate that the login running SQL Server has access to the directory and file where the backup file exists.
Just for clarity - I always use the MOVE option in the restore command to make sure the files are placed where I want them and not necessarily the default locations. This is not related to the problem you are having, but is a recommendation so you can be sure the files are placed where you want them.
Oh - is the G:\ drive a local drive or is it a mapped drive?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 12:12 pm
hxkresl (5/31/2010)
Bear with me.The database is gone. ie. There is no expandable "+" next to the database in Object Explorer, I can't open it. It is also gone from the MSSQL/DATA folder.
The files may be gone, but the database metadata is still there, it's still visible in object explorer and SQL thinks it's still important. Hence you have to either drop the database (not the files) with DROP DATABASE or use REPLACE to tell SQL that yes, you are restoring a backup over an existing database (one with the same database name)
The files are quite immaterial here.
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
May 31, 2010 at 12:31 pm
Some good news. Thank you for the good info about metadata (lingering) and sharing the practices such as MOVE. These are very good to gain along the way...
I issued the DROP DATABASE command and was able to complete the first step of the .bak restore.
--Restore full
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_FULL.bak'
WITH NORECOVERY
GO
A happy moment here. 😛
I proceeded to the .dif restore ...
--Restore Diff
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May29_2010_DIF.dif'
WITH RECOVERY, REPLACE
GO
Another happy moment. 😛
Finally, the transaction log restore:
RESTORE LOG [AdventureWorksDW]
FROM DISK = N'G:\BU\AdventureWorksDW_May31_2010_TRN.trn'
WITH RECOVERY
GO
Msg 3117, Level 16, State 1, 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.
I executed the following INSERT between the dif and trn backup so there is definitely data in the trn backup that I want to restore....
INSERT INTO dbo.DimDepartmentGroup
VALUES (1, 'Before TransBackUp')
I bet I could ignore the trn and reissue a restore with recovery now but then I would be trashing some 'important' data.....(you can see why I'm practicing).
Thank you.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply