In SQL Server 2000, you can easily rename your database running sp_renamedb. The syntax from BOL as follows:
sp_renamedb [ @dbname = ] ‘old_name’ , [ @newname = ] ‘new_name’
In SQL Server 2005, however, this has changed. You will need to use ALTER DATABASE MODIFY NAME instead.
ALTER DATABASE database_name
MODIFY NAME = new_database_name
Although the database name has changed, you probably realise that the logical and physical database filename are still the same! What it means is if you create another database with the old name at the same server with the same filename, you will get an error.
My favourite way to deal with it is to detach and attach the database.
– execute the sp to detach a database
exec sp_detach_db ‘TestDB’,'true’– rename the physical file to whatever you want
– execute attach database and specify filenames.
EXEC sp_attach_db @dbname = ‘TestDB’,
@filename1 = ‘C:\SQLData\TestDB.MDF’,
@filename2 = ‘C:\SQLLog\TestDB_Log.LDF’
I realise that some people have a better way of doing this. If so, you’re welcome to share your thought!
