SQL Server Rename Database and Filenames

Rename a SQL Server Database and the Filenames to Match

Many times I have a need to rename a SharePoint content database or a Team Foundation Server Team Project Collection database name in SQL Server.  The reasons vary, but usually it is because originally the naming of the database is horrible or the purpose of the data has changed and I want all of the references to it to be updated in all systems within the farm.

SQL Commands:

Detach Database using sp_detach_db

--Note: Change MyDatabase to the database name you want to detach.

USE [master]
ALTER DATABASE [MyDatabase]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 
EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

Attach Database

--Note: Change SqlAndMe to the database name you want to detach.

USE [master]
CREATE DATABASE [SqlAndMe] ON 
( FILENAME = N'C:\…\SqlAndMe.mdf'),
( FILENAME = N'C:\…\SqlAndMe_log.LDF')
FOR ATTACH

Rename Logical Filenames

--Note: Change SqlAndMe and MyDatabase to the names you want to change from and to.  (The Logical filenames are not the same as the physical file names.)

USE [SqlAndMe]
ALTER DATABASE [SqlAndMe]
      MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')
ALTER DATABASE [SqlAndMe]
      MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')

Comments

Popular posts from this blog

SharePoint Designer 2013 Approval Workflow with Comments

Change SharePoint server hostname and Web Application Names

The Timer Service Failed to Recycle