How To Rename Database
This is instruction on how to restore proper name for database.
Please open "Microsoft SQL Query Analyser":
[Start] -> Programs -> Microsoft SQL Server -> Query Analyser
Connect to SQL server with administrative rights.
Use this script to do renaming:
-- You must be in
the [master] database to run DBCC DBCONTROL.
USE
master
-- Put
database into single user mode.
ALTER
DATABASE OldDatabaseName
SET SINGLE_USER WITH ROLLBACK AFTER
20
-- Bring
the database down.
EXEC
sp_dboption
'OldDatabaseName',
'offline',
'TRUE'
-- Detach
database.
EXEC
sp_detach_db
'OldDatabaseName'
GO
-- Rename files.
EXEC
xp_cmdshell
'rename
"D:\MSSQL\Data\OldDatabaseName.MDF"
NewDatabaseName.MDF'
EXEC
xp_cmdshell
'rename
"D:\MSSQL\Data\OldDatabaseName_Log.LDF" NewDatabaseName_Log.LDF'
GO
-- Declare files.
DECLARE
@fileData
sysname
DECLARE @fileLogs
sysname
SET @fileData
=
'D:\MSSQL\Data\NewDatabaseName.MDF'
SET @fileLogs
=
'D:\MSSQL\Data\NewDatabaseName_Log.LDF'
-- You must
be in the [master] database to run DBCC DBCONTROL.
USE master
-- Attach
database.
EXEC
sp_attach_db
'NewDatabaseName',
@fileData,
@fileLogs
-- Put
database into single user mode.
ALTER
DATABASE NewDatabaseName
SET SINGLE_USER WITH ROLLBACK AFTER
20
-- Bring
the database up.
EXEC
sp_dboption
'NewDatabaseName',
'offline',
'FALSE'
-- Change
name of files.
ALTER
DATABASE NewDatabaseName
MODIFY FILE
(Name
= 'OldDatabaseName', NewName =
'NewDatabaseName')
ALTER DATABASE NewDatabaseName
MODIFY FILE
(Name
= 'OldDatabaseName_Log', NewName =
'NewDatabaseName_Log')
-- Put
database into multi user mode.
ALTER
DATABASE NewDatabaseName
SET MULTI_USER
If you have .NET sites which are using database then check connection strings inside "/Web.config" file and change names accordingly.
If you have links to database from "System DSN" then change them thru "Microsoft ODBC Data Source Administrator".
If you have some scheduled Database Backup scripts then update them with new names.
If you have some scheduled Files Backup which includes *.LDF and *.MDF file then check them too.