August 29, 2008 at 5:31 am
I need a simple guideline in order to perform the upgrade of SQL 2000 to SQL 2005.
August 29, 2008 at 5:37 am
August 29, 2008 at 5:37 am
I hate to say this but you really should read the directions. There is a lot of value in what MS puts together for upgrade instructions. I have found them to save me a bunch of headaches even when I thought I knew what I was doing.
With that being said, have backups (I always say this and I feel like I am always repeating myself but this is critical), if you have replication do some research as that is not pretty in the upgrade process, and test the upgrade on another box prior to doing it. If you don't have a test box, get managment to give you one. It is the business data so testing is very important.
....and of course post back here if you have questions or problems.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 5:45 am
http://www.sqlservercentral.com/articles/Administration/2987/
Just seen your last post... good article though!
August 29, 2008 at 6:38 am
Hi,
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
You can download the Upgrade Advisor and some useful documents from this link.
Hope this helps
August 29, 2008 at 8:19 am
I have a script that I use when switching from 2000 to 2005.
use [database] -- Please remember to fill this in!!
go
dbcc UPDATEUSAGE (0) WITH NO_INFOMSGS;
go
DBCC CHECKDB (0) with no_infomsgs;
go
-- Update all table stats
declare Tables cursor local fast_forward
for
select name
from sys.all_objects
where type = 'u'
declare @Table varchar(100), @sql varchar(1000)
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
select @sql = 'update statistics dbo.' + @table + ' with fullscan'
exec (@sql)
--raiserror(@table, 10, 1) with nowait
fetch next from tables into @table
end
close tables
deallocate tables;
go
DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(500),
@ProcName1 VARCHAR(500)
DECLARE @T TABLE
(ProcName VARCHAR(200),
sql VARCHAR(max),
ErrorMessage VARCHAR(4000))
DECLARE c Cursor FOR
select name, definition
from sys.all_objects
inner join sys.sql_modules
on all_objects.object_id = sql_modules.object_id
where type in ('p','tf','if')
and name not like 'dt_%'
and name not like 'sys_%'
Open C
FETCH NEXT FROM c INTO @ProcName, @Text
WHILE @@FETCH_STATUS = 0 BEGIN
SET @text = REPLACE(@text, @ProcName, @ProcName + 'CreateTest') -- change proc name
BEGIN TRY
EXEC(@text) -- try to create the proc
INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that could be created
END TRY
BEGIN CATCH
INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH
if exists
(select *
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type = 'p')
begin
set @sql = 'drop procedure ' +
(select name
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type = 'p')
exec(@sql)
end
if exists
(select *
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type in ('if','tf'))
begin
set @sql = 'drop function ' +
(select name
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type in ('if','tf'))
exec(@sql)
end
FETCH NEXT FROM c INTO @ProcName, @Text
END
CLOSE c
DEALLOCATE c
SELECT * FROM @T where errormessage is not null
order by procname
go
It doesn't do everything, but it's a very good start. It does the basic things Microsoft recommends, and then it also checks every proc and function and makes sure they will compile. (Checking that they will compile isn't the same as testing them to make sure they'll do what you actually want, but it does find the most obvious errors.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply