question

Sagar Bhargava avatar image
Sagar Bhargava asked

Error during a restore

Using SQL Server 2008 R2. I have a situation where additional Log file 'L002' was added to a prod database couple of months ago for few hours to resolve a issue and later dropped from the database. This was removed before the full backup started. This looks to have created an entry in the sysaltfile system table(view) which did not update after the file was dropped. Now, whenever we backup the prod database and try to restore to a Dev box, we get the below error message: *The file 'L002' of restored database 'XXXXX' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal rest.* Database is in Full recovery mode. Now, neither the source or the destination database has a 'L002' file so only place I can see this error comes from is by reading sysaltfiles. There is no entry for this file under sys.master_files. Please help to get this sorted.
restoresql server 2008 r2
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you sure that it's on 2008 R2? I found references to 2012/2014 online but not for 2008 R2. Also, in your development environment, have to tried to detach/attach the files with ATTACH_REBUILD_LOG?
0 Likes 0 ·
Yes, I saw those docs as well. The company hasn't really moved beyond 2008 R2 as yet :-) Not tried the ATTACH_REBUILD_LOG but just the manual restore process with MOVE option.
0 Likes 0 ·
JohnM avatar image
JohnM answered
Try the ATTACH_REBUILD_LOG and only attached the MDF and see if that helps to clear it up. Definitely ONLY in the development environment. ;-)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sagar Bhargava avatar image
Sagar Bhargava answered
Hi John, this did not resolve. Since the entry is in the sysaltfile system table(view) on the PROD server, are there any other fixes that we can try without bringing the database down?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Did you try this on the development server or the production server?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.