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.