import database.mdf to new server
Alan Murrell
I am one of the SysAdmins for a small webhosting/colocation company.
One of the sites we host has just recently come over from another
company. The other hosting company sent us a copy of their site +
database on a CD. The database they sent is the *.mdf file, as
opposed to an actual "dump" file (i.e., SQL script file). I tried
creating the database on our server (SQL 2000), then replacing the
*.mdf file that was created with the once provided by the other
hosting company, but then the database had the word "suspect" beside
it when the SQL service was restarted.
I then saw a reference to importing an *.mdf file into an exisiting
database by using the following sytax in the SQL Query Analyzer:
-----
EXEC sp_attach_db @dbname = 'database',
@filename1 = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\database.mdf',
@filename2 = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\database_log.ldf'
-----
My questions (which are prolly simplistic, but I'm relatively new to
MS SQL servers):
1. Do I write the '@dbname', '@filename1', and '@filename2' just
like that, or are they "placeholder" names, and should be replaced by
something else which may be specific to my machine?
2. Which database does the "sp_attach_db @dbname = 'database'" refer
to: the (empty) database I created on the SQL server, and into which I
wish to import the existing 'database.mdf' file, or the full path/name
of the 'database.mdf' I wish to import? I only ask, because it looks
like the '@filename1' and '@filename2' lines would be referring to the
path to the (empty) database that was created on the server, and into
which I wish to import.
TIA for your help and guidance,
Alan Murrell <swa...@hotmail.com>
BP Margolin
Please see inline ...
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Alan Murrell" <swa...@hotmail.com> wrote in message
news:6b9fbc17.02092...@posting.google.com...
> Hello,
>
> I am one of the SysAdmins for a small webhosting/colocation company.
> One of the sites we host has just recently come over from another
> company. The other hosting company sent us a copy of their site +
> database on a CD. The database they sent is the *.mdf file, as
> opposed to an actual "dump" file (i.e., SQL script file). I tried
> creating the database on our server (SQL 2000), then replacing the
> *.mdf file that was created with the once provided by the other
> hosting company, but then the database had the word "suspect" beside
> it when the SQL service was restarted.
>
> I then saw a reference to importing an *.mdf file into an exisiting
> database by using the following sytax in the SQL Query Analyzer:
>
> -----
> EXEC sp_attach_db @dbname = 'database',
> @filename1 = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\database.mdf',
> @filename2 = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\database_log.ldf'
> -----
>
> My questions (which are prolly simplistic, but I'm relatively new to
> MS SQL servers):
>
> 1. Do I write the '@dbname', '@filename1', and '@filename2' just
> like that, or are they "placeholder" names, and should be replaced by
> something else which may be specific to my machine?
They are the parameters used by the stored procedure. Stored procedures can
accept values either positionally, in which case using the parameter names
is optional, or non-positionally, in which case using the parameter names is
mandoratory. In short, the safest policy is to use the parameter names ...
so the actual command will look exactly like the code you posted, except for
the fully qualified location of your .mdf and .ldf files.
> 2. Which database does the "sp_attach_db @dbname = 'database'" refer
> to: the (empty) database I created on the SQL server, and into which I
> wish to import the existing 'database.mdf' file, or the full path/name
> of the 'database.mdf' I wish to import? I only ask, because it looks
> like the '@filename1' and '@filename2' lines would be referring to the
> path to the (empty) database that was created on the server, and into
> which I wish to import.
Drop the "empty" database. The database, for all practical purposes, is the
.mdf file, and by attaching it you will "create" the databsae. The
parameters refer to where ever you have copied the .mdf / .ldf to on the
server.