Re: Copying a database between servers

Tech-Archive recommends: Fix windows errors by optimizing your registry

David Gugick wrote: 
Edward Diener wrote:

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.

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.
.

Relevant Pages

  • Re: unable to start SQL Service ... error code 3417
    ... How about just create a new instance and restore databases to the new instance? ... I wouldn't go with restoring master from another instance. ... The supported route is to rebuild your master database, start SQL Server in single user mode, ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 SP4 on 2003 Server - Single User Mode
    ... I found the section where you list the filename to restore to. ... rename the SQL Server the same as what is listed below Courthouse? ... Once you've created your database (simply right click on the Database ...
    (microsoft.public.sqlserver.setup)
  • Re: Moving databases from SQL 7 to SQL 2000 - More Questions!
    ... want to run sp_updatestats after the restore or attach as well. ... >>There is a lot more that can go wrong with the wizard ... > SQL Server Databases ... >>Issues When a Database Is Moved Between SQL Servers ...
    (microsoft.public.sqlserver.setup)
  • Re: Restore Master DB to New Server
    ... the user database will be suspect. ... Just drop them and then do the restore. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.tools)
  • Re: HELP!!! restore database - left in loading state?? PLEASE H
    ... RESTORE DATABASE WITH RECOVERY ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.tools)