Wednesday, December 30, 2015

Moving User SQL Databases Using PowerShell

I grew tired of manually moving databases around using a combination of SQL and "Copy / Paste" so wrote out a bit of PowerShell to save me some time and effort.
Notice that I am using the copy-item then deleting the object, not just moving the item. This is because of how permissions on objects are handled with copy vs move, plus I am paranoid about not having my original database handy if the move fails, or the moved database gets corrupted in transit.

Let's take a look at the code

In the first section we will be setting the variables.
The next step is to get the database information:
Once we have the database information, the database will need to be taken OFFLINE: Once the database is offline, we can copy the file, set ACLs, and update the database with the new .mdf and .ldf file locations: Then we can bring the DB back ONLINE Once the DB is back ONLINE, wait for 10 seconds and delete the original database: Here is a look at the code once it is all put together:
01/01/2016: Fixed issues with ACL for moved files by converting file location to UTC based path format.
01/02/2016: Updated to include snippets and comments
01/05/2016: Major update to fix $destination to UTC path, added copyItem function, item extension switch, updated outputs with write-output and write-verbose.


  1. This comment has been removed by the author.

  2. Hi

    I get an error on line 94 copying from SQL 2012 to SQL2014 because the database is offline.

    Also $destination does not like spaces in the url.



    1. Nigel, this is to move databases to different drives within the same SQL Server. I would suggest putting the database in read-only mode, backup the database, move the backup file to the new SQL Server, restore the database, and finally take database out of read-only mode.

  3. Hi

    Here is the output from Powershell script :-

    [DBG]: PS C:\Users\administrator.2013DEV>> $query5 = "ALTER DATABASE [" + $dbName + "] SET ONLINE;"
    Write-Output "Bring $dbName ONLINE..."
    Invoke-Sqlcmd -Query $query5
    Bring SP2013_AdminContent ONLINE...

    [DBG]: PS C:\Users\administrator.2013DEV>> $query4 = "ALTER DATABASE [" + $dbName + "] MODIFY FILE ( NAME = " + $name + ", FILENAME = '" + $destination + "' );"
    Write-Verbose "Updating Database with new file location..." -Verbose
    Invoke-Sqlcmd -Query $query4
    VERBOSE: Updating Database with new file location...
    Invoke-Sqlcmd : The path specified by "\\SQLSERVER2012\C$\Program Files\Microsoft SQL
    Server2014\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\SP2013_AdminContent.mdf" is not in a valid directory.
    At line:3 char:3
    + Invoke-Sqlcmd -Query $query4
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand