How To Rename Database


This is instruction on how to restore proper name for database.

  1. Please open "Microsoft SQL Query Analyser":
    [Start] -> Programs -> Microsoft SQL Server -> Query Analyser

  2. Connect to SQL server with administrative rights.

  3. 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
     

  4. If you have .NET sites which are using database then check connection strings inside "/Web.config" file and change names accordingly.

  5. If you have links to database from "System DSN" then change them thru "Microsoft ODBC Data Source Administrator".

  6. If you have some scheduled Database Backup scripts then update them with new names.

  7. If you have some scheduled Files Backup which includes *.LDF and *.MDF file then check them too.