Solved

Restore from MDF/LDF files

Posted on 2007-11-26
3
1,038 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now