In response to some questions about renaming a physical file for a database, I have 2 versions of Powershell scripts that do this for you, including taking the database offline and then online to make the physical change match the meta-data.
First, there is an article about this at http://msdn.microsoft.com/en-us/library/ms345483.aspx. This explains that you start by setting the database offline, then alter the database and modify the filename then set it back online. This particular article does not show the physical renaming of the file, but it is necessary since the ALTER DATABASE MODIFY FILE statement only changes the metadata of the database in master.
There is a procedure to do this with SQL CLR and TSQL, but I chose to illustrate it in Powershell and SMO (using SQL Provider as well as straight SMO). The SQLCLR version is by a SQL Server MVP, Ted Krueger (Blog | Twitter) and can be found at this link.
My version will be based on Powershell and SMO with a smattering of SQL Provider provided in SQL 2008+.
If you notice, I first add the Snapin that is the SQL Provider. If you already have it loaded in your Powershell Profile, then you can just omit those lines. If you do not have SQL 2008 objects installed on this machine, then you will notice that the Snapins don’t load either. In that case you would just use the version below.
In the SQL Provider version you will see a Powershellism with Test-Path and you are using a path, it just is a SQL provider path that points to the Database and makes sure that it exists. Compare it to the other version where you are looking at the Server.Databases[$dbname] and then you get the filegroups and then the files.
This version of the script is purely SMO and PowerShell. First you load the objects from SQL Server 2005/2008 SMO.
This version will use the Server object to get the database that you are looking for so that you can get at the files. The file you are looking for is the $logicalName so that you can get the PhysicalName of the file. Then using the builtin CmdLets to handle Files operations, a Rename-Item is issued to rename the initial path to the new one.
- ALTER DATABASE dbname SET OFFLINE
- ALTER DATABASE dbname MODIFY FILE
- Rename-Item from old to new
- ALTER DATABASE dbname SET ONLINE
I did not put these in a function, but they could easily (or not too hard that is) put together using Parameters and make it versatile for other situations, but that is for you to do so that it works for your scenario.