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
GO

-- creating sample datbase
CREATE DATABASE DB_SQL2008R2
GO

USE DB_SQL2008R2
GO

-- creating test table with values
CREATE TABLE Test_table (col1 VARCHAR(10), col2 VARCHAR(10));
INSERT INTO dbo.Test_table ( col1, col2 )
VALUES
('A1','B1'),
('A2','B2'),
('A3','B3'),
('A4','B4'),
('A5','B5');
GO 

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;
GO

EXEC sp_detach_db @dbname = N'SQLTraining';
GO

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;
GO

CREATE DATABASE DB_SQL2008R2
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')
FOR ATTACH;
GO

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)

About these ads

About sqlpathy

SQL Server enthusiast.

Posted on June 10, 2013, in Database Upgrade, SQL Server 2012. Bookmark the permalink. 9 Comments.

  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.

  4. There’s definately a lot to find out about this issue.
    I like all the points you have made.

  5. I like it when folks come together and share views.
    Great website, continue the good work!

  6. That is very attention-grabbing, You are a very skilled blogger.
    I have joined your feed and sit up for in search of extra
    of your great post. Also, I’ve shared your website in my social networks

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Code Wala

Designing and coding

Paul Hernandez playing with BI

Business Intelligence, Data Analysis, Web Development and more ...

SQL Studies

My SQL Studies

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: