SQL Server – Database Restore – “Defunct State”

Background

Courtesy of Microsoft, here is another Why take oneself too seriously moment.

Database Layout

Database FileGroups

Database Files

Scripting

Here is a little code snippet from a database restore script that we are developing.

Intent

  1. To potentially conserve storage we chose to
    • Skip restore of certain filegroups
      • In our sample below, we skipped restoring the INDEX Filegroup

SQL


RESTORE DATABASE [DBLABIM_R]
FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIM.bak'
WITH  REPLACE,  STATS = 1,
	 MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIM_R.mdf'
       , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIM_R_Data_01.ndf'
       , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIM_R_log.ldf'
       , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_R_FILESTREAM_01'
       , PARTIAL 

Error Message

Image

Textual


21 percent processed.
42 percent processed.
64 percent processed.
85 percent processed.
100 percent processed.
Processed 328 pages for database 'DBLABIM_R', file 'DBLABIM' on file 1.
Processed 4 pages for database 'DBLABIM_R', file 'DBLABIM_log' on file 1.
Msg 3127, Level 16, State 1, Line 1
The file 'DBLABIM_Data_01' of restored database 'DBLABIM_R' 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 restore.
Msg 3127, Level 16, State 1, Line 1
The file 'DBLABIM_INDEX_01' of restored database 'DBLABIM_R' 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 restore.
Msg 3127, Level 16, State 1, Line 1
The file 'DBLABIM_FILESTREAM_01' of restored database 'DBLABIM_R' 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 restore.
RESTORE DATABASE ... FILE=<name> successfully processed 332 pages in 0.041 seconds (63.155 MB/sec).

Catch Phrase

  1. Msg 3127, Level 16, State 1, Line 1
    The file ‘DBLABIM_Data_01’ of restored database ‘DBLABIM_R’ 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 restore.
  2. Msg 3127, Level 16, State 1, Line 1
    The file ‘DBLABIM_INDEX_01’ of restored database ‘DBLABIM_R’ 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 restore.
  3. Msg 3127, Level 16, State 1, Line 1
    The file ‘DBLABIM_FILESTREAM_01’ of restored database ‘DBLABIM_R’ 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 restore.

No Error Message

We changed the originating database recovery state from SIMPLE to FULL, and we are good.

Image

Textual


21 percent processed.
43 percent processed.
64 percent processed.
86 percent processed.
100 percent processed.
Processed 328 pages for database 'DBLABIM_R', file 'DBLABIM' on file 1.
Processed 2 pages for database 'DBLABIM_R', file 'DBLABIM_log' on file 1.
RESTORE DATABASE ... FILE=<name> successfully processed 330 pages in 0.035 seconds (73.451 MB/sec).

Summary

Again, there is not too much to this.

It is Saint Patrick’s day and I got Guinnesses in the cooler.

Trying to understand and sort out Microsoft error messages is good for Mondays, as Sunday is my fun day.

And, that is only two days away.

2 thoughts on “SQL Server – Database Restore – “Defunct State”

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s