SQL Server 2012: Rename a database with datafiles

SQL Server 2012: Rename a database with data files (MDF, LDF)

This post provides the scripts that will rename a database along with its data files.

Audience

DB Admins, T-SQL developers, Technical Consultants, AX Developers

Background

In so many scenarios, you may need to rename a database. One general scenario in AX implementation would be when maintaining multiple AX environments at one server like Staging, TEST, DEV and Production. This is also very likely in development and RnD scenarios where you keep restoring LIVE instances backups on a central backstage HUB for data availability.

Problem

For hosting multiple and different AX servers on a single machine for local availability, you need to have multiple AX server databases hosted with different names. But for that, you need to rename the database since each backup may result in the exact same database name. But you face the problem as you already have a database with exactly same name, 'MicrosoftDynamicsAX' attached to your local server. This can cause problems and you may start thinking how to restore both databases with different names cleanly without disturbing the existing database.

Solution(s)

Properly renaming existing database(s) would let you restore any database coming under the name of MicrosoftDynamicsAX. How I have resolved this issue at my local server is, I rename the existing MicrosoftDynamicsAX database (along with data file names, yes, renaming data files :) ) and the restoring the new database under the same MicrosoftDynamicsAX (or whatever) name. To restore more such instances, I can always rename databases as I like. This I achieve using the attached script. It lets me achieve the following in one go

Script process

• Set desired database to single user mode
• Rename database to new provided name
• Takes the database (with new name) to offline mode
• Suspends further execution for 5 minutes 
• This is done so that the user is expected to rename the files manually at physical file level.
• Renames the data files logically
• Brings the database back to online
• Sets the database again to multi user mode.

The script is at the moment hard coded since I was not able to provide variables to a few alter database commands due to command's nature. For this reason, the script is not plug and playable as I had to use symbols to specify the replacing database names. when using, carefully replace sample values with yours and test and master execution on test databases :)

This mess would be resolved by dynamically generating SQL and providing user provided database names and other variables to SQL. Therefore as improvement, I am writing a small utility that would enable user to select a database, provide its new name and click and all is done automatically. So stay tuned.

Script can be downloaded from the link: Database renaming script