SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Convert 2000 db to 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

389 Posts

Posted - 03/25/2013 :  22:02:08  Show Profile  Reply with Quote
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

USA
190 Posts

Posted - 03/28/2013 :  08:33:55  Show Profile  Visit sql-programmers's Homepage  Reply with Quote
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/
Go to Top of Page

sql-programmers
Posting Yak Master

USA
190 Posts

Posted - 03/28/2013 :  08:37:19  Show Profile  Visit sql-programmers's Homepage  Reply with Quote
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/
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 03/28/2013 :  09:20:05  Show Profile  Reply with Quote
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
Go to Top of Page

jbates99
Constraint Violating Yak Guru

389 Posts

Posted - 04/01/2013 :  11:58:43  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/01/2013 :  13:33:13  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000