SQL Server – Create Database from existing files

Background

Hard crash from running out of storage and now it is time to get a new LUN/Volume up.

 

Outline

  1. Get database filenames of database to be moved
    • sys.master_files ( Master Database )
    • sys.database_files ( Contextual Database )
  2. Take database offfline
  3. Re-attach database files
    • Create database for attach

 

Processing

Get database filenames of database to be moved

sys.master_files

SQL

set nocount on;
go

declare @database sysname
declare @dbid     int
declare @sqlFileName  nvarchar(max)
declare @sql  nvarchar(max)

declare @CHAR_TAB           char(1)
declare @CHAR_COMMA         char(1)
declare @CHAR_CRLF          char(2)
declare @CHAR_SINGLEQUOTES  char(1)

set @CHAR_TAB = char(9)
set @CHAR_COMMA = ','
set @CHAR_CRLF = char(13) + char(10)
set @CHAR_SINGLEQUOTES = ''''

/*	
	Specify database name
*/
set @database = 'hrdb'

/*	
	Get database ID
*/
set @dbid = db_id(@database)

select 
        @sqlFileName = 
            isNull(@sqlFileName, '')
            + @CHAR_TAB
            + case
                when tblSMF.file_id = 1 then ' '
                else @CHAR_COMMA
              end   
            + '(FILENAME = '
            + @CHAR_SINGLEQUOTES
            + tblSMF.physical_name
            + @CHAR_SINGLEQUOTES
            + ')'
            + @CHAR_CRLF

from   sys.master_files tblSMF

where  tblSMF.database_id = @dbid

set @sql = 'CREATE DATABASE '
            + quoteName(@database)
            + @CHAR_CRLF
            + ' ON '
            + @CHAR_CRLF
            + @sqlFileName
            + ' FOR ATTACH '
            + @CHAR_CRLF

print @sql

Output



CREATE DATABASE [hrdb]
 ON 
	 (FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb.mdf') --1
	,(FILENAME = 'Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\hrdb_8.ldf') --2
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_0.ndf') --3
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_1.ndf') --4
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_2.ndf') --5
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_3.ndf') --6
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_4.ndf') --7
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_5.ndf') --8
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_6.ndf') --9
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_7.ndf') --10
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdbLOB_01.ndf') --11
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_index_01.ndf') --12
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_9.memopt') --65537
 FOR ATTACH 

sys.database_files

SQL


set nocount on;
go

use [hrdb]
go

declare @database sysname
declare @dbid     int
declare @sqlFileName  nvarchar(max)
declare @sql  nvarchar(max)

declare @CHAR_TAB           char(1)
declare @CHAR_COMMA         char(1)
declare @CHAR_CRLF          char(2)
declare @CHAR_SINGLEQUOTES  char(1)

set @CHAR_TAB = char(9)
set @CHAR_COMMA = ','
set @CHAR_CRLF = char(13) + char(10)
set @CHAR_SINGLEQUOTES = ''''

set @database = db_name()
set @dbid = db_id()

select 
        @sqlFileName = 
            isNull(@sqlFileName, '')
            + @CHAR_TAB
            + case
                when tblSDF.file_id = 1 then ' '
                else @CHAR_COMMA
              end   
            + '(FILENAME = '
            + @CHAR_SINGLEQUOTES
            + tblSDF.physical_name
            + @CHAR_SINGLEQUOTES
            + ')'
            + @CHAR_CRLF

from   sys.database_files tblSDF

set @sql = 'CREATE DATABASE '
            + quoteName(@database)
            + @CHAR_CRLF
            + ' ON '
            + @CHAR_CRLF
            + @sqlFileName
            --+ @CHAR_CRLF
            + ' FOR ATTACH '
            + @CHAR_CRLF

print @sql

Output


CREATE DATABASE [hrdb]
 ON 
	 (FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb.mdf') --1
	,(FILENAME = 'Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\hrdb_8.ldf') --2
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_0.ndf') --3
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_1.ndf') --4
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_2.ndf') --5
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_3.ndf') --6
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_4.ndf') --7
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_5.ndf') --8
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_6.ndf') --9
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_7.ndf') --10
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdbLOB_01.ndf') --11
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_index_01.ndf') --12
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_9.memopt') --65537
 FOR ATTACH 

Take Database Offline

sp_detach_db

SQL


USE [master]
GO
ALTER DATABASE [hrdb] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'hrdb'
GO


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s