Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-11-25 : 12:27:39
|
I detached database (from SSMS GUI), moved the LDF file to a brand new, local, drive and re-Attached (from SSMS GUI)Before detaching I set the database to DBO OnlyTo Attach I did the following in SSMS:Selected Folder and then File (from E:\path\) for the MDF fileI got message that LDF file (on F:\path\) was not foundI then added LDF file (from H:\path\)and then pressed OKIt gave me an error - "file not found" type message.I tried again and it said create database failed ...The database was mounted, the LDF file was not found, a 4MB default LDF was created on the Data volume (not even on the original LDF drive / path), the database was set to MULTI_USER access.Not happy  Here's the bit from SQL Error Log:2010-11-25 06:39:34.55 spid54 Starting up database 'MyDatabase'.2010-11-25 06:39:34.58 spid54 Error: 17204, Severity: 16, State: 1.2010-11-25 06:39:34.58 spid54 FCB::Open failed: Could not open file H:\MSSQL\Logs\MyDatabase.ldf for file number 2. OS error: 5(Access is denied.).2010-11-25 06:39:34.61 spid54 Error: 5105, Severity: 16, State: 1.2010-11-25 06:39:34.61 spid54 A file activation error occurred. The physical file name 'H:\MSSQL\Logs\MyDatabase.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.2010-11-25 06:39:34.62 spid54 New log file 'E:\MSSQL\Data\MyDatabase_log.LDF' was created.2010-11-25 06:39:34.75 spid54 CHECKDB for database 'MyDatabase' finished without errors on 2010-11-23 07:46:44.040 (local time). This is an informational message only; no user action is required.2010-11-25 06:49:08.75 spid51 Setting database option OFFLINE to ON for database MyDatabase. "Access is denied"SQL had access to the file on E: before I moved it to H:Move command was: ROBOCOPY E:\MSSQL\Logs H:\MSSQL\Logs MyDatabase.ldf /MOVI'm pretty sure I had already restored a file to H:, so SQL should have had sufficient permission"Diagnose and correct additional errors, and retry the operation"Yeah, SSMS told me there was an error. It did NOT tell me that the database was actually created, attached, and running LIVE "New log file xxx was created"Wouldn't "Error" be better?"Setting database option OFFLINE to ON for database MyDatabase"Surprised this changed DBO ONLY to MULTI-USERMaybe I could have thrown away the LDF and retried with my original, but I figured transactions may have already happened (scheduled tasks etc.) - so I restored instead which wasted 40 minutes ...LDF file was carefully created to have minimal VLBs so a 4MB file on the wrong drive wasn't any help ...I never use DETACH / ATTACH so I didn't script it (dunno if that would have helped), I was aiming for the speed of a MOVE of the LDF file instead of a RESTORE, but it didn't work out that way.At least I had done a backup before Detaching, after having set the database to DBO to lock out other processes.I'll go back to not using DETACH again I think ... |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-28 : 22:43:33
|
| Should have set it to single user instead.Was the SQL Service account explicitly grsnted dbo on the database in question? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-29 : 02:20:21
|
| "Should have set it to single user instead."Noted for the future. Just didn't think / realise I needed to be that cautious!"Was the SQL Service account explicitly grsnted dbo on the database in question?"Very much doubt it. I can'#t think that I have ever granted SQL Service permissions to anything (explicitly I mean)I wish I#d scripted the action ... I'd be able to see / reproduce it easily. Too busy at the moment to make the time for the experiment. Having said that ... we have some more databases to move to clear the SAN drive ... |
|
|
|
|
|
|
|
|
Attach Database Failed