Solved

Restore from MDF/LDF files

Posted on 2007-11-26
3
1,042 Views
Last Modified: 2011-01-24
I have root access to my W2K3 server with SQL Server 2005 installed on it. An unrelated melt-down caused me to use the hosting control panel to delete a domain. Deleting the domain also deleted the SQL Server database. I run Backup For Workgroups (with the open file add-on designed for SQL Server) and make nightly backups.

I can restore these files: foo.mdf, foo.ldf

Here is what I have done.
1) Stopped SQL Server
2) Copied the mdf and ldf files to the SQL directory
3) Restarted SQL Server

Q1) How can I confirm that the data is now restored? Do I run a query? If so, how do I go about it?

A web app is now giving me the error: "Cannot open database "foo" requested by the login. The login failed.
Login failed for user 'tester'."

I have created/deleted/re-created the tester user several times but it doesn't resolve the issue.

Q3) How do I resolve this issue?

0
Comment
Question by:djlurch
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 20354335
If you have the files as .mdf and .ldf that means you will have t0 re-attach these files. Restore database is only done on *.bak type of backed up files.

Follow these steps to attach a database:
http://msdn2.microsoft.com/en-us/library/ms190209.aspx

Cheers!!
0
 
LVL 1

Author Comment

by:djlurch
ID: 20354548
I followed those directions.

1) Detached the DB/ Stopped SQL Server
2) Deleted MDF/LDF files from Data directory
3) Restored MDF/LDF files from Backup source
4) Restarted SQL Server
5) Attached the MDF/LDF files

I can see the database in the Server Management Studio.

I'm not really any farther than I was.

Q1) How can I see a few rows of data
Q2) I deleted the associated user, re-added them, but it still gives me the Cannot open database error (because of the incorrect credentials).


0
 
LVL 1

Author Comment

by:djlurch
ID: 20354593
Grrr. I deleted/added the user via the hosting control panel and it is working now.

Full points to PSS.

Thanks for the link.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question