SQL Server – Database Restore Steps thru lenses of errorlog

Introduction

Wanted to document database restore steps as seen from trail left in SQL Server Instance’s errorlog file.

Restore

Restore – Full Database

Step Sub Task Logged Explanation

Start Database (Database started in boot-strap mode )
Starting up database ‘csAnalytics’.

Recovery or NO Recovery
NO Recovery
The database ‘csAnalytics’ is marked RESTORING and is in a state that does not allow recovery to be run. Indicate that we are restoring and will not proceed to actual database recovery; as we would have done during normal SQL Server Instance start.
Recovery
Recovery is writing a checkpoint in database ‘csAnalytics’ (9). This is an informational message only. No user action is required. Database restore wrote a checkpoint ledger to indicate progress.
2018-03-20 05:46:51.110    spid70    Recovery completed for database rbpivr1 (database ID 42) in 12 second(s) (analysis 5444 ms, redo 20 ms, undo 5171 ms.) This is an informational message only. No user action is required. Informational in terms of duration.

Here is what is logged – Database name, Assigned Database ID, and total time.

Time breakdown – Analysis, redo, undo.

2018-03-20 05:46:51.550    spid70    Filegroup fgLOB in database rbpivr1 is unavailable because it is Offline. Restore or alter the filegroup to be available. In the case of partial restores, the file groups implicitly excluded.
2018-03-20 11:10:58.790    Backup    Database was restored: Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.bak’}). Informational message. No user action required. Informational in terms of statistics.

Data logged includes name given to target database, creation time, LSN, number of dump/backup devices, and backup device names.


In-Memory Database ( Hekathon )
[INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [42]. Starting offline checkpoint worker thread on a hidden SOS scheduler. Hekathon Offline Checkpoint
Check Database
CHECKDB for database ‘csAnalytics’ finished without errors on 2011-08-03 00:06:43.420 (local time). This is an informational message only; no user action is required. Minimal Database Consistency ran.
Resource DB Alignment
2018-03-20 05:46:51.950    spid70    Synchronize Database ‘rbpivr1’ (42) with Resource Database. Database system objects aligned with Instance’s Resource DB.

Especially important when database backup file restored from earlier SQL Server version.


Restore Complete
Restore is complete on database ‘rbpivr1’.  The database is now available. Restore Completed

Database Setting
Setting database option MULTI_USER to ON for database csAnalytics. Database transitioned from Single to Multi User

 

Restore – Transaction Log

Logged Explanation
Log was restored. Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:422:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.trn’}). This is an informational message. No user action is required. Conveys that Transaction Log backup file was applied to specified database.  It lets us in on the starting and ending LSN.  And, the name of the transaction backup file.

Summary

Data logged in the SQL Server Instance log is quite useful in gleaning what is occurring behind the scene when a database is restored.

Comparing logs from different SQL Server Instances and Database setup affords familiarity with how various database setup and restore options is considered and acted upon by the DB restore facility.

As summary, the code will consider:

  1. Will the database be recovered or left in no_recovery
    • No recovery basically means subsequent transaction logs will be applied
  2. Fast Database Consistency check occurs
  3. Redo and Undo Log Processing
    • Logs contain both redo and undo portions
      • Redo
        • Once database is fully restored, committed transactions that are still in the transaction log, but have yet to be checkpoint into the database data files are applied to restored database datafiles
      • Undo
        • Undo means that data is not committed and still in flight.
        • The data can later be committed ( commit tran ) or discarded ( rollback )
  4. Resource DB Alignment
    • When a database from a previous version of SQL Server is restored unto a newer version the restored db system dictionary has to be brought up to date
  5. In Memory Database
    • In-Memory tables and programmable objects are read from database files into memory

SQL Server – Backup – When offline filegroups exist

Background

A few months we performed a partial restore.

When we tried to backup the partially restored database we ran into potholes.

Issues

Sample SQL and Error Messages

  1. SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’ TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
    • Error Message :- “Msg 3004, Level 16, State 1, Line 1
      The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.”
    • Corrective Measures
      • SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’, READ_WRITE_FILEGROUPS TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
  2. SQL :- “backup database [QA_rbpivr1] TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’
    • Error Message :- Msg 3007, Level 16, State 1, Line 1
      The backup of the file or filegroup “rbpivrLOB_01” is not permitted because it is not online. Container state: “Vacant” (6). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.”
    • Corrective Measures
      • SQL :- “backup database [QA_rbpivr1] FILEGROUP =’PRIMARY’, FILEGROUP =’DATA’, FILEGROUP =’INDEX’, FILEGROUP =’fg_rbpivr1_memory’
        TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’ WITH INIT, FORMAT , STATS=1″

 

Remediation

To backup we have to explicitly specify each filegroup we will like backed up.

And, in doing so, omit the filegroups that are offline.

 

Code

Sample


backup database [QA_rbpivr1]    
FILEGROUP ='PRIMARY'
    , FILEGROUP ='DATA'
    , FILEGROUP ='INDEX'
    , FILEGROUP ='fg_rbpivr1_memory'  
 TO DISK  = 'Z:\SQLBackups\QA_rbpivr1.bak' 
 WITH INIT, FORMAT  , STATS=1

Stored Procedure

 


use [master]
go

if object_id('[dbo].[sp_DBBackupCustomized]') is null
begin

    exec('create procedure [dbo].[sp_DBBackupCustomized] as ')

end
go

alter procedure [dbo].[sp_DBBackupCustomized]
(
      @dbname			sysname
    , @filenameBackup	sysname = null --= 'NUL'
    , @stat				smallint = 1
    , @scriptOnly		bit = 0
    , @debugGUI			bit = 0
)
as
begin

    set nocount on;

    declare @dbid int
    declare @tblFile TABLE
    (

          [id]			int not null identity(1,1)
        , [name]		sysname not null

        , [dataSpaceID] int null

        , [filegroup]	sysname null
                            default ''

        , [type]		int not null
        , [type_desc]	sysname not null

        , [state]		int not null
        , [state_desc]	sysname not null

        , [include]	as 
                        case
                            when (
                                        ( [type] in (0, 2) )
                                    and ( [state] = 0) 
                                 )
                                    then 1
                            else 0
                        end
    )

    declare @tblFileFromDB TABLE
    (
          [id]			int not null identity(1,1)	
        , [name]		sysname
        , [fileid]		int
        , [filename]	sysname 
        , [filegroup]	sysname null
        , [size]		sysname
        , [maxsize]		sysname
        , [growth]		sysname
        , [usage]		sysname
    )

    declare @tblFileGroupFromDB TABLE
    (
          [id]				int not null identity(1,1)
        , [name]			sysname
        , [dataSpaceID]		int
        , [type]			sysname
        , [type_desc]		sysname
    )

    declare @tblFileGroupIncluded TABLE
    (
          [id]		  int not null identity(1,1)
        , [filegroup] sysname not null
    )

    declare @id int
    declare @idMax int

    declare  @cmd nvarchar(255)

    declare  @FORMAT_FILEGROUP_QUERY		nvarchar(4000);
    declare  @FORMAT_BACKUP_CMD			nvarchar(4000);
    declare  @cmdBackup			nvarchar(4000);
    declare  @statAsVarchar     varchar(10)

    declare @fileGroupName		nvarchar(200)
    declare @fileGroupSingle	nvarchar(200)
    declare @fileGroupBuffer	nvarchar(4000)

    declare @fileGroupBufferNotEmpty bit

    declare @CHAR_NEWLINE		varchar(10)
    declare @CHAR_QUOTES_SINGLE	varchar(10)
    declare @CHAR_COMMA			varchar(10)

    DECLARE @pathBackupDefault NVARCHAR(4000) 

    set @CHAR_NEWLINE = char(13) + char(10)
    set @CHAR_QUOTES_SINGLE = ''''
    set @CHAR_COMMA	  = ' , '
        
    set @fileGroupBufferNotEmpty = 0

    set @FORMAT_BACKUP_CMD=
            'backup database [%s] '
                + ' %s ' -- FILEGROUP
                + ' TO DISK '
                + ' = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_NEWLINE
                + ' WITH INIT, FORMAT '
                + ' , STATS=%s'
                
    set @FORMAT_FILEGROUP_QUERY
            = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    set @dbid = db_id(@dbname);

    if (@filenameBackup is null)
    begin

       EXEC master.dbo.xp_instance_regread 
                      N'HKEY_LOCAL_MACHINE'
                    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                    , N'BackupDirectory'
                    , @pathBackupDefault OUTPUT
                    , 'no_output' 

        set @filenameBackup = @pathBackupDefault + '\' + @dbname + '.bak'

    end

    insert into @tblFile
    (

          [name]
        , [dataSpaceID]

        , [type]
        , [type_desc]

        , [state]
        , [state_desc]

    )
    select 
              tblSMF.[name]
            , tblSMF.[data_space_id]

            , tblSMF.[type]
            , tblSMF.[type_desc]

            , tblSMF.[state]	
            , tblSMF.[state_desc]

    from   sys.master_files tblSMF

    where  (

                    --( tblSMF.[state] = 0 )
                    ( tblSMF.[database_id] = @dbid )

           )	

    --has_dbaccess(@dbname)
    --print N' '
    --select @cmd = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    exec master.sys.xp_sprintf
              @cmd output
            , @FORMAT_FILEGROUP_QUERY
            , @dbname

    insert into @tblFileGroupFromDB
    (
          [name]		
        , [dataSpaceID]		
        , [type]	
        , [type_desc]	
    )
    exec (@cmd)

    update tblF
    
    set [filegroup] = isNull(tblFGFDB.[name], '')

    from   @tblFile tblF

    inner join @tblFileGroupFromDB tblFGFDB

            on tblF.[dataSpaceID] = tblFGFDB.[dataSpaceID]

    if (@debugGUI=1)
    begin

        select 
                 [src] = '@tblFile'
                , tblF.*

        from   @tblFile tblF

    end

    set @statAsVarchar = cast(@stat as varchar(10));

    set @id = 1
    set @idMax = ( select max([id]) from @tblFile)

    set @fileGroupBuffer = '';
    set @fileGroupBufferNotEmpty = 0;

    while (@id <= @idMax)
    begin

        set @fileGroupName = null

        select @fileGroupName = tblF.[filegroup]
        from   @tblFile tblF
        where  tblF.id = @id
        and    tblF.[include] = 1
        and    not exists
                    (
                        select 1
                        from   @tblFileGroupIncluded tblFGI
                        where  tblFGI.[filegroup] = tblF.[filegroup]
                    )

        if (@fileGroupName is not null)
        begin

            set @fileGroupSingle = 'FILEGROUP =' 
                                    + @CHAR_QUOTES_SINGLE 
                                    + @fileGroupName 
                                    + @CHAR_QUOTES_SINGLE;
            
            if  (@fileGroupBufferNotEmpty = 1)
            begin

                set @fileGroupBuffer = isNull(@fileGroupBuffer, '') 
                                            + @CHAR_NEWLINE
                                            + @CHAR_COMMA

            end
            else
            begin

                set @fileGroupBuffer = '  ' 
                                        + isNull(@fileGroupBuffer, '') 
                                        + @CHAR_NEWLINE

            end

            set @fileGroupBuffer = @fileGroupBuffer + @fileGroupSingle;

            set @fileGroupBufferNotEmpty = 1

            insert @tblFileGroupIncluded
            (
                [filegroup]
            )
            values
            (
                @fileGroupName
            )

        end

        set @id = @id + 1

    end

    exec master.dbo.xp_sprintf
              @cmdBackup output
            , @FORMAT_BACKUP_CMD
            , @dbname
            , @fileGroupBuffer
            , @filenameBackup
            , @statAsVarchar

    print '@cmdBackup ' + @CHAR_NEWLINE + @cmdBackup

    if (@scriptOnly =0)
    begin

        exec(@cmdBackup)

    end

end

go




SQL Server – Database Restore – Errors – “C:\ClusterStorage\”

Background

Was reviewing SQL Server Agent logs earlier this week and found errors resulting from a database restore SSIS Package.

 

Error

Error Text

The error looks like the one below:


Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Data' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File 'IND_157GIC126_Log' cannot be restored to 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\IND_157GIC126_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.


Error Tabulated

Message ID Sample Explanation
 Msg 5133 Directory lookup for the file “C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\IND_157GIC126_Data.mdf” failed with the operating system error 3(The system cannot find the path specified.). Review the designated folder name and ensure it desired.

If so create and/or grant SQL Server Engine permission.

 Msg 3156  File cannot be restored. Use with move to Identify a valid location for the file Please add the move option to the restore command
 Msg 3199  Problems were identified while planning for the RESTORE statement  Flags errored restore operations
 Msg 3013  RESTORE DATABASE is terminating abnormally.  Restore failed abnormally

 

 

 

TroubleShooting

The error is of course the fact the on the originating SQL Instance the database files are located on C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA  ( Data Folder ) and C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data   ( Log Folder ).

 

Remediate

The fix is to create the folders ahead of time

Stay on Originating Folder

Create Folders

exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_DTA\MSSQL12.SQ01\MSSQL\DATA\'
exec master.dbo.xp_create_subdir 'C:\ClusterStorage\IND069RSQ084_LOG\MSSQL12.SQ01\MSSQL\Data\'

 

Restore Database


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'

 

Use Own Folders

Get File groups from DB Backup File

restore filelistonly
from disk = 'C:\TEMP\IND.bak'</pre>

Create Folders

exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Datafiles\'
exec master.dbo.xp_create_subdir 'F:\Microsoft\SQLServer\Logfiles\'

Use Move Filegroup


restore database [IND]
from   disk = 'C:\TEMP\IND.bak'
with
	  move 'IND_Data' to 'E:\Microsoft\SQLServer\Datafiles\IND_Data.mdf'
	, move 'IND_Log' to  'F:\Microsoft\SQLServer\Logfiles\IND_Log.ldf'
	, replace
	, stats=1

 

SQL Server – Partial Database Restores – Benefits & Impact

Lineage

In our last couple of posts we focused our lenses on partial database restores.

Those posts are here:

  1. SQL Server – Database Restore – “Defunct State”
    here
  2. SQL Server – Database Restore – Skipping File Groups
    here

 

Current

In this post we will look a bit more at the benefits and costs of restoring just a portion of database.

 

Reviews

GUI

Database

File Groups

databaseProperties-FileGroups (cropped-up)

 

Files

databaseProperties-Files

 

 

Tables

dbo.ten95B

tableCount-Table1

 

Storage
  1. Table Name :- dbo.ten95B
  2. File Group :- PRIMARY
  3. Text filegroup :- fgLOB
  4. Data Space
    • 1370 MB
    • or 1.4 GB
  5. Row Count :- 4800

 

Query

Database

Database – Files

Let us query the sys.database_files system table to get the name, type, state, and size of the individual files that are in our current database.

SQL

select 
		  tblSDF.[file_id]
		, tblSDF.[name]
		, tblSDF.[physical_name]
		, tblSDF.[type_desc]
		, tblSDF.[state_desc]
		, tblSDF.size
--		, tblSDF.*
from   sys.database_files tblSDF

Image

Database – Show File Stats

Let us issue “DBCC ShowFileStats” to gather information about our database files.

SQL
dbcc showfilestats

Image

Table

Let us try to read data from table

 

Query Table for Data – All Columns

dbo.ten95C
SQL

select top (1000) *
from [dbo].[ten95C]

Image
filegroupThatCanNotBeAccessed

 

Textual

Large object (LOB) data for table “dbo.ten95C” resides on an offline filegroup (“fgLOB”) that cannot be accessed

 

Query Table for Data – Specific Columns

On the other hand, we are still able to query for specific columns.

When we restrict our column set to those filegroups that were included in our restore, things are good.

dbo.ten95C
SQL

select top (10)
 
   tblT.[ten95C_id]
 , tblT.[ten95C_view_cnt]
 , tblT.[ten95C_view_last]

from [dbo].[ten95C] tblT

Image

Summary

From a cursory look, Microsoft’s design and implementation of Partial database restore is well thought out and solid.

Its preserves all the niceties of having access to the original database design ( database groups and files), metadata for all concerns.

And, rightfully sacrifices storage of the actual data and thus we gain the benefits of not having to provide and maintain storage for uneeded data.

SQL Server – Database Restore – Skipping File Groups

Background

In our very last post we touched on stumbling on an interesting error message courtesy of attempting to do a partial database restore.

The post is here.

 

Database Restore

Full Database Restore

Sample


RESTORE DATABASE [DBLABIMWithInMemoryFiles_R]
 FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIMWithInMemoryFiles.bak'
 WITH  REPLACE,  STATS = 1, 
	  MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Base_01.mdf'
	 , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Data_01.ndf'
	 , MOVE 'DBLABIM_INDEX_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_INDEX_01.ndf'
	 , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIMWithInMemoryFiles_R_log.ldf'
	 , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIMWithInMemoryFiles_R_FILESTREAM_01'
	 , MOVE 'DBLABIM_InMemory_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_InMemory_01'

Partial Database Restore

Sample


RESTORE DATABASE [DBLABIMWithInMemoryFiles_R]
	  filegroup  = 'PRIMARY'
	 , filegroup  = 'DATA'
	 , filegroup  = 'FG_FS_01'
	 , filegroup  = 'FG_MOD_01'

 FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIMWithInMemoryFiles.bak'
 WITH  REPLACE,  STATS = 1, 
	  MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Base_01.mdf'
	 , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Data_01.ndf'
	 , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIMWithInMemoryFiles_R_log.ldf'
	 , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIMWithInMemoryFiles_R_FILESTREAM_01'
	 , MOVE 'DBLABIM_InMemory_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_InMemory_01'
	 , PARTIAL 

 

Reference

  1. Developer Network
    • Database Features > In-Memory OLTP (In-Memory Optimization)  > Backing Up a Database with Memory-Optimized Tables > Piecemeal Restore of Databases With Memory-Optimized Tables
      Link

 

Summary

When scripting a partial database restore, please do the following:

  1. Explicitly list the file groups that you are targeting
    • Where does this clause go
      • Right underneath the name of the target database
      • And, before the with clause
  2. Add the partial clause

 

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.