August 31, 2009 at 12:17 pm
Hi,
We need to move the physical SQL 2005 files to different locations. In testing - using SQL MGMT Studio I
Took the database off line
Detached the database
Used Windows Explorer to Move the MDF and LDF to a new location
Attached the database - selected to ADD both MDF and LDF and picked them from their new location.
Everything looked great. I could query the DB and click through the DB Objects in Mgmnt Studio.
However, although the application (Web based app that we didn't write) was completely restarted would not run. Not much info either. Basic error 500.
I've written many applications and all of my apps simply connect to the SQL Server and the internal database. None of my apps cared where the physical files were stored.
Can anyone shed some light on this from a SQL Server perspective. Is there something else that I forgot to do? I was even able to go into SSRS and design a quick report off of this "moved" database. No problems. I'm wondering if the APP may have another problem and it just seems like this moved caused it. Too coincidental ?
Thanks
August 31, 2009 at 12:20 pm
it does sound a bit conincidental, and like you say the database move appears to be successful.
A couple of things i would try.
1) is there an application log - or do you have access to the source so you could maybe step through and find where its failing?
2) run profiler and see if a connection is even being made. At least then you know whether or not its seeing the database before failing
August 31, 2009 at 3:43 pm
DoubleEx
Check the login and privilege details of the login that the web application is using.
Try to check is the Users and Login are synced in the new server and currently attached database or you can use the below SP
sp_change_users_login
Search more about it on book online.
Thanks
Raj
August 31, 2009 at 4:21 pm
Try to login to the database using SSMS with the webuser and see if that works.
If you are not able to get in, check to see if the user might have become orphaned during the move process.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 31, 2009 at 4:42 pm
detaching\attaching the database can cause it to end up with a different dbid. This can affect the value of default database for the logins, so thats worth a check.
Alter database modify file is a better way to move database files for this reason (plus maintaining database owner settings)
---------------------------------------------------------------------
August 31, 2009 at 5:48 pm
Thanks everybody. Nothing is ever as straight-forward as it seems. I thought this might be easier since I wasn't moving this DB to another server - just moving it's physical files.
FYI - Moved the files back and although everything seems find (still) from SQL Management Studio and SSRS point of view - the application still fails. We will be calling the vendor since we don't have any access to the source code.
Trace is a good idea - I'll experiment with that a bit.
Alter Database - sounds like something I should have done http://www.sqlservercentral.com/articles/Administration/65896/
Unfortunately when you Google "moving physical SQL MDF file" everything (including MSDN) shows you how to do exactly what I did.
I'm going to have to get the original working before I can try again.
Thanks again for the tips
August 31, 2009 at 8:20 pm
Sounds to me as though you followed the correct steps ... there's not much to it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply