SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem: Restoring DB comprising 2 FileGroups with logs on another server


Problem: Restoring DB comprising 2 FileGroups with logs on another server

Author
Message
Philip Sparke
Philip Sparke
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 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
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 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
Philip Sparke
Philip Sparke
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
Philip Sparke
Philip Sparke
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 204
UmonitorDB should ready MyDB.

Sorry for the typo

Philip
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88138 Visits: 45277
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

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


vino.basis
vino.basis
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88138 Visits: 45277
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search