Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem: Restoring DB comprising 2 FileGroups with logs on another server Expand / Collapse
Author
Message
Posted Thursday, July 14, 2011 3:33 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 27, 2011 3:30 AM
Points: 5, Visits: 204
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
Post #1141572
Posted Thursday, July 14, 2011 6:58 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
you forgot one last step

RESTORE database UMyDB with recovery;


Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #1141721
Posted Thursday, July 14, 2011 7:01 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
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



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #1141729
Posted Thursday, July 14, 2011 11:05 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 27, 2011 3:30 AM
Points: 5, Visits: 204
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

Post #1141947
Posted Thursday, July 14, 2011 11:06 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 27, 2011 3:30 AM
Points: 5, Visits: 204
UmonitorDB should ready MyDB.

Sorry for the typo

Philip
Post #1141949
Posted Thursday, July 14, 2011 11:39 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
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



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #1141976
Posted Thursday, July 14, 2011 11:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1141986
Posted Friday, August 1, 2014 4:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:01 PM
Points: 2, Visits: 4
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
Post #1598824
Posted Saturday, August 2, 2014 4:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
Please post new questions in a new thread, don't hijack an unrelated thread


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1598894
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse