Upgrade to SQL 2012 using detach and attach commands

Today we shall try to see how to upgrade database from SQL Server 2008 R2 to SQL Server 2012 using Detach and Attach T-SQL commands.

Let’s first create a sample database say “DB_SQL2008R2” using SQL 2008 and create a test table (with some values)

USE master

-- creating sample datbase


-- creating test table with values
CREATE TABLE Test_table (col1 VARCHAR(10), col2 VARCHAR(10));
INSERT INTO dbo.Test_table ( col1, col2 )

Take note of the data file and log file created and the location (this is required as we are going to attach these files to SQL Server 2012 later). In my test server, they are created at:
Data File >> C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2.mdf
Log File >> C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2_log.ldf

How do I get the data and log file name?

Go to “Object Explorer” window in Sql Server management studio. Expand the database folder and look for “DB_SQL2008R2”. Right-click and choose “Properties”. Database properties window will open. Select the “Files” page on the left. On the right, you will find two files DB_SQL2008R2 and DB_SQL2008R2_log. The location of these files is mentioned under the “Path” column header.

So, we are all set for upgrade!

First, step is to detach the database. Open SSMS for SQL Server 2008R2 and execute the following code snippet to detach the database

USE master;

EXEC sp_detach_db @dbname = N'SQLTraining';

Now login to SSMS for SQL Server 2012 and create the database using the data and log files we have taken not of earlier.

USE master;

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2.mdf'),
   (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL\MSSQL\DATA\DB_SQL2008R2_log.ldf')

So finally, we have been successful in upgrading a database from SQL Server 2008R2 to SQL Server 2012

For further reading please refer MSDN: Upgrade a Database Using Detach and Attach (Transact-SQL)

9 thoughts on “Upgrade to SQL 2012 using detach and attach commands

  1. Hey There. I found your blog the usage of msn. That
    is an extremely neatly written article. I’ll be sure to bookmark it and return to read more of your useful info.

    Thanks for the post. I will certainly comeback.


  2. Whats up this is kind of of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML.

    I’m starting a blog soon but have no coding experience so
    I wanted to get advice from someone with experience.
    Any help would be enormously appreciated!


  3. We’re a gaggle of volunteers and opening a new scheme in our community.
    Your web site provided us with useful info to work
    on. You have performed a formidable activity and our whole
    group will be thankful to you.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s