Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD.

Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive.

I have found this MSDN article, but that seems to be the procedure to move only one database.

Is there an automatic way or script to move all my databases at once?

share|improve this question
add comment

3 Answers

I use Powershell for this sort of work. In fact, I use Powershell to generate Powershell, because I have a script that will loop through my databases and generate my final move script. You will have to move each database one at a time, but this will at least help you script 90% of the work.

#load SMO
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Create server object and output filename 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost"
$outputfile=([Environment]::GetFolderPath("MyDocuments"))+"\FileMover.ps1"

#set this for your new location
$newloc="X:\NewDBLocation"

#get your databases
$db_list=$server.Databases

#build initial script components
"Add-PSSnapin SqlServerCmdletSnapin100" > $outputfile
"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile
"[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') `"localhost`" | out-null" >> $outputfile
"`$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile

foreach($db_build in $db_list)
{
    #only process user databases
    if(!($db_build.IsSystemObject))
    {
        #script out all the file moves
        "#----------------------------------------------------------------------" >> $outputfile
        "`$db=`$server.Databases[`""+$db_build.Name+"`"]" >> $outputfile

        $dbchange = @()
        $robocpy =@()
        foreach ($fg in $db_build.Filegroups)
        {
            foreach($file in $fg.Files)
            {
                $shortfile=$file.Filename.Substring($file.Filename.LastIndexOf('\')+1)
                $oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf('\'))
                $dbchange+="`$db.FileGroups[`""+$fg.Name+"`"].Files[`""+$file.Name+"`"].Filename=`"$newloc`\"+$shortfile+"`""
                $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"

            }
        }

        foreach($logfile in $db_build.LogFiles)
        {
            $shortfile=$logfile.Filename.Substring($logfile.Filename.LastIndexOf('\')+1)
            $oldloc=$logfile.Filename.Substring(0,$logfile.Filename.LastIndexOf('\'))
            $dbchange+="`$db.LogFiles[`""+$logfile.Name+"`"].Filename=`"$newloc`\"+$shortfile+"`""
            $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"
        }

        $dbchange+="`$db.Alter()" 
        $dbchange+="Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;`" -Database `"master`"" 

        $dbchange >> $outputfile
        $robocpy >> $outputfile

        "Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;`" -Database `"master`""  >> $outputfile
    }
}

The output will be a FileMover.ps1 script in your MyDocuments folder that looks something like this:

    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') "localhost" | out-null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server 
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorks2012_Data"].Filename="X:\NewDBLocation\AdventureWorks2012_Data.mdf"
    $db.LogFiles["AdventureWorks2012_Log"].Filename="X:\NewDBLocation\AdventureWorks2012_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorks2012_Data.mdf /copyall /mov
    ROBOCOPY "C:\DBFiles\Log" "X:\NewDBLocation" AdventureWorks2012_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET ONLINE;" -Database "master"
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012DW"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorksDW2012_Data"].Filename="X:\NewDBLocation\AdventureWorksDW2012_Data.mdf"
    $db.LogFiles["AdventureWorksDW2012_Log"].Filename="X:\NewDBLocation\AdventureWorks2012DW_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorksDW2012_Data.mdf /copyall /mov
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorks2012DW_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET ONLINE;" -Database "master"

...

Caveats

  • The script moves all files, no matter their source location, to the same destination. You will need to tweak for custom location paths.
  • The script is designed to run on the server you need to move the files on (see all usages of localhost'). Replace localhost with your instance name if you run this remotely.
  • The user you run this as needs access to all folder paths involved in the move, both for updating the SQL server filename information and for moving the files.
  • I use the InvokeSQLCmd for Offline/Online execution because of the funky nature of the .SetOffline() and .SetOnline methods. I've found this to be more reliable.
share|improve this answer
1  
+1 Best use of powershell automation. –  Kin Jul 17 '13 at 21:04
add comment

You can use either Alter database Modify File or Detach/Attach methods.

Note: Both will require some downtime, so has to be done during maintenance window.

This assumes that you have same directory structure on the new drive e.g. C:\data\ and D:\Data.

-- using Alter database with Modify method (preferred one)

SET NOCOUNT ON

DECLARE @datafile VARCHAR(255)
    ,@logfile VARCHAR(255)
    ,@dbid TINYINT
    ,@SQLText VARCHAR(max)
    ,@dbname VARCHAR(255)
    ,@sqltext1 VARCHAR(max)
    ,@SQLText2 VARCHAR(max)

--2. Prepare for modify
IF EXISTS (
        SELECT 1
        FROM tempdb..sysobjects
        WHERE NAME LIKE '%#filetable%'
        )
BEGIN
    DROP TABLE #filetable
END

CREATE TABLE #filetable (
    mdf VARCHAR(255)
    ,ldf VARCHAR(255)
    ,dbid TINYINT
    ,dbname VARCHAR(100)
    ,fileid TINYINT
    ,logicalname SYSNAME
    )

--
INSERT #filetable (
    mdf
    ,dbid
    ,fileid
    ,logicalname
    )
SELECT physical_name
    ,database_id
    ,data_space_id
    ,NAME
FROM sys.master_files
WHERE data_space_id = 1

INSERT #filetable (
    ldf
    ,dbid
    ,fileid
    ,logicalname
    )
SELECT physical_name
    ,database_id
    ,data_space_id
    ,NAME
FROM sys.master_files
WHERE data_space_id = 0

UPDATE u
SET u.dbname = s.NAME
FROM #filetable u
INNER JOIN master..sysdatabases s ON u.dbid = s.dbid

UPDATE #filetable
SET mdf = replace(mdf, 'C:', 'D:')
    ,ldf = replace(ldf, 'C:', 'D:')
FROM #filetable

SELECT @dbid = min(dbid)
FROM #filetable
WHERE dbid > 4

WHILE @dbid IS NOT NULL
BEGIN
    SELECT @SQLText = 'alter database [' + dbname + '] MODIFY FILE (Name = ' + logicalname + ' , FileName = N''' + ldf + ''');'
    FROM #filetable
    WHERE dbid = convert(VARCHAR, @dbid)
        AND fileid = 0 -- Log file

    PRINT @SQLText

    --Exec(@SQLText)
    SELECT @SQLText2 = 'alter database [' + dbname + '] MODIFY FILE (Name = ' + logicalname + ' , FileName = N''' + mdf + ''');'
    FROM #filetable
    WHERE dbid = convert(VARCHAR, @dbid)
        AND fileid = 1 -- data file

    PRINT @SQLText2

    --Exec(@SQLText)
    SELECT @dbid = min(dbid)
    FROM #filetable
    WHERE dbid > 4
        AND dbid > @dbid
END

--- using Old Detach / Attach method (not preferred, but still people use it.. unfortunately I used it recently on a NON prod server).

DECLARE @datafile VARCHAR(255)
    ,@logfile VARCHAR(255)
    ,@dbid TINYINT
    ,@SQLText VARCHAR(8000)
    ,@dbname VARCHAR(255)
    ,@SQLText2 VARCHAR(8000)

--2. Detach All Local Databases and prepare for Attach
IF EXISTS (
        SELECT 1
        FROM tempdb..sysobjects
        WHERE NAME LIKE '%#filetable%'
        )
BEGIN
    DROP TABLE #filetable
END

CREATE TABLE #filetable (
    mdf VARCHAR(255)
    ,ldf VARCHAR(255)
    ,dbid TINYINT
    ,dbname VARCHAR(100)
    ,fileid TINYINT
    )

--
INSERT #filetable (
    mdf
    ,dbid
    ,fileid
    )
SELECT physical_name
    ,database_id
    ,data_space_id
FROM sys.master_files
WHERE data_space_id = 1

INSERT #filetable (
    ldf
    ,dbid
    ,fileid
    )
SELECT physical_name
    ,database_id
    ,data_space_id
FROM sys.master_files
WHERE data_space_id = 0

UPDATE u
SET u.dbname = s.NAME
FROM #filetable u
INNER JOIN master..sysdatabases s ON u.dbid = s.dbid

UPDATE #filetable
SET mdf = replace(mdf, 'C:', 'D:')
    ,ldf = replace(ldf, 'C:', 'D:')
FROM #filetable

SELECT @dbid = min(dbid)
FROM #filetable
WHERE dbid > 4

WHILE @dbid IS NOT NULL
BEGIN
    SELECT @SQLText = 'alter database [' + dbname + ']'
    FROM #filetable
    WHERE dbid = convert(VARCHAR, @dbid)

    SELECT @SQLText = @SQLText + CHAR(10) + ' set single_user with rollback immediate;'

    SELECT @SQLText = @SQLText + CHAR(10) + ' exec master..sp_detach_db ' + dbname
    FROM #filetable
    WHERE dbid = convert(VARCHAR, @dbid)

    PRINT @SQLText

    --Exec(@SQLText)
    SELECT @SQLText2 = 'exec master..sp_attach_db ''' + dbname + ''''
    FROM #filetable
    WHERE dbid = @dbid

    SELECT @SQLText2 = @SQLText2 + ',''' + mdf + ''''
    FROM #filetable
    WHERE dbid = @dbid
        AND mdf IS NOT NULL

    SELECT @SQLText2 = @SQLText2 + ',''' + ldf + ''''
    FROM #filetable
    WHERE dbid = @dbid
        AND ldf IS NOT NULL

    PRINT @SQLText2

    --Exec(@SQLText)
    SELECT @dbid = min(dbid)
    FROM #filetable
    WHERE dbid > 4
        AND dbid > @dbid
END

DROP TABLE #filetable
share|improve this answer
add comment

The only way that I know of to do multiple DBs at once would be to script the move for multiple DBs at once.

ALTER DATABASE database_nameA SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE database_nameB SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE database_nameC SET OFFLINE WITH ROLLBACK IMMEDIATE;
-------

Here you could either move the files manually, or write a script to do it. Possibly using xp_cmdshell or some tool. Probably easier to just move the files by hand though. Mark a bunch of them, then drag and drop.

-------
ALTER DATABASE database_nameA MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
ALTER DATABASE database_nameB MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
ALTER DATABASE database_nameC MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

ALTER DATABASE database_nameA SET ONLINE;
ALTER DATABASE database_nameB SET ONLINE;
ALTER DATABASE database_nameC SET ONLINE;

Of course if you move the data file and the log file you have to make sure you do the MODIFY FILE part for each part.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.