September 6, 2013 at 1:20 am
SQL Service was down, our storage engineer created a new LUN and then moved the log file to the new location ...
Database is therefore unaccessible ...
after running a Alter database script to point the log file location, The only way to get the DB online is to restart SQL Service??? this will in effect take the whole instance DB's down ...
Is there another way to just get that 1 DB online???
I know the sequence of events is not exactly best practice but is there a way around this scenario???
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 6, 2013 at 1:31 am
Feeg (9/6/2013)
The only way to get the DB online is to restart SQL Service???
No. If its a user database. But if you are moving system databases such as master,model it requires a service restart.
September 6, 2013 at 1:34 am
Using ALTER DATABASE to move a user database file doesn't require a service restart.
Run the ALTER DATABASE
Take that DB offline
Go to the file system
Move the database's files to the new location
Bring the DB back online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 2:14 am
Hi guys thanks for the prompt response but I didn't explain clearly what happened ...
My bad ...
The storage engineer stopped the SQL service ...
created the new LUN ...
he physically moved the user database log file to the new LUN ...
brought the services backup and the DB was inaccessible (as expected to me)
he came to me and explained that I must bring the DB backup and gave me the new location
I ran the Alter db script to point to the new location ..
db still inaccessible, could detach, bring offline of course ...
message said db file location altered and need to restart service to get db online :blink:
To fix it I had to restart the SQL service ...
The above sequence of events is clearly not correct and I know this ...
My question is ... if the above happens ... is there a way to get the DB online without restarting the SQL service?
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 6, 2013 at 2:21 am
You did not need to take the instance down.
After running the alter database, take the database offline, ensure the files really are in the correct place and that SQL has permissions, bring the DB back online. Done. Only TempDB needs an instance restart to change the file locations.
Now, if it was a new drive entirely and SQL was clustered then you would need (I believe) to take the entire SQL resource group offline to set the dependencies, but that's just for clustered SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2013 at 2:22 am
Feeg (9/6/2013)
Hi guys thanks for the prompt response but I didn't explain clearly what happened ...My bad ...
The storage engineer stopped the SQL service ...
created the new LUN ...
he physically moved the user database log file to the new LUN ...
brought the services backup and the DB was inaccessible (as expected to me)
he came to me and explained that I must bring the DB backup and gave me the new location
I ran the Alter db script to point to the new location ..
db still inaccessible, could detach, bring offline of course ...
At this point you had to bring the database online again (or attach if database was detached).
That should work and if it failed you need to see the error log as to why it failed. Maybe a typo in the new file location.
message said db file location altered and need to restart service to get db online :blink:
To fix it I had to restart the SQL service ...
The above sequence of events is clearly not correct and I know this ...
My question is ... if the above happens ... is there a way to get the DB online without restarting the SQL service?
September 6, 2013 at 2:43 am
Thanks for advice , will try to avoid this but will test on a DEV DUMP db I have. Fortunately this was on QA and not PROD ... :hehe:
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
September 19, 2013 at 7:21 am
In Case someone needs info ...
Moving SQL_TEST datafile from 'E:\MSSQLData\SQL_TEST.ndf' to 'K:\MSSQLData\SQL_TEST.ndf'
USE master;
GO
ALTER DATABASE SQL_TEST SET OFFLINE;
GO
ALTER DATABASE SQL_TEST
MODIFY FILE (NAME = SQL_TEST, FILENAME = 'K:\MSSQLData\SQL_TEST.ndf');
GO
--Move the file to the new K: location
ALTER DATABASE SQL_TEST SET ONLINE;
GO
DONE
In my case in the Original Post I could of as Gail suggested ... take the db offline (it was in an unavailable state) and then make sure the file was in the correct location by running the Alter databae modify file script as above and then simply bring thr db online again instead of what I did (restart the sql instance :hehe:)
Similar to a detach, move and attach db with new file locations just another way ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply