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:
Updates
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.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  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.

    Regards

    Nigel

    ReplyDelete
    Replies
    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.

      Delete