Moving 6.5 DB's

I have a legacy database installed on a SQL Server 2000 machine, running in
6.5 compatibility mode. What is the best method to move it to another SQL
Server 2000 server, while still in 6.5 compatibility mode?

More details: The DB is about 4.8GB. It is a data source for a 3rd party
application, so accessing the client code will be difficult at best (I do
know how to modify its .ini file to redirect the connection string to anothe
r
server, as this has been done before).  I am concerned with updating the
database to 2000, since I can't say for sure that the client app will still
work if I do.

I tried using a standard backup from EM, which failed. I discovered that
the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
still being available for backward compatibility , it doesn't show what the
syntax used to be or what arguments it used back then.

The method of moving the database I was trying to do was to back up the
database, and restore it to a different server. The DB is for archive
purposes only, so there are no transactions taking place.

I am concerned with the best way to do this. Since discovering that there is
no back-up of the database (The job set up by the previous DB Admin has been
failing all this time), and if I upgrade it, there is no fall-back. The only
way to recover it then is the server back-up tapes. This is the only copy of
this database, so I'm concerned with the best approach to this situation.

Of course, once I have it copied into the test environment, I will test how
it integrates with the client app after upgrading to 2000. But for now, I
just need to know how to safely move it from the production server to the
test server.

Suggestions? Thanks!

Joel,

First step - make a backup.  I'd also perform a DBCC CHECKDB on the database
as well.  The backup/restore method should work with no issues (outside of
possible drive/path differences).  Also, sp_detach_db and sp_attach_db would
probably be the easiest.  You can control the compatibility mode by using
sp_dbcmptlevel (see SQL BOL) for the database.

For moving the database see (in addition to SQL BOL):

http://support.microsoft.com/defaul...kb;en-us;314546

HTH

Jerry

"Joel" <Joel@discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D- 39424025DBBC@microso
ft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
>
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to
> another
> server, as this has been done before).  I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will
> still
> work if I do.
>
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what
> the
> syntax used to be or what arguments it used back then.
>
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
>
> I am concerned with the best way to do this. Since discovering that there
> is
> no back-up of the database (The job set up by the previous DB Admin has
> been
> failing all this time), and if I upgrade it, there is no fall-back. The
> only
> way to recover it then is the server back-up tapes. This is the only copy
> of
> this database, so I'm concerned with the best approach to this situation.
>
> Of course, once I have it copied into the test environment, I will test
> how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
>
> Suggestions? Thanks!

I thought about the detach / attach options as well. However, I'm trying to
copy the database, not move it. I thought detaching & attaching was only for
moving them.

"Jerry Spivey" wrote:

> Joel,
>
> First step - make a backup.  I'd also perform a DBCC CHECKDB on the databa
se
> as well.  The backup/restore method should work with no issues (outside of
> possible drive/path differences).  Also, sp_detach_db and sp_attach_db wou
ld
> probably be the easiest.  You can control the compatibility mode by using
> sp_dbcmptlevel (see SQL BOL) for the database.
>
> For moving the database see (in addition to SQL BOL):
>
> http://support.microsoft.com/defaul...kb;en-us;314546
>
> HTH
>
> Jerry
>
> "Joel" <Joel@discussions.microsoft.com> wrote in message
> news:C8C1EC22-A943-4E1F-BD2D- 39424025DBBC@microso
ft.com... 
>
>
>

Joel,

You can detach the database, copy the file, then attach the database both
locally and remotely.  Notice you'll be copying the file not moving it in
this case.

HTH

Jerry
"Joel" <Joel@discussions.microsoft.com> wrote in message
news:A22F722F-0274-431E-869D- E7AE35B0F6E6@microso
ft.com...
>I thought about the detach / attach options as well. However, I'm trying to
> copy the database, not move it. I thought detaching & attaching was only
> for
> moving them.
>
> "Jerry Spivey" wrote:
> 

Hi,

To add on to Joel; Since compatibility mode is stored in Master database --
sysdatabases table; you may need to set the
db compatibility level to 65 again using

EXEC sp_dbcmptlevel 'DBNAME', 65


Note:-

If you need to keep the source database online then use BACKUP / RESTORE
commds to copy the database.

1. Backup the database
2. copy the backup file to destination
3. Restore the database
4. Set the dbcopmatibility to 65
5. sync the logins and users using  sp_change_users_logi
n


Thanks
Hari
SQL Server MVP



"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:ucTpDiUxFHA.2312@TK2MSFTNGP14.phx.gbl...
> Joel,
>
> You can detach the database, copy the file, then attach the database both
> locally and remotely.  Notice you'll be copying the file not moving it in
> this case.
>
> HTH
>
> Jerry
> "Joel" <Joel@discussions.microsoft.com> wrote in message
> news:A22F722F-0274-431E-869D- E7AE35B0F6E6@microso
ft.com... 
>
>

DUMP and LOAD is essentially the same as BACKUP and RESTORE, for instance:

DUMP DATABASE db
TO DISK = 'C:\a.bak'

LOAD DATABASE db
FROM DISK = 'C:\a.bak'

I don't know if new features of BACKUP and RESTORE are available for DUMP an
d LOAD, like RECOVERY
etc. Wouldn't think so.

However, it doesn't matter. Always execute the backup and restore commands f
rom the master database,
and master cannot be in down-level compatibility mode. Or, use detach and at
tach, as suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Joel" <Joel@discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D- 39424025DBBC@microso
ft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
>
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to anot
her
> server, as this has been done before).  I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will stil
l
> work if I do.
>
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for h
ow
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what th
e
> syntax used to be or what arguments it used back then.
>
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
>
> I am concerned with the best way to do this. Since discovering that there 
is
> no back-up of the database (The job set up by the previous DB Admin has be
en
> failing all this time), and if I upgrade it, there is no fall-back. The on
ly
> way to recover it then is the server back-up tapes. This is the only copy 
of
> this database, so I'm concerned with the best approach to this situation.
>
> Of course, once I have it copied into the test environment, I will test ho
w
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
>
> Suggestions? Thanks!