Migrate SQL Server 2000 to SQL Server 2012

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Migrate SQL Server 2000 to SQL Server 2012

Author

Topic

cycheang
Starting Member

40 Posts

Posted - 2015-02-25 : 02:57:05

Hi,

I plan to upgrade my existing databases from SQL Server 2000 to SQL Server 2012.

From What I browse through the net, suggestion I got is Backup existing database in SQL Server 2000, restore in SQL Server 2008 R2, then backup again in 2008 R2, at last restore in 2012 again.

I tried above way is working but the login, user permission and user roles are all screw up. Login is created under the security option but it did not link to any table, view .....

Is that possible to script all my table, view, stored procedure, triggers, primary key, foreign key, indexes from SQL Server 2000 and run in SQL Server 2012. After this, use the data migration wizard to transfer just the data.

Really appreciate if you could give some advise on this.

Many Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 12:33:59

I use this to copy the logins from the source server and then paste into the destination server to create them. Run both SQL and Windows auth.

http://sqlmag.com/query-analyser/sql-server-login-transfer

Using that script will copy the sids, so you shouldn't need to unorphan them. However in case you've created some logins already, you can use this to unorphan them:


USE YourDbNameGoesHere
GO

DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR FOR
	SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''
	FROM sysusers
	WHERE issqluser = 1 AND name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')
		AND name NOT IN ('BOE', 'printerdash', 'LYNX', 'ROUser')

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC (@SQL)
	FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 12:34:47

Do NOT script out your objects and use the data migration wizard. You will end up with a big mess. Definitely use backup/restore and the above login stuff I provided. You should be all set after that except for jobs or anything else not stored within the database (msdb stuff typically).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cycheang
Starting Member

40 Posts

Posted - 2015-02-25 : 19:23:33

I'm not actually a DBA. Just a software developer. This will be my first time doing this. My initially plan is to script all the database structure without any data from SQL Server 2000 and run it in SQL Server 2012. So that, my new server (SQL Server 2012) will have all the structure (table, login, view, stored procedure, index, key, ...)
During the migration time, then only do the data transfer from 2000 straight to 2012.

I install SQL Server 2012 in Windows Server 2012. The only login I have is sa. The server is not in domain it is in WORKGROUP.

Actually I tried before the backup and restore method from 2000 -> 2008 R2 -> 2012 and I noticed that it really take time and
for just one databases. I have more than 10 databases (per database could be 400GB) and if I use this method and it cause a lot of down time in production floor and warehouse as well. Some of the view in one of the database is actually pointing to another database)

Yesterday I tried using the script I generated in 2000 and run in 2012. At the end, I found out some view which is depending on the other database is missing because the other database is not exist. All this time I only did for one database.
Still puzzle which way is the best approach for me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 19:38:12

quote:
Originally posted by cycheang

I'm not actually a DBA. Just a software developer. This will be my first time doing this. My initially plan is to script all the database structure without any data from SQL Server 2000 and run it in SQL Server 2012. So that, my new server (SQL Server 2012) will have all the structure (table, login, view, stored procedure, index, key, ...)
During the migration time, then only do the data transfer from 2000 straight to 2012.

DON'T. BACKUP/RESTORE is the correct approach for database migrations.

quote:

Actually I tried before the backup and restore method from 2000 -> 2008 R2 -> 2012 and I noticed that it really take time and
for just one databases. I have more than 10 databases (per database could be 400GB) and if I use this method and it cause a lot of down time in production floor and warehouse as well. Some of the view in one of the database is actually pointing to another database)

Still use BACKUP/RESTORE. You can prep the databases ahead of time if you specify the WITH NORECOVERY option on the restores and apply incremental backups. I did a large database migration a few months ago. I had the databases copied over in just a few seconds using backup/restore since I had them prepped. All I had to do was apply the final incremental backup.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cycheang
Starting Member

40 Posts

Posted - 2015-02-25 : 20:13:49

is that possible if using the detach and attach method ?
Which mean, I detach all database from SQL Server 2000 and re-attach it on SQL Server 2008 RS. Set the compatibility level to 100.
Detach again, and re-attached it back to SQL Server 2012. Set compatibility level to 110.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-26 : 12:24:50

Yes you can use the detach/attach method, but I prefer backup/restore since it requires less downtime since you can prep the databases ahead of time.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cycheang
Starting Member

40 Posts

Posted - 2015-03-02 : 20:06:06

All right, seem backup/restore method will be the most safest way to do the migration.
If I only have 1 day to do the migration.. Think the backup/restore method maybe wont work for me. Yes, I can prepare all the databases ahead of time.

For eg: I backup all databases from my SQL 2000 and restore it in SQL Server 2008 R2. Backup again in 2008 R2 and restore in SQL 2012.

During migration, I still have to do the individual databases from 2000 until 2012.

All this might take a lot of times. Maybe can you share more details of your migration or maybe I'm overlook something.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-02 : 20:12:45

Can you do the upgrade over 2 maintenance windows, each having only a few minutes or even seconds of downtime?

backup 2000 databases (full)
restore fulls with norecovery on 2008
backup 2000 databases (trn/incrementals) every 5 minutes (or whatever)
restore incrementals with norecovery every hour or so on 2008

Once the maintenance window starts, do a final incremental on the 2000 server databases and then restore them on the 2008 server. Then restore with recovery.

Perform the same thing again except go from 2008 to 2012. I would recommend a separate maintenance window for this so that you can again have very minimal downtime.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2015-03-09 : 14:13:49

quote:
Originally posted by cycheang

Hi,

I plan to upgrade my existing databases from SQL Server 2000 to SQL Server 2012.

From What I browse through the net, suggestion I got is Backup existing database in SQL Server 2000, restore in SQL Server 2008 R2, then backup again in 2008 R2, at last restore in 2012 again.

I tried above way is working but the login, user permission and user roles are all screw up. Login is created under the security option but it did not link to any table, view .....

Is that possible to script all my table, view, stored procedure, triggers, primary key, foreign key, indexes from SQL Server 2000 and run in SQL Server 2012. After this, use the data migration wizard to transfer just the data.

Really appreciate if you could give some advise on this.

Many Thanks.

Did you test your application already? If not, stop immediately as you're heading towards a disaster.

A lot of things changed from SQL2000 to SQL2005: new schemas, permissions, deprecated T-SQL, etc, etc. Now imagine from SQL2000 to SQL2012.

You need to test your application against a SQL2012 database. And once you're done testing, do it again. Seriously. I've been in that wagon before and it's not fun. In fact, you may have to redesign some of your queries and permissions if you really want to make this works.

cycheang
Starting Member

40 Posts

Posted - 2015-03-25 : 01:56:02

Thanks for your advise. I'm now plan to get the structure of the database ready in new server. Test application of course and finally copy use copy database wizard to move over the data from old server (SQL 2000) to new server (SQL 2012).

Feel free to advise.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-25 : 12:23:53

Using the copy database wizard is the worst possible thing to choose for a database upgrade.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cycheang
Starting Member

40 Posts

Posted - 2015-03-25 : 22:14:40

quote:
Originally posted by tkizer

Using the copy database wizard is the worst possible thing to choose for a database upgrade.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Any reason for this ?

cycheang
Starting Member

40 Posts

Posted - 2015-03-26 : 04:40:04

If I were use the backup restore method from 2000 -> 2008 -> 2012.
After I did the restore in SQL 2008, can I skip to perform DBCC check, DBCC UPDATEUSAGE ...
I will change the compatibility mode. Then do backup again in SQL 2008. After all databases is already finished restore then
I only do the all the DBCC check ?

Please advise.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-26 : 12:58:55

quote:
Originally posted by cycheang

quote:
Originally posted by tkizer

Using the copy database wizard is the worst possible thing to choose for a database upgrade.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Any reason for this ?

Because it's not reliable and does not give you an EXACT copy. Backup/restore does give you an exact copy and also allows you to prep the database ahead of time so that there is minimal downtime.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-26 : 12:59:49

quote:
Originally posted by cycheang

If I were use the backup restore method from 2000 -> 2008 -> 2012.
After I did the restore in SQL 2008, can I skip to perform DBCC check, DBCC UPDATEUSAGE ...
I will change the compatibility mode. Then do backup again in SQL 2008. After all databases is already finished restore then
I only do the all the DBCC check ?

I don't typically have those as part of my upgrade process. I would just check the 2000 databases the day before and then check them after the upgrade to 2012.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cycheang
Starting Member

40 Posts

Posted - 2015-03-27 : 05:27:15

Hi tkizer,

Appreciate your advise given. Can you give me some hints after the restoration complete, what is the step I should take note other than increase the compatibility level to 110 for all databases immediately after the restoration process is completed.

Can you help to list is out such as maintenance to improve the performance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 05:34:42

I don't have a list handy, but this is a well documented upgrade. I would search for article on 2012 upgrades and go from there. Some will suggest updating stats on all tables.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 06:33:40

quote:
Originally posted by cycheang

Can you give me some hints after the restoration complete, what is the step I should take note other than increase the compatibility level to 110 for all databases immediately after the restoration process is completed.

There are two threads on here which may help you.

This is the more complete, it governs upgrading from SQL 2005 and SQL 2008

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

Much of it is applicable to upgrading between any version ... but this thread is specific to SQL 2000 to SQL 2008

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80138

but as I said, that thread is less "well thought through" than the one higher up, but it does contain stuff specific to SQL 2000 to SQL 2005.

cycheang
Starting Member

40 Posts

Posted - 2015-03-30 : 21:55:24

Thanks to tkizer and Kristen for your recommendation.

cycheang
Starting Member

40 Posts

Posted - 2015-03-31 : 23:46:27

I'm using sql server management studio 2012, I tried to right click the table and edit I row of data (some columns data) and there is no updated record when I clicked on the execute button. Any reason or anything I missed out.

I have no issue in SQL Server Enterprise manager, I can edit any column with any value.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources