restore sql database from extension with mdf and ldf files 1

[nimsam]

I have NT Server 4.0 O/S and Sql Server 7.0. I have droped one of my databse from sql server by mistake. Luckly i have database files extension with mdf and ldf. May i know that how to restore database from data files extension with mdf and ldf. So, can anyone help me please? Thanks in advance

 

[MartinCroft]

Hi

you could use the stored procedure sp_attach_db

See below a statement which attached the bookshopDB database(used as an example)


EXEC sp_attach_db @dbname = N'BOOKSHOPDB',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\bookshop.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\bookshop.ldf'


I suggest looking up the proc in BOL (book online for more info)

Mart

 

[JayKusch]

If the above, best method, fails ... you may also try sp_attach_single_file_DB. You will have to rename your LDF first so that it will not be attached. This SP will create a new LDF for you. BUT as already mentioned ... mysticmart has the optimal way to go.

Thanks

J. Kusch

 

[mb22]

what about creating the same database on ANOTHER machine instead .... Can you restore from the .mdf and .ldf onto the new machine?

Suppose you want a bigger database size on the new machine ... can you create the new database first and then restore from the .mdf and .ldf? ... Newbie?

 

[JayKusch]

That method will work for creating the DB on another machine. When I say "Target" I mean another server.

If you want your DB to be bigger ... restore first then build it out to as large as you want by getting into the Properties tab of the DB and tweaking its size.

Thanks

J. Kusch

 

[JayKusch]

Sorry about that last post ... I thought I was answering a similair post. Yes you can create it on another server. All you need to do is copy the MDF and LDF to the Target server and then run the listed methods above to attach the DB.

Thanks

J. Kusch

 

[mb22]

thanks buddy! i will try that after I eat my lunch. You have made my day!