Rename a SQL Server database

Restoring databases frequently I realized I need a fast was to rename the databases on the fly. Here we have a rather fast (but manual - for the moment) way of doing this:



 USE YourDB
 EXEC sp_helpfile

 ALTER DATABASE YourDB MODIFY FILE (NAME=N'YourDBName', NEWNAME=N'NewName')
 GO

 ALTER DATABASE YourDB MODIFY FILE (NAME=N'YourDBName_log', NEWNAME=N'NewName_log')
 GO

 ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 GO

 USE master
 GO

 EXEC master.dbo.sp_detach_db @dbname = N'YourDB'
 GO

 ** Manual step: RENAME the files using your file browser

 USE [master]
 GO

 CREATE DATABASE YourDB ON
 ( FILENAME = N'C:\filePath.mdf' ),
 ( FILENAME = N'C:\filePath_log.ldf' )
 FOR ATTACH
 GO

 ALTER DATABASE YourDB SET MULTI_USER
 GO

No comments:

Post a Comment