How to move a SQL Server 7 (no Service Pack) database to SQL Server 2005???

1 Jul 2006 6:08 AM

Alan Mailer

I'm not proud of this, but I currently run a database on SQL Server 7
with NO service pack applied.

How do I transition that database to SQL Server 2005? Admittedly my
research has been very preliminary, but I'm not finding much help on
how this can be done. I'm sorry if this has been asked a million
times.

Thanks in advance for any all help you care to give.

1 Jul 2006 7:56 AM

Tibor Karaszi

How about backup/restore?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Alan Mailer" <clarityas***@earthlink.net> wrote in message
news:f04ca2lmn3p7kdiu4pfcqb77gue76eh7n7@4ax.com...
> I'm not proud of this, but I currently run a database on SQL Server 7
> with NO service pack applied.
>
> How do I transition that database to SQL Server 2005? Admittedly my
> research has been very preliminary, but I'm not finding much help on
> how this can be done. I'm sorry if this has been asked a million
> times.
>
> Thanks in advance for any all help you care to give.

1 Jul 2006 4:12 PM

Alan Mailer

Tibor,

Thanks for responding. Backup/restore would be ideal under the
circumstances.

I will be installing SQL Server 2005 on a different machine than the
one that currently runs SQL Server 7. Does your suggestion mean that
accomplishing what I need to do here is just a matter of asking my new
SQL Server 2005 to 'restore' my .BAK files from SQL Server 7? Are you
saying that SQL Server 2005 will automatically do everything it needs
to do to these legacy files to turn them into SQL Server 2005 -
acceptable data??? (This would be SO great!)

Thanks again in advance to you and anyone who can provide this SQL
Server semi-novice with some help!

On Sat, 1 Jul 2006 09:56:35 +0200, "Tibor Karaszi"
<tibor_please.no.email_kara***@hotmail.nomail.com> wrote:

Show quote

>How about backup/restore?

1 Jul 2006 6:47 PM

Tibor Karaszi

Yes and yes. The database will be in compatibility level 70 after the restore. Read about
sp_dbcmptlevel for how to change it and consequences.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Alan Mailer" <clarityas***@earthlink.net> wrote in message
news:h97da2lro62ngsb6cnq358l49soq78fu00@4ax.com...
> Tibor,
>
> Thanks for responding. Backup/restore would be ideal under the
> circumstances.
>
> I will be installing SQL Server 2005 on a different machine than the
> one that currently runs SQL Server 7. Does your suggestion mean that
> accomplishing what I need to do here is just a matter of asking my new
> SQL Server 2005 to 'restore' my .BAK files from SQL Server 7? Are you
> saying that SQL Server 2005 will automatically do everything it needs
> to do to these legacy files to turn them into SQL Server 2005 -
> acceptable data??? (This would be SO great!)
>
> Thanks again in advance to you and anyone who can provide this SQL
> Server semi-novice with some help!
>
> On Sat, 1 Jul 2006 09:56:35 +0200, "Tibor Karaszi"
> <tibor_please.no.email_kara***@hotmail.nomail.com> wrote:
>
>>How about backup/restore?

2 Jul 2006 2:17 PM

Aaron Bertrand [SQL Server MVP]

You may need to issue a more complex RESTORE command -- e.g. WITH MOVE if
your files are not going to live in the same place. For example, if the
data drive on your old machine was on E: and on the new machine the files
should live on F:, a simple restore will try to place the files in the same
location on E: where they lived on the old machine.

See the RESTORE topic in Books Online...

Show quote

"Alan Mailer" <clarityas***@earthlink.net> wrote in message
news:h97da2lro62ngsb6cnq358l49soq78fu00@4ax.com...
> Tibor,
>
> Thanks for responding. Backup/restore would be ideal under the
> circumstances.
>
> I will be installing SQL Server 2005 on a different machine than the
> one that currently runs SQL Server 7. Does your suggestion mean that
> accomplishing what I need to do here is just a matter of asking my new
> SQL Server 2005 to 'restore' my .BAK files from SQL Server 7? Are you
> saying that SQL Server 2005 will automatically do everything it needs
> to do to these legacy files to turn them into SQL Server 2005 -
> acceptable data??? (This would be SO great!)
>
> Thanks again in advance to you and anyone who can provide this SQL
> Server semi-novice with some help!
>
> On Sat, 1 Jul 2006 09:56:35 +0200, "Tibor Karaszi"
> <tibor_please.no.email_kara***@hotmail.nomail.com> wrote:
>
>>How about backup/restore?