Muhammad Bilal on Wed, 26 Dec 2012 19:48:51
I have SQL Server 2008 SP3 (32 Bit) database which I want to upgrade to SQL Server 2012 SP1 (64 Bit) edition.
I want to know what will be the best procedure to upgrade i.e. restore database, transfer data or de-attach and then attach the database and what are the pre and post steps for upgradation for optimal performance?
Alberto Morillo on Wed, 26 Dec 2012 20:50:23
You cannot use SQL Server setup for this upgrade, since SQL Server setup does not support cross platform upgrades (x86 to x64).
I would recommend you detach/attach and backup/restore your databases. One of the issues you may face are orphan users. Database users that does not have associated login on the new server.
Remember that jobs, integrations services packages and alerts may be located on the MSDB system database.
A good practice is to defragment indexes and updates statistics after migration.
Hope this helps.
Kumar muppa on Thu, 27 Dec 2012 02:17:01
Nothing really new in migrating point of view from SQL2008 R2 to SQL 2012.
1) Run DBCC check db on sql2008 r2.
2) Back up the database or save the .mdf and .ldf files
3) Get your SQL 2012 server ready
4) Restore the database from back up or copy the .mdf & .ldf to the required locations and attach tot eh database.
5) Find orphan users and fix them by updating the windows SID with SQL SID.
6) Rebuild indexes
7) When you rebuild indexes statistics outside the index columns may not updated so update all statistics
8) Get your jobs, packages, maintenance plans, linked server information, alerts.. Simply all the required features from the old server bring to the new server. Simply copy or script and run on new server....your choice.
9) Finally change the compatibility level to 110
10) Run dbcc checkdb
Your SQL2012 SP1 database is ready...
Fanny Liu on Thu, 27 Dec 2012 08:30:24
Both attach and resort can upgrade SQL Server 2008 R2 database to SQL Server 2012 automatically. If the Database is big, detach/attach option will cause long down time while backup restore will have less downtime.
Please refer to the following thread for detail description:
For more comparison, please see the books online:
Upgrade a Database Using Detach and Attach (Transact-SQL)
Back Up and Restore of SQL Server Databases