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. ;-)
.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. ;-)