If you're talking about moving a database, a coworker ran into this
recently. Here's what he found out, verbatim: (in other words, your mileage
may definitely vary, no I haven't tried it, etc.)
-------------------------------------------snip
- From EM (Enterprise Manager), perform a backup of the database to a file
(NWT.BAK). When specifying a file name put the '.BAK' on the end of it. (The
resultant file can be compressed for permanent storage).
- Copy the backup file (NWT.BAK) to some location on the destination server.
The typical place for backup files is in the "BACKUP" sub-directory of the
SQL Server data directory.
Here is where the problem comes in. If the destination server does not have
the same DATA directory location and structure as the source server, you
will receive an error when trying to perform a restore from within EM. The
error indicates that we should use the 'MOVE' option. If you receive this
error, the only way to restore the database is to use the more versatile
commands found in TRANSACT SQL.
- Start a SQL Server Query Analyzer window attached to the destination
server as dbo equivalent and issue the following commands.
USE MASTER (press F5 to execute the command)
RESTORE FILELISTONLY
FROM DISK = 'F:\NWT.BAK' (F5)
(Condensed Results)
LogicalName
--------------------- -------------------------------
--------------------
Northwind D:\MSSQL7DATA\DATA\northwnd.mdf
Northwind_log D:\MSSQL7DATA\DATA\northwnd.ldf
Examine the list of files in the backup, pay particular attention to the
'Logical Name' and Physical Name fields. Then, issue the restore command:
RESTORE DATABASE NWT
FROM DISK = 'F:\NWT.BAK'
WITH RECOVERY,
MOVE 'Northwind' to 'NWT.MDF',
MOVE 'Northwind_log' to 'NWT.LDF' (don't forget the F5)
There are many more options and this assumes that we wanted a copy of
Northwind with different logical and file names, and that there were
conflicts with existing files.
I found this information in the online 'SQL Server Books Online' help file
in the "Transact-SQL | Administering SQL Server | Backing Up and Restoring
Databases" section.
Hope that you find this useful.
Nick Call wrote:
> Help SQL Server 7.0 gurus....
>
> I need to attach a SQL database to my SQL server. It was created on another
> machine, and you know how MS SQL hates foreign DB's. It was suggested that
> I try "attach_db", but all I get is the tables, not the data. I am NOT a
> SQL guru. Please help. I will be working all weekend until I figure this
> out.
--------------------------------------------------------------------
Darryl Davidson [EMAIL PROTECTED]
SCIENTECH, Inc.
Idaho Falls, Idaho, USA 83402
--------------------------------------------------------------------
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
