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.
This comment has been removed by the author.
ReplyDeleteHi
ReplyDeleteI 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
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.
DeleteHi
ReplyDeleteHere 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
Regards
Nigel