Migrating SQL2000 Databases to SQL2005

9 Dec 2008 2:36 PM

Julie

Good Morning,

I have a generic question in regards to migrating databases from a SQL2000
Server to a SQL2005 Server. I am basically looking for suggestions on the
best way to do this. The current SQL2000 Server is running Windows Server
2000. The SQL2005 server will be running Windows Server 2003 Std. We have
about 6-7 custom databases on the 2000 server as well. We use Backup Exec
for our backup software, but are wondering if we should do a restore of the
databases or use the sp_detach and attach commands to move them. We actually
performed a test restore of all databases from Backup Exec and ran into
several problems trying to restore the system databases. If anyone has any
suggestions, I would really appreciate it. Thanks!!

9 Dec 2008 5:12 PM

Eric Isaacs

Why are you trying to restore the SQL Server 2000 system databases on
a SQL Server 2005 machine? You shouldn't need the System Database,
only your custom databases. I'm curious why you think you need them.

You can restore them in SQL Server 2000 compatibility mode on SQL
Server 2005, which should work flawlessly, but will not allow you to
take advantage of new SQL Server 2005 features. Restoring them to SQL
Server 2005 compatibility mode will upgrade them to 2005.

-Eric Isaacs

9 Dec 2008 6:03 PM

Julie

We were hoping to retain the logon accounts and all of the mainenance jobs we
have already created. I believe those are stored in the master and msdb
databases... should these be recreated on the new 05 server?

Show quoteHide quote

"Eric Isaacs" wrote:

> Why are you trying to restore the SQL Server 2000 system databases on
> a SQL Server 2005 machine? You shouldn't need the System Database,
> only your custom databases. I'm curious why you think you need them.
>
> You can restore them in SQL Server 2000 compatibility mode on SQL
> Server 2005, which should work flawlessly, but will not allow you to
> take advantage of new SQL Server 2005 features. Restoring them to SQL
> Server 2005 compatibility mode will upgrade them to 2005.
>
> -Eric Isaacs
>

9 Dec 2008 11:41 PM

bass_player

You can use the Transfer Logins Task and Transfer Jobs Task in SSIS for your
logins and jobs

Show quoteHide quote

"Julie" <Ju***@discussions.microsoft.com> wrote in message
news:3B33A13E-D5C7-43D6-927E-EB03EA5088BC@microsoft.com...
> We were hoping to retain the logon accounts and all of the mainenance jobs
> we
> have already created. I believe those are stored in the master and msdb
> databases... should these be recreated on the new 05 server?
>
> "Eric Isaacs" wrote:
>
>> Why are you trying to restore the SQL Server 2000 system databases on
>> a SQL Server 2005 machine? You shouldn't need the System Database,
>> only your custom databases. I'm curious why you think you need them.
>>
>> You can restore them in SQL Server 2000 compatibility mode on SQL
>> Server 2005, which should work flawlessly, but will not allow you to
>> take advantage of new SQL Server 2005 features. Restoring them to SQL
>> Server 2005 compatibility mode will upgrade them to 2005.
>>
>> -Eric Isaacs
>>