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

Transact SQL - nth day of Month (DateName)

One of my projects wants me to identify the given date's day name (DateName) ad find its occurrence in the given month. Say today is Tuesday, 20th May 2014, and this is the 3rd Tuesday of this month.

Once it is identified that the given date [20 may 2014] is 3rd Tuesday of the month, go back to last month's 3rd Tuesday, print sales data, keep going back for last 4 months and print sales data, interesting :) yet tiring too

So we are going to know if today, or any given date's day (DateName) is 2nd Monday of the Month, or 4th Tuesday  ? or 3rd Sunday ? or nth day of Month

Just now I wrote some code to find the occurrence of day in current month. The code is not refactored for any good :D (Sorry its mid mid night and my brain cells are too short i guess). Any ways it does the trick

Declare @du_date datetime, @du_date_FirstDate datetime
Declare @du_date_DateName nvarchar(10), @thDayOccStr nvarchar(4)
Declare @thDayOcc int

Set @du_date = getDate()

Select @du_date_DateName = DATENAME(dw, @du_date)

Set @thDayOcc = 0
Set @du_date_FirstDate = dateadd(day,1-datepart(day,@du_date),@du_date)

While (@du_date_FirstDate <= @du_date)
Begin
--** Calculate which th day it is of this month, next we will find same for last month(s) **--
if (DATENAME(dw, @du_date_FirstDate) = @du_date_DateName)
Begin
Set @thDayOcc += 1
--** FOR LOGGIN , U MAY ENABLE FOLLOWING **--
--Select 'Adding', DATENAME(dw, @du_date_FirstDate_Copy), @du_date_DateName, @thDayOcc
End

Select @du_date_FirstDate = DateAdd(dd,1,@du_date_FirstDate)
End
Set @thDayOccStr = Convert(nvarchar(100), @thDayOcc)

Select [Date processed] = @DU_Date, @du_date_DateName, [DateName occurence in Month] = @thDayOcc,
 @thDayOccStr +
Case
When @thDayOcc = 1 Then 'st '
When @thDayOcc = 2 Then 'nd '
When @thDayOcc = 3 Then 'rd '
Else 'th '
End
+ @du_date_DateName + ' of the Month'