Detaching and Attaching a SQL Database

In this blog I will cover about detaching and attaching the SQL database.

When to go for detaching and attaching a Database?
If you can afford the database to be taken offline and want it to be reused then this is the right option rather than going for backup & restore.
When you want to move the database from one instance to another instance of SQL Server in the same machine this has to be used. Practically during the development time the same database data may be used for development as well as testing. The DB can be taken offline and the mdf & ldf sql files can be shared with testers so they attach the DB. This will help both developers and testers to proceed separately with same data.

Detaching the SQL Database

Takes the SQL DB offline.

SQL Query:

Syntax: EXEC sp_detach_db @DBNname=<<DB Name>>, @skipchecks = <<TRUE or FALSE for skipchecks>>, @keepfulltextindexfile = <<TRUE or FALSE for keepfulltextindexfile>>

Here @DBName is a mandatory parameter.

Usage: sp_detach_db <dbname>, [TRUE|FALSE], [TRUE|FALSE]

Example: EXEC sp_detach_db @DBName = 'DemoDB';

Practically, it can also be used like this.

db_id(<<DB Name>>) is used to check whether the DB exists or not. If DB doesn't exist and if its tried to be detached then the below error is thrown.

Database '<<DB name>>' does not exist. Make sure that the name is entered correctly.

Sometimes the DB may be used and at that time if its tried to be detached then the below error is thrown. Kill the process which is currently attached to our Database and then take the database offline.

Cannot detach the database '<<DB Name>>' because it is currently in use.



SQL


DECLARE @DatabaseName nvarchar(15) = N'DemoDB'

If db_id(@DatabaseName) is not null
    BEGIN
       
        DECLARE @SQL varchar(max)
           
         SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + 
         Convert(varchar, SPId) + ';'
         FROM MASTER..SysProcesses
         WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId   
       
         EXEC(@SQL)

         EXEC sp_detach_db @DatabaseName
       
         If db_id(@DatabaseName) is null
         BEGIN       
            PRINT @DatabaseName + ' is deattached successfully'
         END
    END
Else
    PRINT 'DB does not exist'


SQL PowerShell Script:

This uses the SQL server provider for PowerShell. If its not installed then .Net framework dlls can be consumed and then the query can be executed.

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose

The above code executes the sql query, most of the parameters passed to Inovke-sqlCmd here can be guessed just by their name but still let me brief about each and every parameter that are passed to Invoke-Sqlcmd command.

$SQLQuery is the sql query that has to be executed.

Invoke-Sqlcmd -InputFile "C:\SQLCmd.sql"

Here in our case the query is very small, if in case if the SQL Query is large then the Sql query can be stored as script file and it can be invoked in PowerShell.

$ServerInstance is the SQL server instance that has to be used, if its not passed then the default instance is used.

$DBName is the initial database that has to be used for executing the query, this can also be achieved by having USE <<DB Name>> as the first line in the SQL query.

-Verbose - This displays the SQL messages. In our SQL query we are having Print statement, to show the SQL messages -verbose is employed.



PowerShell


#
# Add the SQL Server Provider.
#

$ErrorActionPreference = "Stop"

#Check whether SQL PowerShell snapin is loaded or not
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and
     (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{
    #Load the SQL Powershell snapin
    write-host "Loading SQL snapin ..."
   
    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    {
        throw "SQL Server Provider for Windows PowerShell is not installed."
    }
    else
    {
        $item = get-itemproperty -path $sqlpsreg
        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
    }


    #
    # Set mandatory variables for the SQL Server provider
    #
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    #
    # Load the snapins, type data, format data
    #
    Push-Location
    cd $sqlpsPath
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Update-TypeData -PrependPath SQLProvider.Types.ps1xml
    update-FormatData -prependpath SQLProvider.Format.ps1xml
    Pop-Location
}

#SQL Query to be executed
$SQLQuery = "  DECLARE @DatabaseName nvarchar(15) = N'DEMODB'

If db_id(@DatabaseName) is not null
    BEGIN
       
        DECLARE @SQL varchar(max)
           
         SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
         FROM MASTER..SysProcesses
         WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId   
       
         EXEC(@SQL)

         EXEC sp_detach_db @DatabaseName
       
         If db_id(@DatabaseName) is null
         BEGIN       
            PRINT @DatabaseName + ' is deattached successfully'
         END
    END
Else
    Print 'DB does not exist'"

#SQL Instance
$ServerInstance = "SQLSERVER"                   

#SQL DB Name
$DBName = "Master"

#Execution
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose


Attaching the SQL Database

This brings the database online.

SQL Query:
sp_attach_db can be used for attaching the database but use of this stored procedure is not advised. 'Create Database' should be used for attaching the database.

If only mdf file is available, then use the below query.



SQL


  CREATE DATABASE SMS_POC
        ON
        ( NAME = SMS_POCDAT,   
            FILENAME = N'D:\Aravind\SQL\SMS_POC.mdf')
            FOR ATTACH ;


If both mdf & ldf files are available, then the below query can be used.



SQL


DECLARE @DatabaseName nvarchar(15) = N'DemoDB'

If db_id(@DatabaseName) is null
    BEGIN
        CREATE DATABASE DemoDB
        ON
        ( NAME = DEMODAT,   
            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDB.mdf')
            LOG ON
        ( NAME = Demo_log,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDBlog.ldf')
        FOR ATTACH ;
       
        If db_id(@DatabaseName) is not null
         BEGIN       
            PRINT @DatabaseName + ' is attached successfully'
         END
    END
ELSE
Print 'DB ' +@DatabaseName+ ' already exists'


PowerShell Script

The below script executes the SQL query to attach the database from PowerShell.



PowerShell


#
# Add the SQL Server Provider.
#

$ErrorActionPreference = "Stop"

#Check whether SQL PowerShell snapin is loaded or not
if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and
     (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{
    #Load the SQL Powershell snapin
    write-host "Loading SQL snapin ..."
   
    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    {
        throw "SQL Server Provider for Windows PowerShell is not installed."
    }
    else
    {
        $item = get-itemproperty -path $sqlpsreg
        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
    }


    #
    # Set mandatory variables for the SQL Server provider
    #
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    #
    # Load the snapins, type data, format data
    #
    Push-Location
    cd $sqlpsPath
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Update-TypeData -PrependPath SQLProvider.Types.ps1xml
    update-FormatData -prependpath SQLProvider.Format.ps1xml
    Pop-Location
}

#SQL Query to be executed
$SQLQuery = "  DECLARE @DatabaseName nvarchar(15) = N'DemoDB'

If db_id(@DatabaseName) is null
    BEGIN
        CREATE DATABASE DemoDB
        ON
        ( NAME = DEMODAT,   
            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDB.mdf')
            LOG ON
        ( NAME = Demo_log,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDBlog.ldf')
        FOR ATTACH ;
       
        If db_id(@DatabaseName) is not null
         BEGIN       
            PRINT @DatabaseName + ' is attached successfully'
         END
    END
ELSE
Print 'DB ' +@DatabaseName+ ' already exists'"

#SQL Instance
$ServerInstance = "SQLSERVER"                   

#SQL DB Name
$DBName = "Master"

#Execution
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose