Background
Hard crash from running out of storage and now it is time to get a new LUN/Volume up.
Outline
- Get database filenames of database to be moved
- sys.master_files ( Master Database )
- sys.database_files ( Contextual Database )
- Take database offfline
- 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