June 7, 2013 at 9:49 am
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?
June 10, 2013 at 1:22 am
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
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
June 10, 2013 at 1:42 am
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
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
June 10, 2013 at 2:49 am
Advisable to take a full backup of the source beforehand anyway
June 10, 2013 at 3:07 am
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!
June 12, 2013 at 12:04 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy