March 21, 2006 at 3:01 am
Hello Kind Sirs and Ma'ams,
I would like to get your expert advice on a plan that we would need to execute regarding our SQL Server and its databases. Here's the scenario...
We would be adding a new hard disk (drive O) on the Server. Currently, SQL and all its files are saved on drive N. What we would be doing is to rename drive N to drive P (basically to signify it as a temp drive) then rename drive O to drive N. Then eventually renaming drive P back to drive O.
With this being said, we would assume that SQL Server should be re-installed since drive O does not hold the SQL data files plus its data and it was just renamed to drive N. We also assume that copying the SQL Server files (all data and code folder) would not do the trick as well.
What would be the best solution for this? If we are to reinstall SQL in drive O, could we then just re-attach the databases? (We will be making backups of our databases as well as the data and log files). Or do we resintall SQL, recreate the databases then restore the backups that we have made?
Any suggestions or opinions would be greatly appreciated! thank you so much in advance and more power to all!
March 21, 2006 at 4:31 am
Hi Timber, my approach would be install SQL Server on your new drive. Before you attach your user databases using sp_attach_db, you should consider also 'restoring' your msdb database. This would save you having to recreate all your jobs again. Essentially your approach looks good to me. You'd have been away on a hack if you did not have to reinstall SQL Server. Attach databases is quick and straightforward. But you would need to restore the msdb if you wanted to. Rgds Derek.
March 21, 2006 at 5:43 pm
March 21, 2006 at 11:20 pm
hi,
what about master database ? if having so many roles, users created . I feel to take a back up of master database and restore on the new one..
March 22, 2006 at 8:49 am
As I interpret it, you want to move all SQL files from your current N drive to your O drive and then swap drive letters.
I have done this before, although it does require taking SQL server down (but no reboot unless you have a swap file on your N drive).
There is no need to reinstall. There is no need to detatch / attach. Master file is unchanged.
Stop SQL Server / SQLServer Agent. This closes all the database files including Master and MSDB. If there are other services holding files open on the N: drive, stop them as well
Copy all the files from your N drive to your O drive using either Xcopy at a command prompt or Windows Explorer. Be sure to maintain the same exact folder structure on the new drive.
Use disk manager to change your N drive to something else. Change your O drive to N
Restart SQL Server & SQL Server agent. It shouldn't even notice the difference (other than not running out of disk space, if that was your issue).
If your swap file is on the N drive, you will need to move it FIRST, as you can't rename the partition containing your swap file. This will require a server reboot. First, move your swap file to a partition that isn't being renamed, removing your N swap file. Reboot the server. Do your file moves for SQL, rename the drives. Once SQL is working, recreate the swap file on the new drive.
March 28, 2006 at 3:32 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply