Friday, March 7, 2014

Rename your SQL Server database files

Every so often, you will need to rename your database files.  Today, for example, I attempted to create a new database.  Very simple, have done it a million times before... but it failed!

  .Net SqlClient Data Provider: Msg 5170, Level 16, State 1, Line 1
  Cannot create file 'C:\MSSQL\DATA\DBA_Primary.mdf' because it already exists. Change 
  the file path or the file name, and retry the operation.
  .Net SqlClient Data Provider: Msg 1802, Level 16, State 4, Line 1
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It didn't fail because the DBA database already existed.  It failed because the .mdf filename in my statement was already there.  My bad.  I forgot that I had renamed the DBA database a while back.  Running sp_renamedb only renames the database, NOT the underlying files.  This is the code that I used to rename the files after I received the error above:

  /* Run this to return the filenames from the database in question. */ 
  USE DBName
  EXEC sp_helpfile

  /* Rename logical database filenames. */
  ALTER DATABASE DBName MODIFY FILE (NAME=N'DBA_Primary', NEWNAME=N'NewFileName_Primary')
  GO
  ALTER DATABASE DBName MODIFY FILE (NAME=N'DBA_Log', NEWNAME=N'NewFileName_Log')
  GO

  /* Rename physical database filenames -- this requires multiple steps:
      1. Put database into single-user mode
      2. Detach database
      3. Rename files
      4. Attach database
      5. Put into multi-user mode      */
 

  -- 1. Single user mode
 
  ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  GO
  -- 2. Detach
  USE master
  GO
  EXEC master.dbo.sp_detach_db @dbname = N'DBName'
  GO

  -- 3. Rename the physical files
  -- Do this in Windows Explorer. Right click file, Rename

 
  -- 4. Attach database
  USE [master]
  GO
  CREATE DATABASE DBName ON
  ( FILENAME = N'C:\MSSQL\DATA\DBName_Primary.mdf' ),
  ( FILENAME = N'C:\MSSQL\DATA\DBName_Data.ndf'),
  ( FILENAME = N'C:\MSSQL\LOG\DBName_Log.ldf' )
  FOR ATTACH
  GO

  -- 5. Put back in multi user mode 
  ALTER DATABASE DBName SET MULTI_USER
  GO


All done! 

Friendly reminder, don't do this during the day, while you have users on the system.  ;-)