Post reply

Moving 2008 R2 db to 2012

  • June 7, 2013 at 9:49 am

    #274891

    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

    #1622327

    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[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • June 10, 2013 at 1:42 am

    #1622331

    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[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • June 10, 2013 at 2:49 am

    #1622337

    Advisable to take a full backup of the source beforehand anyway 😀

  • June 10, 2013 at 3:07 am

    #1622344

    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

    #1623213

    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