July 14, 2011 at 3:33 am
We have a DB: MyDB (148GB) running on SQL2k5 SE(64bit) 9.0.4035, We recently broke out one large table on to its own FILEGROUP: SECONDARY (84GB) leaving the rest of the database on the FILEGROUP: PRIMARY (64GB). There are 3 log files associated with MyDB: mydb_log, mydb_log2 and mydb_log3. We have two BACKUP DEVICES: MyDBPRIMBKUP and MyDBSECBKUP. MyDB uses the full back-up model and the following scheduled jobs:
BACKUP DATABASE MyDB FILEGROUP = 'PRIMARY' TO MyDBPRIMBKUP WITH INIT (running once a day).
BACKUP DATABASE MyDB FILEGROUP = 'SECONDARY' TO MyDBSECBKUP WITH INIT (running once a day).
BACKUP LOG MyDB TO MyDBPRIMBKUP WITH NOINIT (running every hour).
We wish to restore the latest possible MyDB backup and logs as UMyDB on another server running SQL2k5 SE(32bit) 9.0.4035 so we tried using the following after copying both BACKUP DEVICES to the F: drive on the other server:
RESTORE DATABASE UMonitorDB
FILE = 'mydb_pridata',
FILEGROUP = 'PRIMARY'
FROM DISK = 'F:\MSSQL2k5\BACKUP\MyDBPRIMBKUP.BAK' WITH NORECOVERY,
MOVE 'mydb_pridata' TO 'D:\MSSQL2K5\DATA\UMyDBpridata.mdf',
MOVE 'mydb_secdata' TO 'E:\MSSQL2k5\DATA\UMyDBsecdata.ndf',
MOVE 'mydb_log' TO 'L:\MSSQL2K5\DBLogs\UmDBlog.ldf',
MOVE 'mydb_log2' TO 'E:\MSSQL2k5\DBLOGS\UmyDBLog2.ldf',
MOVE 'mydb_log3' TO 'F:\MSSQL2k5\DBLOGS\UmyDBLog3.ldf'
/*
SYSTEM RESPOINSE:
Processed 7859480 pages for database 'UMyDB', file 'mydb_pridata' on file 1.
Processed 3030 pages for database 'UMyDB', file 'mydb_log' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_log2' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_log3' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 7862510 pages in 2710.250 seconds (23.765 MB/sec).
*/
--UMyDB DATABASE LEFT IN RESTORING STATE!
--Then we restored the secondary filegroup:
RESTORE DATABASE UMyDB
FILE = 'mydb_secdata',
FILEGROUP = 'SECONDARY'
FROM DISK = 'F:\MSSQL2k5\BACKUP\MyDBSECBKUP.BAK'
WITH NORECOVERY,
MOVE 'mydb_pridata' TO 'D:\MSSQL2K5\DATA\UMyDBpridata.mdf',
MOVE 'mydb_secdata' TO 'E:\MSSQL2k5\DATA\UMyDBsecdata.ndf',
MOVE 'mydb_log' TO 'L:\MSSQL2K5\DBLogs\UmDBlog.ldf',
MOVE 'mydb_log2' TO 'E:\MSSQL2k5\DBLOGS\UmyDBLog2.ldf',
MOVE 'mydb_log3' TO 'F:\MSSQL2k5\DBLOGS\UmyDBLog3.ldf'
/*
SYSTEM RESPONSE:
Processed 10560992 pages for database 'UMyDB', file 'mydb_secdata' on file 1.
Processed 1603 pages for database 'UMyDB', file 'mydb_log' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_log2' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_log3' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 10562595 pages in 3689.438 seconds (23.453 MB/sec).
*/
--UMyDB DATABASE LEFT IN RESTORING STATE!
Now we attempt to bring database on-line with latest LOGS:
RESTORE LOG UMyDB
FILE = 'mydb_pridata',
FILEGROUP = 'PRIMARY'
FROM DISK = 'F:\MSSQL2k5\BACKUP\MyDBPRIMBKUP.BAK' WITH RECOVERY,
MOVE 'mydb_pridata' TO 'D:\MSSQL2K5\DATA\UMyDBpridata.mdf',
MOVE 'mydb_secdata' TO 'E:\MSSQL2k5\DATA\UMyDBsecdata.ndf',
MOVE 'mydb_log' TO 'L:\MSSQL2K5\DBLogs\UMyDBlog.ldf',
MOVE 'mydb_log2' TO 'E:\MSSQL2k5\DBLOGS\UMyDBLog2.ldf',
MOVE 'mydb_log3' TO 'F:\MSSQL2k5\DBLOGS\UMyDBLog3.ldf'
Processed 0 pages for database 'UMyDB', file 'mydb_pridata' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_secdata' on file 1.
Processed 3030 pages for database 'UMyDB', file 'mydb_log' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_log2' on file 1.
Processed 0 pages for database 'UMyDB', file 'mydb_log3' on file 1.
The roll forward start point is now at log sequence number (LSN) 428647000013823600001. Additional roll forward past LSN 428648000016889500001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE LOG successfully processed 3030 pages in 1857.375 seconds (0.013 MB/sec).
*/
---------MyDB DATABASE LEFT IN RESTORING STATE! ----
What are we doing incorrectly?
Philip
July 14, 2011 at 6:58 am
you forgot one last step
RESTORE database UMyDB with recovery;
Craig Outcalt
July 14, 2011 at 7:01 am
I should have also said, you may need to synch the login sids and add logins on the new server to allow access.
after you add logins, use the database and run
exec sp_change_users_logins 'report'
to see if you have some SID mismatches.
There is an MS proc out there for migrating logins (complete with SID information) called sp_help_revlogins. that would be another approach.
Cheers
Craig Outcalt
July 14, 2011 at 11:05 am
Many Thanks for your reply, however when we execute:
RESTORE DATABASE UMonitorDB
WITH RECOVERY
we get:
Msg 4303, Level 16, State 1, Line 1
The roll forward start point is now at log sequence number (LSN) 428647000013823600001. Additional roll forward past LSN 428648000016889500001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Do you have any other suggestions?
Philip
July 14, 2011 at 11:06 am
UmonitorDB should ready MyDB.
Sorry for the typo
Philip
July 14, 2011 at 11:39 am
The error means that you restored one of your filegroups farther forward in time than you did the other.
You'll have to restore them to the same point in time then run the command
~C
Craig Outcalt
July 14, 2011 at 11:52 am
You need to restore the next log backup in the chain. That's what the message is telling you, that you need a further roll forward to bring the DB to a consistent point.
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
August 1, 2014 at 4:18 pm
Hi All,
we are also facing same problem when we try to execute "restore database SID with recovery" command, can you help us with solution.
Msg 4303, Level 16, State 1, Line 1
The roll forward start point is now at log sequence number (LSN) 59802000006517200023. Additional roll forward past LSN 59802000006529000001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Regards,
Vino
August 2, 2014 at 4:30 am
Please post new questions in a new thread, don't hijack an unrelated thread
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply