Normal database rename does not rename the files.

This script does. If database name is part of the file names (it usually is).

It is also my first powershell script, so ignore bad practice might be used, it is "Hello World!!!".

Be careful, remember to take backup first, etc.

PowerShell
Edit|Remove
#Script name: RenameDatabase.ps1 
#Created on: Sunday, September 27, 2009
#Author: Gorm Braarvig
#Purpose: Rename SQL Server database and the files used by the database (if file names contains database names)

param (
  [string] $servername
  ,[string] $sourceDb
  ,[string] $destDb
)

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
 
$SqlConnection.ConnectionString = "Server=$servername;Database=master;Integrated Security=True"
 
$SqlCmd.CommandText = "
BEGIN TRY 
    EXEC sp_executesql 'sp_configure ''xp_cmdshell'',1; RECONFIGURE'
END TRY BEGIN CATCH END CATCH

BEGIN TRY 
    EXEC sp_executesql N'DROP PROCEDURE gbrRenameFile' 
END TRY BEGIN CATCH END CATCH
BEGIN TRY 
    EXEC sp_executesql N'DROP PROCEDURE gbrRenameDatabase' 
END TRY BEGIN CATCH END CATCH
    
BEGIN TRY 
    EXEC ('CREATE PROCEDURE gbrRenameFile @sName NVARCHAR(4000), @sNewName NVARCHAR(4000) AS 
	    SET NOCOUNT ON
	    SET @sNewName = Reverse(Left(Reverse(@sNewName), Charindex(''\'', Reverse(@sNewName))-1))
	    DECLARE @cmd NVARCHAR(4000)=REPLACE (''REN ""''+ @sName + ''"" ""'' + @sNewName + ''""'', ''\\'', ''\'')
	    EXEC xp_cmdshell @cmd, no_output')
END TRY BEGIN CATCH END CATCH

BEGIN TRY 
    EXEC ('CREATE PROCEDURE gbrRenameDatabase @sName SYSNAME, @sNewName SYSNAME AS
	    SET NOCOUNT ON
	    IF EXISTS (SELECT name FROM sys.databases WHERE name=@sNewName)
		    SELECT ''Destination database exists'' AS Failed
	    ELSE IF EXISTS (SELECT name FROM sys.databases WHERE name=@sName)
	    BEGIN
	        BEGIN TRY
		        EXECUTE (''ALTER DATABASE '' + @sName + '' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'')
		        EXECUTE (''select * INTO ##Files from '' + @sName + ''.sys.sysfiles'')
		        EXECUTE (''ALTER DATABASE '' + @sName + '' SET OFFLINE'')
		        EXECUTE (''DROP DATABASE '' + @sName)
		        DECLARE Files CURSOR FAST_FORWARD FOR SELECT FileName FROM ##Files
		        DECLARE @FileName NVARCHAR(4000)
		        DECLARE @NewFileName NVARCHAR(4000)
		        DECLARE @attachCommand NVARCHAR(MAX)
		        SET @attachCommand = ''sp_attach_db '''''' + @sNewName + ''''''''
		        OPEN Files
		        FETCH NEXT FROM Files INTO @FileName
		        WHILE @@fetch_status <> -1
		        BEGIN
			        SET @NewFileName = REPLACE(@FileName, @sName, @sNewName)
			        EXEC gbrRenameFile @FileName, @NewFileName
			        SET @attachCommand = @attachCommand + '','''''' + @NewFileName + ''''''''
			        FETCH NEXT FROM Files INTO @FileName
		        END
		        CLOSE Files
		        DEALLOCATE Files
		        DROP TABLE ##Files
		        EXEC sp_executesql @attachCommand
	            SELECT @sName + ''->'' + @sNewName AS Success
	        END TRY		       
	        BEGIN CATCH
	            SELECT @sName + ''->'' + @sNewName AS Failed
	        END CATCH
	    END
	    ELSE
		    SELECT ''Source database does not exist'' AS Failed
    ')
END TRY BEGIN CATCH END CATCH

    EXEC gbrRenameDatabase '$sourceDb', '$destDb'
"

$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$dbs =$DataSet.Tables[0]
$dbs 
$SqlConnection.Close()