Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving 2008 R2 db to 2012 Expand / Collapse
Author
Message
Posted Friday, June 7, 2013 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:01 PM
Points: 46, Visits: 154
I've got a database running on SQL Server 2008 R2 Standard. I'm building a SQL server 2012 Standard server to host a new version of that db. I want to move a copy of the database to the new server and run it under 2012 for testing for a couple of weeks, then move a fresh copy of the data to the new server when we're ready to go into production.

What's involved in detaching a database from a 2008 R2 instance and attaching it to a 2012 instance? I found some references to updating the compatibility level (to 110) and updating statistics. What else needs to be done?
Post #1461142
Posted Monday, June 10, 2013 1:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
Take a backup of the database on 2008R2, example shown below:

USE Master
GO
BACKUP DATABASE [SQLTraining]
TO DISK = N'D:\SQLTraining_FullBackup.bak' WITH NOFORMAT, NOINIT,
NAME = N'SQLTraining-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Restore the same on 2012 box

USE master
GO
RESTORE DATABASE [SQLTraining2012] FILE = N'SQLTraining'
FROM DISK = N'D:\SQLTraining_FullBackup.bak'
WITH FILE = 1,
MOVE N'SQLTraining'
TO N'D:\SQLTraining2012.mdf',
MOVE N'SQLTraining_log' TO N'D:\SQLTraining2012.LDF',
NOUNLOAD, STATS = 10
GO

This works like charm


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1461413
Posted Monday, June 10, 2013 1:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
I re-read your post and found that you are actually looking for detaching and attaching the DB's. Here is the useful link http://msdn.microsoft.com/en-us/library/ms189625.aspx

Hope this helps!


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1461417
Posted Monday, June 10, 2013 2:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 12:53 AM
Points: 18, Visits: 235
Advisable to take a full backup of the source beforehand anyway
Post #1461428
Posted Monday, June 10, 2013 3:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:01 PM
Points: 46, Visits: 154
I'm happy to do it via the backup and restore method, and that seems to be the approach I see discusse most often. Thanks!
Post #1461437
Posted Wednesday, June 12, 2013 12:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 12:53 AM
Points: 18, Visits: 235
rray 44280 (6/10/2013)
I'm happy to do it via the backup and restore method, and that seems to be the approach I see discusse most often. Thanks!


Well, detaching and attaching is a good option if you don't need to move the data and log files around the network. It's all a question of time. If it's quick enough to do a backup and restore then by all means take that approach.

Just make sure your backups are in order if you try doing the detach/attach. Not being a doomsayer, just advising caution wherever possible.
Post #1462467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse