February 1, 2009 at 9:17 am
My current setup is a server with SBS 2003 running SQL server 2000 sp4 and a second server running Windows server 2008 64 bits and SQL server 2008.
I'm trying to migrate a small database from Sql server 2000 to a Sql server 2008 using the Copy database task under Sql server management studio. I'm always getting an error in the last step and i don't know what i'm doing wrong.
I'm getting a Messenger service not running but since i'm pretty new to SQL server 2008, i have no idea how to fix it.
Can someone give me a couple of clue to solve my problem ?
Thank you
February 1, 2009 at 9:52 am
Well, unless you only want to copy a subset of the database, I would not use the copy wizard. Use detach -copy-attach or backup-copy-restore.
---------------------------------------------------------------------
February 1, 2009 at 10:05 am
This is what i've try first, to make a backup copy of the SQL 2000 database and to try to restore it on the SQL 2008 but SQL 2008 didn't recognize the backup file.
Maybe i've didn't do it the right way. I will retry that.
Thank you
February 1, 2009 at 10:11 am
binettec (2/1/2009)
This is what i've try first, to make a backup copy of the SQL 2000 database and to try to restore it on the SQL 2008 but SQL 2008 didn't recognize the backup file.
It should do, providing it was a SQL native backup and you restored using the SQL restore command. What error did you get?
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
February 2, 2009 at 9:29 am
Ok, the backup restore procedure worked fine.... I've forgot to check the replace Option the first time i've used it.
So much time lost by a so tiny detail.
Thank's
February 2, 2009 at 9:37 am
Woops,
I have now this error while restoring.
There is already an object named 'sysnsobjs' in the database.
Msg 3167, Level 16, State 1, Line 1
Maybe it's the way i'm backing up the original database.
How can i solve this problem ?
February 2, 2009 at 9:49 am
doesnt sound like an error from a sql restore job. pls post the backup and restore commands you are using, and the results of
restore filelistonly from disk = 'path to your backup file'
---------------------------------------------------------------------
February 2, 2009 at 10:14 am
For the backup, i'm using the command:
backup database ssjb to disk = 'c:ssjbbackup.bak'
And for the restore, this one:
restore database [ssjb] from disk = 'e:ssjbbackup.bak'
February 2, 2009 at 10:19 am
And i'm getting thoses messages:
Processed 41240 pages for database 'ssjb', file 'master' on file 1.
Processed 1 pages for database 'ssjb', file 'mastlog' on file 1.
Converting database 'ssjb' from version 539 to the current version 655.
Database 'ssjb' running the upgrade step from version 539 to version 551.
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'sysnsobjs' in the database.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'ssjb'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
February 2, 2009 at 10:23 am
The production DB is still at the SP 3 on SS2000. Maybe that's the problem. I'm upgrading it an i will retry.
February 2, 2009 at 10:46 am
binettec (2/2/2009)
The production DB is still at the SP 3 on SS2000. Maybe that's the problem. I'm upgrading it an i will retry.
the min requirement for sql 2000 to sql 2005 was SP3, not sure if it is the same for 2008.
have you run the 2008 upgrade advisor against your 2000 instance? may highlight the problem and a fix. Perhaps you have a table name that is reserved in 2008.
when you run the backup use with init unless you are backing up to a different file name each time
---------------------------------------------------------------------
February 2, 2009 at 12:39 pm
I have upgrade to SP4 but the problem persist.
I've try the detach and attach method but i'm getting an other error with that. Even if i'm using a unique DB name, SS tell me that i can't attach a database with the same name as an existing database.
There's no existing database using the name of the one that i want to attach. Still in the dark.
February 2, 2009 at 12:49 pm
Please post the code you're using and the exact error message that you're getting.
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
February 2, 2009 at 1:14 pm
For the Detach/Attach process, i don't use any code, just the Management Studio of SS2088.
So this is what i'm getting when i'm trying to attach the database file to SS2008
--------------------
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'SSJBSERV-PROD'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
There is already an object named 'sysnsobjs' in the database.
Converting database 'ssjb2' from version 539 to the current version 655.
Database 'ssjb2' running the upgrade step from version 539 to version 551. (Microsoft SQL Server, Error: 2714)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=2714&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
February 2, 2009 at 1:17 pm
this is the script generated by the Mngnt Studio
----
USE [master]
GO
CREATE DATABASE [ssjb2] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ssjb2.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ssjb2_log.ldf' )
FOR ATTACH
GO
-----
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply