September 28, 2012 at 8:08 am
I have a database of size 42gb on production which is 2000sql standard edition and want it to move to sql 2008 standard on cluster.Can any one help me with the steps and complexities involved. I am confused to choose which way.
September 28, 2012 at 8:29 am
stillconfused (9/28/2012)
I have a database of size 42gb on production which is 2000sql standard edition and want it to move to sql 2008 standard on cluster.Can any one help me with the steps and complexities involved. I am confused to choose which way.
The first thing to do when attaching it to sql server 2008 is to run
DBCC UPDATEUSAGE (0)
This should be followed by
DBCC CHECKDB WITH data_purity, all_errormsgs, NO_INFOMSGS
It would be a good idea to rebuild all your indexes too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 28, 2012 at 8:46 am
hey perry,
Can you help me with the process.
If I take backup and restore and later go with revlogin etc stuff can I be getting database objects from 2000 to 2008?and after moving data shall I run indexfrag,updatestats ...... I am confused with the picture in my mind
September 28, 2012 at 8:49 am
First, run these on the SQL 2000 database
DBCC CheckDB
DBCC CheckCatalog
If neither return an error, take a backup, restore that backup on the SQL 2008 instance. Then the two commands Perry gave, then rebuild all indexes and update all statistics with fullscan
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 8:55 am
stillconfused (9/28/2012)
hey perry,Can you help me with the process.
If I take backup and restore and later go with revlogin etc stuff can I be getting database objects from 2000 to 2008?and after moving data shall I run indexfrag,updatestats ...... I am confused with the picture in my mind
and after the detail below then use revlogin to port your logins across 😉
GilaMonster (9/28/2012)
First, run these on the SQL 2000 databaseDBCC CheckDB
DBCC CheckCatalog
If neither return an error, take a backup, restore that backup on the SQL 2008 instance. Then the two commands Perry gave, then rebuild all indexes and update all statistics with fullscan
I generally don't bother with checkdb on the source server, i just run the commands i stated on the target instance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 28, 2012 at 8:58 am
thats kool thanks a lot... and what if we have DTS packages too
September 28, 2012 at 9:40 am
Perry Whittle (9/28/2012)
GilaMonster (9/28/2012)
First, run these on the SQL 2000 databaseDBCC CheckDB
DBCC CheckCatalog
If neither return an error, take a backup, restore that backup on the SQL 2008 instance. Then the two commands Perry gave, then rebuild all indexes and update all statistics with fullscan
I generally don't bother with checkdb on the source server, i just run the commands i stated on the target instance.
The reason I suggest those is that if someone's been messing with the system tables on SQL 2000, you really want to find and fix those problems before upgrading where it's still reasonably easy to fix. You also probably don't want to try to upgrade a database that's already corrupt.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 9:57 am
stillconfused (9/28/2012)
thats kool thanks a lot... and what if we have DTS packages too
How do you intend to store your packages under 2008, filesystem, sql, SSIS?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 28, 2012 at 10:14 am
well if any DTS packages are there in 2000 then what are the possibilities of moving them..I have never done that before so need some suggestions.
September 28, 2012 at 2:47 pm
October 1, 2012 at 4:28 pm
stillconfused (9/28/2012)
well if any DTS packages are there in 2000 then what are the possibilities of moving them..I have never done that before so need some suggestions.
The simplest way in my opinion is to continue running them as DTS packages in your new environment and then migrate them to SSIS gradually as you have time to port and test them.
Support for SQL Server 2000 DTS in SQL Server 2008 R2
I would follow Gail's recommendation as well. Run DBCC CHECKDB on the SQL 2000 database(s) prior to attempting the migration.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 8:17 am
I took your advice and did DBCC CHECKCATALOG on production as we are moving it to 2008.
so what exactly does the moving mean and how do I correct it?
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 544525519 (object '544525519') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
October 3, 2012 at 9:00 am
Nice, that's exactly the thing you want to find before the upgrade, that's easy to fix on SQL 2000, very very hard on SQL 2008.
What is object 544525519 ? (check sysobjects)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2012 at 10:02 am
Perry
I took your suggestion and did a
DBCC UPDATEUSAGE (0)
and
DBCC CHECKDB WITH data_purity, all_errormsgs, NO_INFOMSGS
as I am porting to 2008 in a couple of weeks.
I got the error
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=576525633) of row (object_id=576525633,column_id=23) in sys.columns does not have a matching row (object_id=576525633) in sys.objects.
I tried to identify the object using SELECT Object_Name(576525633) but it returns a NULL record. I have about 3 of these objects in the error list that cannot be found. Is this a concern and how could I get rid of them. BTW they show up in the normal production system.
Thanks
October 3, 2012 at 10:12 am
SELECT Object_Name(544525519) returns a NULL record. I have about 3 of them. How can I get rid of them?
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply