Detach System Databases

Question

Post reply

Detach System Databases

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
December 17, 2014 at 10:49 am

#292904

All,
We are having an issue with the SAN being over provisioned for our drive that holds our data files (.mdf) denoted as the M:\ drive and a seperate drive that holds our tran log fils (.ldf) denoted as the L:\. The previous DBA also set up the system databases on the M:\, inlcuding both .mdf and .ldf files.
Our SAN administrator wants to reclaim this space on the Equallogic, by creating a new drive and assigning the M:\ and L:\ drive letters.
For the user databases, I know that I can detach the databases, copy the datafiles to a holding area until their work is done, copy the files back over and re-attach the databases.
My question is, how should this work for the system databases? I can't detach them. I am thinking nothing needs to be done. When the files are copied back over and service restarted, everything will be fine. Can someone confirm or deny this for me?
I've found this link: http://msdn.microsoft.com/en-us/library/ms345408(v=sql.110).aspx, but I don't believe that his meets what is going on.
Thanks for all the help!!!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 1

I feel that the order of operation here should be:

1. Create backups of all user databases and systems databases.

2. Detach all User databases

3. Copy all files in the M:\ and L:\ drive to their designated holding areas

4. Stop the SQL Server Service

5. Let SAN Admin create new appropriately sized drives, get rid of old drives, set up correct drive letters, etc

6. Copy all files to their respective drives

7. Start SQL Server Service

8. Re-attach databases

I believe this is the correct order in which to do things, feel free to suggest anything I am missing. And, I am not sure about the systems databases. I know within the SQL Config Manager, you can specify the Startup Parameters for the Master database and Error log. None of this will be changing.

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 2

One last thing, TempDB has been setup with a local drive on each node of this cluster, so the TempDB mdf and ldf, do not reside on the M:\ and L:\ drives.

Thanks Again for any help provided!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As long as, when the service restarts, the system database files have the exact same path and name and SQL has full permissions on files and folders, you should be fine.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chandleya Right there with Babe Points: 725 More actions · Answer 4

I'll also add that you -do not want- to change the path of master/msdb/model for future compatibility reasons. Moving tempdb is a non-issue; Pinal Dave has that script language readily available. Changing the path to master in particular can break patching efforts. Follow the instructions above and move the file and folder structures verbatim to another volume, then shell game the drive letters.

I generally use robocopy for these operations so that I can confidently replicate permissions.

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 5

Thank You both for the replies!!! I was under the impression that if file locations, structures, and permissions didn't change I would be good to go!!!

Thanks Again!!

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 6

So our SAN/database maintenance didn't go as planned. The plan was to backup and detach all user databases; then stop the SQL Server Service. Then copy all of the data in our M:\ (mdf files), the data in our L:\ (ldf files) to a temporary location so that new appropriately sized drives could be created under the new firmware of the SAN. When it got to the point where it wanted to copy the system databases, they wouldn't copy over even though the SQL Server Service was shut off and our passive node was shut down during this maintenance.

I thought maybe I needed to stop the Cluster Service in order to get the system databases copied over, when I stopped the Cluster service, the shared storage (Dell Equallogics) disappeared from Windows and I was unable to see any drives/files to copy over.

Needless to say, we reverted back and all was good. I have been trying to research what went wrong here, but am having trouble figuring it out. Can anyone advise me on what I missed or send to a website so that I can figure this out?

Thank You!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You said the files wouldn't copy, why wouldn't they copy?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 8

I'm not 100% for sure, but it was giving me, "there's another process using these files" and I could either try again, skip or cancel. It was almost as if the SQL Server Service really wasn't stopped or it was trying to fail over and couldn't due to the second node being turned off, so it was stuck in limbo.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

You checked what process was using the files?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 10

GBeezy (12/29/2014)
I'm not 100% for sure, but it was giving me, "there's another process using these files" and I could either try again, skip or cancel. It was almost as if the SQL Server Service really wasn't stopped or it was trying to fail over and couldn't due to the second node being turned off, so it was stuck in limbo.

Assuming SQL service is turned off, identify the process and kill it. You can use a free apps like Process Explorer or KillProcess to kill the stubborn pid. Once killed, you should be able to move the file(s).

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 11

I did not...I'm not sure I would no how to check that and I was more dumbfounded that they wouldn't copy as the service was stopped. How could you check what process had a hold of the master, model, and msdb databases?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Process Explorer (sysinternals) or their simpler tool "handle"

It could have been something as simple as an antivirus grabbing the files. It could have been that the cluster service noticed the SQL 'failure' and restarted the service. Hard to say.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 13

Good points on the cluster trying to restart the service as well as antivirus!! I will look into this sysinternals stuff. Thank You all for the replies!!!