August 11, 2009 at 7:56 pm
Hi there, we need to migrate sql server to a larger hdd because of lack of disk space and unable to do proper sql backups and log backups. Problem is I have only been in this job for 1 mnth and don't know much about the company's applications and specific configurations, and there is no documentation either (the people who knew this stuff have already left the co).
I was just wondering if it was possible to backup all the files and directories on the current disk using normal network backup, including all the mdf and ldf files (after sql server has been stopped), we also have Symantec SQL Agent backup if needed, then add the extra disk space and restore all the files and directories back into the same structure as before. Would SQL Server then work? I'm trying to avoid re-installing SQL Server as their configuration is so specific I dont' want to miss any finer details and find the system doesn't work.
August 11, 2009 at 8:33 pm
Well, I am not sure that I would approach it that way. It all depends upon how you are going to get that new storage available. Is the new storage being presented from a SAN? Or, are you swapping out old smaller drives for new larger drives?
If this is going to be new presented storage from a SAN, then you could present the storage as a new volume, create the partition and assign an unused drive letter. Take SQL Server down and copy all files over to the new drive, dismount the old drive and reassign the new drive to the old drive.
As long as SQL Server is down while you are moving files you should be okay and SQL Server will start back up with no problems as long as the new storage has the same drive letter, folder structure and files (database files).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 11, 2009 at 8:46 pm
Thanks for your speedy response Jeffrey.
The new space is just added disk drives, so currently sql server is on D drive, but that will be blown away after the full backup, new disks added, then reconfigured as D drive again. So essentially it should be all the same, just with larger disk space.
August 11, 2009 at 8:59 pm
If this is new drives - I would do the following:
1) Shut down all services (SQL Server, etc...), change to Manual startup type
2) Change drive letter on D: to X: (or anything else)
3) Restart the server - need to do this to release the D: drive
4) Add new drives and configure as D: drive
5) XCOPY/Robocopy everything on X: to new D:
6) Restart SQL Server - change back to Automatic startup type
Validate the system - once everything is validated you can then remove to old X: drive.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 12, 2009 at 6:46 am
Correct me if i have misunderstood the whole thing. What if you move the database files only to your new disks. You can use attach and detach for this. You can then configure your backups to be taken to the new drives.
"Keep Trying"
August 12, 2009 at 7:58 am
I would do the same as Chirag suggests, database by database, I'd detach the mdf/ldf, physically move them to the new drive, then reattach;
after that , i would review the backup jobs, exactly like Chirag again, to use the new drive as well.
Lowell
August 12, 2009 at 8:11 am
Are you needing the physical disk slots in the current server to upgrade the disk capacity for D? Ie, you have 5 physical slots with say 18Gb disks in and you want to replace them all with 72Gb disks?
If that's the case, what I'd do is twist the fingers of the bean-counters until they allowed an additional disk rack to be bought. I'd fill that with large disks and use the small disk array that you currently have for the log file disk. This means you can carefully move one db at a time with minimal downtime and without the potential for a monumental disaster with huge gnashing of teeth, wailing and losing of data.
Above all, no matter what you do: TAKE A BACKUP. CHECK THE BACKUP. TEST THE BACKUP.
August 12, 2009 at 8:20 am
To get some sound advice could you share some info on how your current disk are configured?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 12, 2009 at 7:02 pm
FNS (8/12/2009)
Are you needing the physical disk slots in the current server to upgrade the disk capacity for D? Ie, you have 5 physical slots with say 18Gb disks in and you want to replace them all with 72Gb disks?If that's the case, what I'd do is twist the fingers of the bean-counters until they allowed an additional disk rack to be bought. I'd fill that with large disks and use the small disk array that you currently have for the log file disk. This means you can carefully move one db at a time with minimal downtime and without the potential for a monumental disaster with huge gnashing of teeth, wailing and losing of data.
Above all, no matter what you do: TAKE A BACKUP. CHECK THE BACKUP. TEST THE BACKUP.
Yes that's exactly what we're doing - getting rid of current disks and replacing with larger ones within a RAID5 config. No can do re: buying additional rack due to budget constraints.
August 12, 2009 at 11:10 pm
Above all, no matter what you do: TAKE A BACKUP. CHECK THE BACKUP. TEST THE BACKUP.
I agree. If you do not have disk space then backup to a tape.
"Keep Trying"
August 13, 2009 at 9:50 am
Maria Smith (8/11/2009)
Hi there, we need to migrate sql server to a larger hdd because of lack of disk space and unable to do proper sql backups and log backups. Problem is I have only been in this job for 1 mnth and don't know much about the company's applications and specific configurations, and there is no documentation either (the people who knew this stuff have already left the co).I was just wondering if it was possible to backup all the files and directories on the current disk using normal network backup, including all the mdf and ldf files (after sql server has been stopped), we also have Symantec SQL Agent backup if needed, then add the extra disk space and restore all the files and directories back into the same structure as before. Would SQL Server then work? I'm trying to avoid re-installing SQL Server as their configuration is so specific I dont' want to miss any finer details and find the system doesn't work.
Hi,
Let me describe my scenario ..
I had SQL server installed on c: drive as well as user databases were on C: drive (this was done before I joined the company). There was a geo database application that was loading the data into SQL server. The problem was that the C; drive was short of space.:w00t:
To overcome this, I had to request to add more disk space to the server. For that, I shut down the SQL server service. The disk space was added and then SQL server service was started. Then I moved the databases to the disk where there was plenty of space for mdf and ldf (better be on separate drives) follow http://sqlquest.blogspot.com/[/url] for Moving database from one location to another.
Hope this helps,
\\K 🙂
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
August 13, 2009 at 7:31 pm
Thanks for all the replies so far
What I want to know is if the current D drive is blown away (after full backup has completed and been verified) ,the new larger disks added and made into D drive again, then the backup restored including SQL Server folders does this mean the SQL installation will still be ok?
I'm not actually moving the db files as I don't have anywhere to move them to. They will be backed up and also use Symantec SQL Agent to back them up as well. So when the new drives are connected and formatted as the new D drive, the db files will have to be restored first, then probably re-attached?? Should I detach them first before the starting the backup?
I've never done this before so I'm looking for feedback from anyone who has had experience in doing it this way.
TIA.
August 13, 2009 at 10:30 pm
Maria Smith (8/13/2009)
Thanks for all the replies so farWhat I want to know is if the current D drive is blown away (after full backup has completed and been verified) ,the new larger disks added and made into D drive again, then the backup restored including SQL Server folders does this mean the SQL installation will still be ok?
I'm not actually moving the db files as I don't have anywhere to move them to. They will be backed up and also use Symantec SQL Agent to back them up as well. So when the new drives are connected and formatted as the new D drive, the db files will have to be restored first, then probably re-attached?? Should I detach them first before the starting the backup?
I've never done this before so I'm looking for feedback from anyone who has had experience in doing it this way.
TIA.
Okay - swapping out old drives for new drives.
Before you backup anything on the current drive, you are going to want to shut down all services that could have any file open on that drive. I cannot say if there are other services that have open files - I don't know what else you have on the server and what folders/files are there.
For SQL Server, if you shut down SQL Server and set the service startup to manual - when you try to start it after the new drive has been restored, SQL Server should start up with no problems. That is, if all you have on that drive is the database files (mdf, ndf, ldf) and not the binaries. If you have the binaries - I don't know if that would work - it should, I just don't know for sure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 16, 2009 at 4:25 pm
Jeffrey Williams (8/13/2009)
For SQL Server, if you shut down SQL Server and set the service startup to manual - when you try to start it after the new drive has been restored, SQL Server should start up with no problems. That is, if all you have on that drive is the database files (mdf, ndf, ldf) and not the binaries. If you have the binaries - I don't know if that would work - it should, I just don't know for sure.
Binaries are on the C drive which is not being touched so I'm hoping it should be fine. Doesn't mean I won't have a contingency plan though, it's just that the former is a lot quicker and potentially pain free - if it works.
Thanks once again 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply