Background
Upgrading is easy when we talk about one level upgrade. For example, upgrading from 2012 R2 to 2014 will be seemless. However if you upgrade from SQL Server 2000 to SQL Server 2014, you will need some tips to make the database run smoothly. This article will guide you to do step-by-step approaches.
- Patching your SQL Server 2000 to the latest service pack (i.e. SP4(
- Backup your SQL Server 2000 Database from your original machine
- Install SQL Server 2008 R2 into other machine
- Restore your backup from SQL Server 2000 to the SQL Server 2008 R2
- Backup your SQL Server 2008 R2 database into a .bak file. You can set the compatibility backup to SQL Server 2008.
- Create a new database on SQL Server 2014 and restore to the that data. Please do overwrite and cut the tail log to make the database clean.
Advanced Scenario
The above step is sufficient if you upgrade a simple database. However, If you have complex schema rather than dbo schema. For example, a schema based on username access. you should also script the other object. In order to do that, follow this step
- Open your SQL Server Management Studio
- Right click and generate script
- Click next to continue the step. On this step, Select the database roles and schema. Confirm it with next
- On the next step, select save to file and click advanced set true for script login, script object ;evel permission, and script owner.
- Next to generate script and restore the script on the upgraded database.
That’s it. If you have mpre question, please feel free to drop the comment below.