Copying a database between servers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Edward Diener - 29 Apr 2005 03:22 GMT

Is backup and restore the best way to simply copy a database from one
SQL Server 7.0 database with 'select' access to another SQL Server 7.0
database on another machine with 'all' access ? Or is there another
easier way with the SQL Server 7.0 tools ?

Reply to this Message

David Gugick - 29 Apr 2005 04:42 GMT

> Is backup and restore the best way to simply copy a database from one
> SQL Server 7.0 database with 'select' access to another SQL Server 7.0
> database on another machine with 'all' access ? Or is there another
> easier way with the SQL Server 7.0 tools ?

You could try detaching and reattaching the database using sp_detach_db
and sp_attach_db / sp_attach_single_file_db. You would need to stop the
server and copy the data and log files and attach the copy. You wouldn't
need to detach in this case. When you attach the copy, you'll likely get
an error related to the log file since the data file points to a log
file in use by the original database. SQL Server 2000 will create a new
log file and attach. I'm not sure if SQL 7 will do the same, but it
likely will.

Signature

David Gugick
Imceda Software
www.imceda.com

Reply to this Message

Edward Diener - 29 Apr 2005 05:52 GMT

>> Is backup and restore the best way to simply copy a database from one
>> SQL Server 7.0 database with 'select' access to another SQL Server 7.0
[quoted text clipped - 9 lines]
> log file and attach. I'm not sure if SQL 7 will do the same, but it
> likely will.

Can this detach/attach be done with Enterprise Manager and, if not, how
do I do it ?

I tried to backup and restore but SQL Server 7 would only allow me to
backup on the machine where the server resides in which is the database
I want to backup, and would only allow me to restore from the machine
where is the server to which I wanted to restore the database. Now that
is what I call flexibility ! Why I can not backup and restore to and
from any machine to which I am connected and have directory rights I do
not know.

Reply to this Message

David Gugick - 29 Apr 2005 06:03 GMT

> Can this detach/attach be done with Enterprise Manager and, if not,
> how do I do it ?

No. You have to run the commands I mentioned.

- Use the database you want to copy in query analyzer
- Run sp_helpfile and note the locations of all data and log files
- Stop the SQL Server
- Open Explorer and make a _copy_ of all data and log files from
sp_helpfile
- Start SQL Server
- Run either sp_attach_db or sp_attach_single_file_db with the new
database name and data file location. For example, for
sp_attach_single_file_db:

Exec sp_attach_single_file_db 'NewDBName', 'C:\Data\NewDataFile.mdf'
-- You'll likely see an error on the log file and a message indicating
the new log file name
-- you can then delete the copied log file since it won't be used any
longer

Signature

David Gugick
Imceda Software
www.imceda.com

Reply to this Message

Steen Persson - 29 Apr 2005 11:59 GMT

I'm not sure about SQL7.0, but SQL2000 will without any problems backup and
restore databases from other servers. In 2000 you can backup to an UNC path
or a local drive and the same goes for the restore. Attaching and Detaching
the files as DAvid explains will work, but you have to remember that it's an
offline operation where your source database will be unavailable while you
are copying the files. Also there're more steps to be done than if you just
backup the database and then restore it on the new server.

I'm not an expert in doing this from EM, but maybe others can help you with
that. I'd suggest that you look up the Backup and Restore command in Books
On Line and then do it from Query Analyzer - that will give you more options
and flexibility.

Regards
Steen

>>> Is backup and restore the best way to simply copy a database from
>>> one SQL Server 7.0 database with 'select' access to another SQL
[quoted text clipped - 20 lines]
> and restore to and from any machine to which I am connected and have
> directory rights I do not know.

Reply to this Message