June 18, 2008 at 7:14 am
hi all,
i am doing an upgrade from sql2000 to sql2005 uisng copy database wizard i get the following error. i cannot understand as to why this error is being throiwn. when i check the d/b is created on sql2005.if i sue attachdb-dettachdb the d/b is attched to sql server 2005 without any errors. is this error telling me that there is some discrepancies with FK - PK relationship. How do i correct this error.
thanks for your help.
Executed as user: TEST-SHARON\SYSTEM. ...ROLOGUE SQL USE [Payroll_System] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Dependents_CollectionOffice]') AND parent_object_id = OBJECT_ID(N'[dbo].[Dependents]')) ALTER TABLE [dbo].[Dependents] DROP CONSTRAINT [FK_Dependents_CollectionOffice] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Dependents_Employee]') AND parent_object_id = OBJECT_ID(N'[dbo].[Dependents]')) ALTER TABLE [dbo].[Dependents] DROP CONSTRAINT [FK_Dependents_Employee] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__SubGrade___CLOID__6EF57B66]') AND parent_object_id = OBJECT_ID(N'[dbo].[SubGrade]')) ALTER TABLE [dbo].[SubGrade] DROP CONSTRAINT [FK__SubGrade___CLOID__6EF57B66] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__Bank_Deta__CLOID__68487DD7]') AND parent_object_id = OB... The package execution fa... The step failed.
June 18, 2008 at 8:27 am
I have to admit, I haven't had a lot of luck with the wizard for moving databases. I usually just back them up and then restore them on the new server/instance.
I have a few scripts I run when upgrading from 2000 to 2005. Here's the main one:
use
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
- 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
June 18, 2008 at 9:27 am
I've had issues with the wizards as well. Use a backup / restore or a detach attach to move databases.
June 18, 2008 at 9:29 am
This ties in to the questoin I want to ask!
We are upgrading our from SQL 2000 to SQL 2005. We will be building a new production server when we do this (SQL 2000 will NOT be on the new server). The old server will go away as soon as the migration happens.
I have successfully done a restore of the SQL 2000 DB into our test SQL 2005 instance.
I have been reading about doing a Detach and Attach to accomplish this task. Seeing as this is a completely new server, is the Backup and Restore a valid solution to performing this migration?
The steps I am following for the Backup / Restore are:
- Restore the DB backup to a NEW instance
- Remove the Schemas and Users
- Recreate the Users
- Change the Owner (DB Properties / Files)
1) I have not changed the Compatibility Level to 2005. Should I ?
2) Is there any problem with doing the migration this way?
3) Is there any advantage to doing the Detach / Attach? (Remember, this is a NEW server).
June 18, 2008 at 10:46 am
The main advantage to backup-restore vs detach re-attach is that backup-restore only requires you to move one file (the backup file) from one server to the other. If you use a program that compresses the backup (like the one from RedGate), moving it takes less time too.
Of course, if both servers use the same SAN for their databases, then detach-reattach doesn't require moving any files, so it would have the advantage there.
The disadvantage of backup-restore is that the log file won't include any completed transactions at that point. It only backs up stuff that's in progress. Minor, and only matters in a few rare cases, but it is something to keep in mind.
I prefer backup-restore because of only having to move one file. More convenient.
Otherwise, both solutions work just fine.
On changing the compatibility level from 80 to 90 (SQL 2000 to SQL 2005), there are advantages and drawbacks to both answers. 90 gives you a number of improvements, at the cost of having to make sure all your code is compatible. 80 gives you no improvements, but you also don't have to rewrite any code.
The script I included (which I got from the scripts page of this site), has a cursor in it that will go through your procs and functions and make sure they will at least compile in SQL 2005. Doesn't guarantee they will work as expected, but it at least makes sure they will compile. That can save a lot of time in the migration. The rest of the script does the necessary table actions to get the database up to 2005.
- 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
June 18, 2008 at 11:07 am
Thanks.
The log is not an issue. The app will be down during the migration. Moving the DB is also not an issues.
I ran the script above and got no errors. Although I needed to make one change: I added the braces in the line below.
select @sql = 'update statistics dbo.[' + @table + '] with fullscan'
I have a table named User !!
So far all testing seems fine. I also have a utility that I wrote that scripts all our SPO's and FN's. We use this to work with SVN and manage our dev changes. The utility also generates a script that drops and recreates all SPO's and FN's. When I ran this script it did not produce any errors.
Thanks for all your help.
June 18, 2008 at 1:59 pm
You're welcome.
- 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