Problems restoring filegroup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problems restoring filegroup

Author

Topic

marco gwiliani
Starting Member

25 Posts

Posted - 2008-04-05 : 07:48:04

I have a backup of my PRIMARY filegroup saved as a .bak from a webserver.

I am trying to restore this to a fresh copy of my database on my local PC. The database has a PRIMARY filegroup and 1 additional filegroup - which contains READONLY data, but is not set as readonly.

First I complete a taillog backup, then I restore the PRIMARY filegroup - no problems so far. Finally I try to restore the taillog (with recovery) - however I get the following error. Can anyone help? (note, I have already searched google, msdn and BOL but don't understand what I am missing) rgds MArco

Msg 4326, Level 16, State 1, Line 2
The log in this backup set terminates at LSN 182000000043700001, which is too early to apply to the database. A more recent log backup that includes LSN 183000000022100001 can be restored.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

--Perform a tail-log backup
use master
backup log mydatabase
to disk = 'C:\POTail.bak'
with norecovery

--Restore PRIMARY filegroup
use master
restore database mydatabase
filegroup = 'PRIMARY'
from disk = 'C:\PRIMARY.bak'
with partial, norecovery

--Restore tail-log
use master
restore log mydatabase
from disk = 'C:\POTail.bak'
with recovery

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-05 : 12:56:56

You have transaction log backup in between.It has LSN. Check sysbackupsets or Error log for the time when you took Log backup in between

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-05 : 23:23:46

Where did you get filegroup backup file? Log backup you like to restore should be taken form same db on same server.

marco gwiliani
Starting Member

25 Posts

Posted - 2008-04-06 : 04:14:11

What I am trying to do is simulate a server problem. I have the following backups from my webserver:

1. A full backup of DB
2. A full backup of PRIMARY Filegroup. (the secondary filegroup is read only, so I do not back this up nightly, only the PRIMARY file group.
3. A copy of the log file from the database - .ldf

Firstly, I am trying to simulate that the server has completely gone. So I would only have 1 + 2 (not the .ldf) I Restore 1 on my local PC (WITH NO RECOVERY), I then restore the PRIMARY file group (WITH PARTIAL, RECOVERY, REPLACE). This works in terms of the PRIMARY filegroup, however I cannot access data in the second filegroup. I get a message saying it is not online?

Secondily, I complete as above however PRIMARY restore = (WITH PARTIAL, NORECOVERY), then I try to restore 3 (.ldf) but get the below error:

The media family on device 'C:\XXX.ldf' is incorrectly formed. SQL Server cannot process this media family.

thanks, a frustrated MArco!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-06 : 10:58:04

1)If you restore 1 ,everything will be restored and you can apply tran log backup
2)you can't restore LDF files .Do transaction log backup and restore?
3)Try restoring FG backup(Norecovery) and transactional backup taken after that.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-06 : 16:03:19

1. If you need access both file groups, restore full backup only. Once you restored primary filegroup backup, the db contains that file group only.

2. You can't restore log from db's log file. Need log backups.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-06 : 17:31:30

Thats what i mean too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources