Restore .mdf files

Hi All:
SQL Server 2000
We have a really huge problem that we need some advice on. I don't know if
this is the correct group or not, but hopefully someone can point us in the
right direction.
Yesterday we had some major problems with our server, and raid array,
resulting in the loss of our SQL database backups and the need to reformat
the hard drive.
We have all the .mdf files for the databases.
So, how the heck do we get them to work at this point, or without the
backups, are we hosed?
Any help would be very much appreciated
SteveYou might be able to use sp_attach_db or sp_attach_single_file_db.
Lots of documentation can be found within Books Online.
Did you backup your backups to tape?
-- Keith, SQL Server MVP
"Steve G" <steve@.nospam.tnccreations.com> wrote in message =news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Hi All:
> > SQL Server 2000
> > We have a really huge problem that we need some advice on. I don't =know if
> this is the correct group or not, but hopefully someone can point us =in the
> right direction.
> > Yesterday we had some major problems with our server, and raid array,
> resulting in the loss of our SQL database backups and the need to =reformat
> the hard drive.
> > We have all the .mdf files for the databases.
> > So, how the heck do we get them to work at this point, or without the
> backups, are we hosed?
> > Any help would be very much appreciated
> > Steve
> >|||Place the mdf files in their original paths and restart
the sql server. You shouldn't have any problems.
Make sure you have all the files in their original
directories before you faced problems.
Thanks
Ravi
>--Original Message--
>Hi All:
>SQL Server 2000
>We have a really huge problem that we need some advice
on. I don't know if
>this is the correct group or not, but hopefully someone
can point us in the
>right direction.
>Yesterday we had some major problems with our server,
and raid array,
>resulting in the loss of our SQL database backups and
the need to reformat
>the hard drive.
>We have all the .mdf files for the databases.
>So, how the heck do we get them to work at this point,
or without the
>backups, are we hosed?
>Any help would be very much appreciated
>Steve
>
>.
>|||As others mentioned read on sp_Attach_db.
Example for 2 file database:
EXEC sp_attach_db @.dbname = N'test2db',
@.filename1 = N'f:\test2db\TESTDB_Data.MDF',
@.filename2 = N'f:\test2db\TESTDB_Data1.NDF'
SQL Server will create a new log file and you should be fine.
"Steve G" <steve@.nospam.tnccreations.com> wrote in message
news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Hi All:
> SQL Server 2000
> We have a really huge problem that we need some advice on. I don't know if
> this is the correct group or not, but hopefully someone can point us in
the
> right direction.
> Yesterday we had some major problems with our server, and raid array,
> resulting in the loss of our SQL database backups and the need to reformat
> the hard drive.
> We have all the .mdf files for the databases.
> So, how the heck do we get them to work at this point, or without the
> backups, are we hosed?
> Any help would be very much appreciated
> Steve
>|||Thanks for the response, Keith
Unfortunately, no, we did not back up the backups to tape.
Researching how to use these procedures now.
Steve
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:e38iBDvSDHA.2768@.tk2msftngp13.phx.gbl...
You might be able to use sp_attach_db or sp_attach_single_file_db.
Lots of documentation can be found within Books Online.
Did you backup your backups to tape?
--
Keith, SQL Server MVP
"Steve G" <steve@.nospam.tnccreations.com> wrote in message
news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Hi All:
> SQL Server 2000
> We have a really huge problem that we need some advice on. I don't know if
> this is the correct group or not, but hopefully someone can point us in
the
> right direction.
> Yesterday we had some major problems with our server, and raid array,
> resulting in the loss of our SQL database backups and the need to reformat
> the hard drive.
> We have all the .mdf files for the databases.
> So, how the heck do we get them to work at this point, or without the
> backups, are we hosed?
> Any help would be very much appreciated
> Steve
>|||I think I need some additional help here :((
If I use:
EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
@.physname = 'c:\sql\hrbcoSQL_Data.MDF'
(the path specified is where the mdf file is stored currently)
I get this error:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
The path to the ldf file would represent the old virtual directory where the
file was stored previously.
This seems to contradict what the Book says about SQL creating a new log
file.
So, should I recreate the database first, then run this code?
EXEC sp_detach_db @.dbname = 'hrbcoSQL_Data'
EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
@.physname = 'c:\sql\hrbcoSQL_Data.MDF'
Copied from the book, then modified to reflect actual names/paths
Steve
"Steve G" <steve@.nospam.tnccreations.com> wrote in message
news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Hi All:
> SQL Server 2000
> We have a really huge problem that we need some advice on. I don't know if
> this is the correct group or not, but hopefully someone can point us in
the
> right direction.
> Yesterday we had some major problems with our server, and raid array,
> resulting in the loss of our SQL database backups and the need to reformat
> the hard drive.
> We have all the .mdf files for the databases.
> So, how the heck do we get them to work at this point, or without the
> backups, are we hosed?
> Any help would be very much appreciated
> Steve
>|||Well that didn't work...however, after modifying the code thusly:
EXEC sp_detach_db @.dbname = 'hrbcoSQL'
EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
@.physname = 'c:\sql\hrbcoSQL_Data.MDF'
I got this error:
Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'hrbcoSQL' because it is currently in use.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
I am beginning to think we may be hosed. :((
Steve
"Steve G" <steve@.nospam.tnccreations.com> wrote in message
news:Ofj986vSDHA.3144@.tk2msftngp13.phx.gbl...
> I think I need some additional help here :((
> If I use:
> EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> (the path specified is where the mdf file is stored currently)
> I get this error:
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
> The path to the ldf file would represent the old virtual directory where
the
> file was stored previously.
> This seems to contradict what the Book says about SQL creating a new log
> file.
> So, should I recreate the database first, then run this code?
> EXEC sp_detach_db @.dbname = 'hrbcoSQL_Data'
> EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> Copied from the book, then modified to reflect actual names/paths
> Steve
>
> "Steve G" <steve@.nospam.tnccreations.com> wrote in message
> news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> > Hi All:
> >
> > SQL Server 2000
> >
> > We have a really huge problem that we need some advice on. I don't know
if
> > this is the correct group or not, but hopefully someone can point us in
> the
> > right direction.
> >
> > Yesterday we had some major problems with our server, and raid array,
> > resulting in the loss of our SQL database backups and the need to
reformat
> > the hard drive.
> >
> > We have all the .mdf files for the databases.
> >
> > So, how the heck do we get them to work at this point, or without the
> > backups, are we hosed?
> >
> > Any help would be very much appreciated
> >
> > Steve
> >
> >
>|||I further modified the code as follows (think this may be the correct
version):
EXEC sp_detach_db @.dbname = 'hrbcoSQL'
EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL',
@.physname = 'c:\sql\hrbcoSQL_Data.MDF'
Error I am getting now is:
Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'hrbcoSQL' because it is currently in use.
Server: Msg 1801, Level 16, State 3, Line 1
Database 'hrbcoSQL' already exists.
Please...anyone?
Steve
"Steve G" <steve@.nospam.tnccreations.com> wrote in message
news:O4prCAwSDHA.2020@.TK2MSFTNGP11.phx.gbl...
> Well that didn't work...however, after modifying the code thusly:
> EXEC sp_detach_db @.dbname = 'hrbcoSQL'
> EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> I got this error:
> Server: Msg 3701, Level 16, State 1, Line 1
> Cannot detach the database 'hrbcoSQL' because it is currently in use.
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
> I am beginning to think we may be hosed. :((
> Steve
> "Steve G" <steve@.nospam.tnccreations.com> wrote in message
> news:Ofj986vSDHA.3144@.tk2msftngp13.phx.gbl...
> > I think I need some additional help here :((
> >
> > If I use:
> > EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> > @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> >
> > (the path specified is where the mdf file is stored currently)
> >
> > I get this error:
> > Server: Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is aborted.
> > Device activation error. The physical file name
> > 'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
> >
> > The path to the ldf file would represent the old virtual directory where
> the
> > file was stored previously.
> >
> > This seems to contradict what the Book says about SQL creating a new log
> > file.
> >
> > So, should I recreate the database first, then run this code?
> > EXEC sp_detach_db @.dbname = 'hrbcoSQL_Data'
> > EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> > @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> >
> > Copied from the book, then modified to reflect actual names/paths
> >
> > Steve
> >
> >
> > "Steve G" <steve@.nospam.tnccreations.com> wrote in message
> > news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> > > Hi All:
> > >
> > > SQL Server 2000
> > >
> > > We have a really huge problem that we need some advice on. I don't
know
> if
> > > this is the correct group or not, but hopefully someone can point us
in
> > the
> > > right direction.
> > >
> > > Yesterday we had some major problems with our server, and raid array,
> > > resulting in the loss of our SQL database backups and the need to
> reformat
> > > the hard drive.
> > >
> > > We have all the .mdf files for the databases.
> > >
> > > So, how the heck do we get them to work at this point, or without the
> > > backups, are we hosed?
> > >
> > > Any help would be very much appreciated
> > >
> > > Steve
> > >
> > >
> >
> >
>|||One more attempt:
I set the database to be offline, then ran the below code and got this
error:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'hrbcoSQL'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
So instead of SQL creating a new ldf file, it appears it is still looking
for the old one that use to be stored on what is now a non-existent virtual
drive/directory.
Steve
"Steve G" <steve@.nospam.tnccreations.com> wrote in message
news:Orf5jCwSDHA.2020@.TK2MSFTNGP11.phx.gbl...
> I further modified the code as follows (think this may be the correct
> version):
> EXEC sp_detach_db @.dbname = 'hrbcoSQL'
> EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL',
> @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> Error I am getting now is:
> Server: Msg 3701, Level 16, State 1, Line 1
> Cannot detach the database 'hrbcoSQL' because it is currently in use.
> Server: Msg 1801, Level 16, State 3, Line 1
> Database 'hrbcoSQL' already exists.
> Please...anyone?
> Steve
> "Steve G" <steve@.nospam.tnccreations.com> wrote in message
> news:O4prCAwSDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > Well that didn't work...however, after modifying the code thusly:
> > EXEC sp_detach_db @.dbname = 'hrbcoSQL'
> > EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> > @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> >
> > I got this error:
> > Server: Msg 3701, Level 16, State 1, Line 1
> > Cannot detach the database 'hrbcoSQL' because it is currently in use.
> > Server: Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is aborted.
> > Device activation error. The physical file name
> > 'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
> >
> > I am beginning to think we may be hosed. :((
> >
> > Steve
> >
> > "Steve G" <steve@.nospam.tnccreations.com> wrote in message
> > news:Ofj986vSDHA.3144@.tk2msftngp13.phx.gbl...
> > > I think I need some additional help here :((
> > >
> > > If I use:
> > > EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> > > @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> > >
> > > (the path specified is where the mdf file is stored currently)
> > >
> > > I get this error:
> > > Server: Msg 1813, Level 16, State 2, Line 1
> > > Could not open new database 'hrbcoSQL_Data'. CREATE DATABASE is
aborted.
> > > Device activation error. The physical file name
> > > 'E:\MSSQL2000\Logfiles\hrbcoSQL_Log.LDF' may be incorrect.
> > >
> > > The path to the ldf file would represent the old virtual directory
where
> > the
> > > file was stored previously.
> > >
> > > This seems to contradict what the Book says about SQL creating a new
log
> > > file.
> > >
> > > So, should I recreate the database first, then run this code?
> > > EXEC sp_detach_db @.dbname = 'hrbcoSQL_Data'
> > > EXEC sp_attach_single_file_db @.dbname = 'hrbcoSQL_Data',
> > > @.physname = 'c:\sql\hrbcoSQL_Data.MDF'
> > >
> > > Copied from the book, then modified to reflect actual names/paths
> > >
> > > Steve
> > >
> > >
> > > "Steve G" <steve@.nospam.tnccreations.com> wrote in message
> > > news:Oo2jE5uSDHA.2128@.TK2MSFTNGP12.phx.gbl...
> > > > Hi All:
> > > >
> > > > SQL Server 2000
> > > >
> > > > We have a really huge problem that we need some advice on. I don't
> know
> > if
> > > > this is the correct group or not, but hopefully someone can point us
> in
> > > the
> > > > right direction.
> > > >
> > > > Yesterday we had some major problems with our server, and raid
array,
> > > > resulting in the loss of our SQL database backups and the need to
> > reformat
> > > > the hard drive.
> > > >
> > > > We have all the .mdf files for the databases.
> > > >
> > > > So, how the heck do we get them to work at this point, or without
the
> > > > backups, are we hosed?
> > > >
> > > > Any help would be very much appreciated
> > > >
> > > > Steve
> > > >
> > > >
> > >
> > >
> >
> >
>