Rename the database in SQL 2012 R2

It happens sometimes that we need to rename a database. It is good to know that what happens behind the scenes is different from what you may be expecting. SQL Server renames the presenting name of the DB only. The problem happens when you want to create a DB with the old name. For instance, imagine you have a DB called MyDB. You rename it to MyDB_OLD. Now you try to create a new DB called MyDB. This operation fails because the underlying files of the MyDB_OLD are yet MyDB.mdf and MyDB_log.ldf. If you take the DB offline and physically rename the files, when you try to bring it back online, it fails because the new file name does not match the one saved inside the file!

And here is the question again: how can we rename a DB and all its physical files? In order to do that, you need to follow these steps:

    1. Open Microsoft SQL Server Management Studio.
    2. Connect to the server wherein the DB you want to rename is located.
    3. Modify the following script and run it –
-- Replace all MyDBs with the name of the DB you want to change its name
USE [MyDB];
-- Changing Physical names and paths
-- Replace all NewMyDB with the new name you want to set for the DB
-- Replace 'C:\...\NewMyDB.mdf' with full path of new DB file to be used
ALTER DATABASE MyDB MODIFY FILE (NAME = ' MyDB ', FILENAME = 'C:\...\NewMyDB.mdf');
-- Replace 'C:\...\NewMyDB_log.ldf' with full path of new DB log file to be used
ALTER DATABASE MyDB MODIFY FILE (NAME = ' MyDB _log', FILENAME = 'C:\...\NewMyDB_log.ldf');
-- Changing logical names
ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB, NEWNAME = NewMyDB);
ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB _log, NEWNAME = NewMyDB_log);
  1. Right click on the DB and select Tasks>Take Offline
  2. Go to the location that MDF and LDF files are located and rename them exactly as you specified in first two alter commands. If you changed the folder path, then you need to move them there.
  3. Go back to Microsoft SQL Server Management Studio and right click on the DB and select Tasks>Bring Online.
  4. Now is the time to rename you DB to the new name.

 

 

Another method using SQL Statements

 

You might have seen “The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)” error when you try to rename SQL server database.  This error normally occurs when your database is in Multi User mode where users are accessing your database or some objects are referring to your database.

Nothing much to do to resolve the issue. First set the database to single user mode and then try to rename the database and then set it back to Multi user mode.

We will go through step by step.

First we will see how to set the database to single user mode,

ALTER DATABASE dbName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Now we will try to rename the database

ALTER DATABASE dbName MODIFY NAME = dbNewName

Finally we will set the database to Multiuser mode

ALTER DATABASE dbName
SET MULTI_USER WITH ROLLBACK IMMEDIATE
Hope you are able to rename your database without any issues now!!!

 

Advertisements

How to install .NET Framework 3.5 on Windows Server 2012 and Windows Server 2012 R2

How to install .NET Framework 3.5 on Windows Server 2012 and Windows Server 2012 R2

If you have an application that you want to run on Windows Server 2012 that requires the .NET Framework 3.5, you will most likely run in to a problem when trying to install it. If you are trying to install .NET Framework 3.5 from the Server Manager GUI, you will see this when installing the feature:

“Do you want to specify an alternate source path? One or more installation selections are missing source files…”

Bug when adding .net framework 3.5 in Server 2012

To solve this, you can either:

1. Go to a command prompt and enter this:

dism /online /enable-feature /featurename:NetFX3 /all /Source:d:\sources\sxs /LimitAccess

Note: Source should be the Windows installation disc. In my case, this was located on D:

Bug when adding .net framework 3.5 in Server 2012

2. Go down to “Specify an alternate source path” and enter “d:\sources\sxs” as the path.

Specify alternate source path windows server 2012 .net framework 3.5

Specify alternate source path windows server 2012 .net framework 3.5

Now you should see this under your Features list: