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)