Sql-server – the best way to automate a migration of multiple SQL Databases to a different hard disk

sql serversql server 2014t-sql

We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names all remain the same.

What is the best, safest way to do this in an automated fashion?

My initial idea was to use a sort of pseudo ForEach Loop, supply a list of database names, and have it loop through queries to backup and restore or detach and re-attach the databases, but I have not been able to properly insert the database name variables into SQL queries within a loop.

Best Answer

Something as simple as the below can construct your script

SELECT
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

You can then take the output and save it, or you could potentially wrap it further to turn it into dynamic SQL to actually run it

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

SELECT @SQL +=
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

PRINT @SQL
--EXEC (@SQL)

Note the use of REPLACE to deal with the change of file location

For additional parameters and commands the above script can be edited easily to include things such as setting the database to single_user or offline.

Following this the SQL Server should be brought offline, the files moved in the file system, and the server brought back online.

Related Solutions

Sql-server – attach database – verify – detach from a script or a batch file

This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.

# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file names somehow and generate attach/check/detach statements
foreach($db in $databases) {
  $dbName = [regex]::Replace($db.Name, "(?i)\.mdf", "")
  $lgName = [regex]::Replace($db.FullName, "(?i)\.mdf", ".ldf")
  $sqlCmds += $("CREATE DATABASE {0} ON (FILENAME = '{1}') LOG ON (FILENAME = '{2}') FOR ATTACH;"  -f $dbName, $db.FullName, $lgName)
  $sqlCmds += $("DBCC CHECKDB ({0});" -f $dbName)
  $sqlCmds += $("EXEC sp_detach_db @dbname='{0}';" -f $dbName)
}

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Server=SERVER\INSTANCE;Database=master;Integrated Security=True"
$s = $sqlCmds[0]

# Try and execute the statements   
try {
    foreach($s in $sqlCmds) {
      $ret = $conn.ExecuteNonQuery($s)
      $ret
    }
} catch [system.exception] {
  $_.Exception.ToString()
}

Sql-server – What Are the Log File Names

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }

Best Answer

Related Solutions

Sql-server – attach database – verify – detach from a script or a batch file

Sql-server – What Are the Log File Names

Related Question