Convert 2000 db to 2008 - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

Convert 2000 db to 2008

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-03-25 : 22:02:08

Hi all,

I have a database running on a 2008 R2 instance but it uses the 2000 Compatibility Level. I'm tired of looking at that so I want to upgrade/convert it.

1. Are there any gotchas in just flipping the compatibility to 2008 and running my script to update the views?

2. Are there any real advantages to switching the compatibility to 2008 R2?
Thanks, Jack

sql-programmers
Posting Yak Master

190 Posts

Posted - 2013-03-28 : 08:33:55

Hi,

You can try this,

1. Take a backup of your database in SQL Server 2000 to .bak file.
2. Move the *.bak to new server.
3. Restore the *.bak file in SQL Server 2008, on your new server.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2013-03-28 : 08:37:19

The difference and advantages of SLQ Server 2000 & 2008 are in the following link.

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

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-03-28 : 09:20:05

I believe the problem jbates99 is having is not moving to 2008 R2 but changing the compatibility level http://msdn.microsoft.com/en-us/library/bb510680(v=sql.105).aspx within 2008 R2.

djj

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-04-01 : 11:58:43

You are correct, djj55. Someone else restored this 2000 database onto a 2008 R2 instance - but they didn't change the Compatibility Level to 2008.

This is what I usually do to "convert" a db to 2008:
1. Manually flip the Compatibility mode to 2008

2. ALTER DATABASE tkcdsb SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;
USE tkcsdb;
SELECT 'ALTER INDEX ALL ON dbo.[' + [name] + '] REBUILD '
+ 'WITH (ONLINE=OFF, STATISTICS_NORECOMPUTE = OFF)'
FROM sys.tables
ORDER BY [name];
USE tkcsdb;
SELECT 'UPDATE STATISTICS dbo.[' + [name] + '] WITH FULLSCAN'
FROM sys.tables
ORDER BY [name];
SELECT 'UPDATE STATISTICS dbo.[' + [name] + '] WITH FULLSCAN'
FROM sys.views
ORDER BY [name];

DBCC UPDATEUSAGE (tkcsdb) WITH COUNT_ROWS;
Use tkcsdb;
DBCC CHECKDB WITH NO_INFOMSGS, DATA_PURITY;

Some of these steps may not be necessary; I just run them to be sure the statistics are current, etc.

I would appreciate any feedbac or advice.
Thanks, Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 13:33:13

hopefully you dont have any breaking changes present in 2000 db

http://msdn.microsoft.com/en-us/library/ms143179(v=sql.100).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources