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

 

One thought on “SQL Server – Database Restore – Skipping File Groups

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