Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Moved a SQL SERVER 2005 log file, causing the database to fail on startup

Posted on 2009-02-08
8
Medium Priority
?
259 Views
Last Modified: 2012-05-06
I have a 2005 SQL Server database running on Windows 2003 Server.  I wanted to separate the log file from the mdb file, and thought I could move it and repoint it.  When I wasn't able to access it afterwards, I put the log file back to it's original location, with the mdb file, but it still does not start now.  Does it need a Alter statement to kickstart it?  Any help is much appreciated.  Thanks
0
Comment
Question by:Damian_Gardner
  • 5
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23585997
how exactly did you "move" the file?
did you stop sql server during that?
or did you use the sp_detach_db and sp_attach_db procedures ?
0
 

Author Comment

by:Damian_Gardner
ID: 23586013
I stopped the entire SQL system, went to windows explorer to cut and paste the log file onto a different physical drive location, then started the SQL system back up.  I was expecteing to be able to change the properties on the DB to change the path of the file, but it's blocking me out now, and failing to start.  I moved the file back into place, but no luck.  
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23586036
> I was expecteing to be able to change the properties on the DB to change the path of the file,
how do you expected sql server to "find" the new location of the db file?
note: the actual location is stored in the master db, except for the location of the master db itself.

now, which db file(s) did you move? the ones of master itself?

I really hope you did not do this on a production system... these things are to be tested first on a dummy system first....
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Damian_Gardner
ID: 23586050
This is a test USER database.  With the file back in place, can an ALTER stmt be issued to bring it online possibly?
0
 

Author Comment

by:Damian_Gardner
ID: 23586053
I also have a backup of the database from Friday.
0
 

Author Comment

by:Damian_Gardner
ID: 23586075
Well - I just issued an sp_attach_db command, reaffirming the location of the mdf and ldf files, and it's back online.  I think the step I missed was detaching the db first. Do you agree?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 23586084
yes. always properly detach the db.
0
 

Author Closing Comment

by:Damian_Gardner
ID: 31544325
Ok - thanks for your help Angel.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

564 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