1

We have a new SQL server (Dell PowerEdge R730xd). It has a RAID 5 for the OS and the database files for SQL, and also has two SSDs. It is running Windows Server 2012 R2 Standard and SQL Server 2014 Standard.

One of the SSD drives holds the log files for the SQL databases. The other holds the backup folders and files for the same.

We only have one instance.

One of the SSDs is the subject of troubleshooting, so we need to remove it and replace it with the replacement SSD.

The drive to replace is the drive that holds the SQL backup files.

What steps do I need to take to switch out the SSD with a new one so there is no corruption, lost data, etc?

I have been researching this and I think that I should be able to shut down the SQL services, copy over the backup folders to another drive temporarily, switch the SSDs (they are supposed to be hot swappable, but I may need to reboot if they don't show), copy the backup folders back over to the new SSD, start the SQL services and everything should work fine.

Is that correct? Do I need to take any other steps, like detaching databases or something? I am extremely new to SQL Server. Advice from an experienced DBA would be greatly appreciated.

Also, would these steps be the same if I needed to switch out the other SSD that currently holds the LDF files?

I did do some Google searches, but all I found were topics on how to move the backup folder location to a different drive. I have not done that before. I could change the backup files location in SQL Server Management Studio 2014 to a different drive, then go about switching SSDs, then change the location back, but I am not sure if there are "gotcha" steps that I would need to be aware of in that process, like checking for changing Windows permissions, disabling SQL service accounts, etc.

Also, I am asking because I am not sure if it is simple as it seems. I could just move the backup folders and files, but I am not sure what steps I need to take to shut down/disconnect/detach the SQL databases properly.

2
  • Why did you choose to put database log files and backups on SSDs? Typically you'd be better off having data files there. Also, it's not the best idea to have your backups on the local server... Jan 3 '17 at 2:30
  • We had this Dell server and the SQL Server install set up for us by a contractor. Thank you, this will be helpful for future discussions and analysis regarding our SQL installation. We also have a backup server (Dell RapidRecovery) that backs up all of servers, so this server and its data are being backed up elsewhere.
    – Disco
    Jan 3 '17 at 2:44
1

For backup folder :

If you mean the default backup folder as configurable in server properties --> database settings

enter image description here

You can easily change that location to the one you want. If your SSD has only the backup files, then it should be ok to hot swap when your backups are not running. Just change the default location to the new one in the above stated location. A restart of sql server service may be required for the new values to get reflected.

A more easy and managable approach will be to explicitely specify the backup location in your backup scripts (or just use Ola's backup solution).

For LDF (or MDF) files :

You need to use alter database .. modify file to first modify the location in the system tables.

Then offline the database/s and then physically copy the LDF (or MDF) to the new location.

Once above is done, bring the database online.

See this answer for more detail steps by Mike Fal.

1
  • Awesome. Thank you very much, this was just what I was looking for! I was able to complete my troubleshooting process with your help regarding moving the backup files (the new SSD is also working great, so far). Thanks again. :)
    – Disco
    Jan 3 '17 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.