June 1, 2011 at 5:56 am
I did some searching but all I came up with was restoring to a new server and such...
Anyway, I have an older 2005 instance that was installed on the OS C: drive which is now running out of space. The engineers have installed a new drive on the server for me to move the instance to. I know how to put the user database into single user mode killing all connections and then detach/reattach it making sure to keep the DBO account the same however this only moves the user database. In the event the C: drive fills up won't this present issues with the system dbs that are still on the C: drive? What all has to be moved to constitute an entire instance moving? User + System dbs?
Or
Do I need to do a fresh install on SQL Server to the new drive and restore to it?
Thanks!
June 1, 2011 at 6:03 am
Why move the whole thing?
Just move the user databases to the new drive.
Dettach all dbs.
COPY to the new drive.
Attach at new location.
Run check db to ensure you have the dbs back online.
Delete the old files after a few hours running in prod (I'd burn 'em to dvd just in case...).
June 1, 2011 at 6:50 am
moving system databases
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx
You may get away with just moving tempdb (much simpler) - it depends how tight your space is.
As for the user databases I much prefer using alter database...modify file to detach\attach.
Whichever way you go back the databases up first.
---------------------------------------------------------------------
June 1, 2011 at 12:13 pm
Ninja's_RGR'us (6/1/2011)
Just move the user databases to the new drive.
quote]
I'm not sure I understand you... I could be mistaken but wouldn't I want to move the system dbs off the C: drive so they don't grow and fill up C: ? Or do they not grow the way a user db does?
June 1, 2011 at 2:01 pm
Vertigo44 (6/1/2011)
Ninja's_RGR'us (6/1/2011)
Why move the whole thing?Just move the user databases to the new drive.
quote]
I'm not sure I understand you... I could be mistaken but wouldn't I want to move the system dbs off the C: drive so they don't grow and fill up C: ? Or do they not grow the way a user db does?
Only msdb can fill up the drive. See how big it is now. Model won't change unless you do it manually or by accident. Master holds the logins and linked servers. So unless you have millions of those it can't fill up the drive either.
msdb can be kept in check if you delete the job history. I've never seen msdb over 500 mb at my clients and I keep a very long history. So again 500 mb is not supposed to be an issue.
I find it much simpler to just move a couple dbs rather than rebuild the whole server.
June 1, 2011 at 2:20 pm
tempdb is a system database and that has the potential to grow and fill a drive, it just takes one bad query.
As I said it depends on how tight space is on the C drive.
Moving the other system databases is doable but I would agree only do it if you really have to.
---------------------------------------------------------------------
June 2, 2011 at 6:51 am
Good point on TempDB. Can I move tempdb just like I did the User db or is it more involved?
June 2, 2011 at 7:05 am
its very similar but you have to restart SQL for it to take effect.
Exact process is documented in the link I provided above.
---------------------------------------------------------------------
June 3, 2011 at 1:16 am
When moving user databases you dont need to detach them at all.
Issue the alter database modify file statements which will set the new paths. Take each database offline in turn, move the disk file to it's new location and then bring the database back online, couldn't be easier
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply