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)

Advertisement