Backup SQL Database on another drive using SSMS

I came across a problem where I has two hard drives in my PC where my C drive was used to install Microsoft Windows and Microsoft SQL Server. My C Drive was full and I had to make backups of one of my SQL Databases and found that I didn't have enough space available to complete the backup. When I tried to make a backup using Microsoft SQL Server Management Studio (SSMS), only my C drive was available to make backups on but I had another hard drive called D in my PC with enough space. I found the following solution that worked for me.

Did you know 11% of people are left handed.

Before we start with the before and after, what is SSMS? SQL Server Management Studio (SSMS) is a tool included with Microsoft SQL Server for accessing, configuring, managing, administering and developing all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server.

This is how my view looked like before the change. I've opened Microsoft SQL Server Management Studio (SSMS), right clicked on my database that I want to backup and clicked on Back Up under the Tasks menu item:

Then I've set my back up to Disk under the Destination section and click on Add:

As you can see I only have my C drive available to backup to. Now to add drive D to the list of drives. firstly you need to enable xp_cmdshell command because it is by default disabled. To enable the xp_cmdshell command, use the sp_configure command:

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO

RECONFIGURE
GO

Now that command xp_cmdshell is enabled, you can setup drive D using the following command:

EXEC XP_CMDSHELL 'net use D:'

Now, close your Microsoft SQL Server Management Studio (SSMS), reopen SSMS and follow the same steps as above to backup your database and you will see the following backup locations:

Now I have the D drive available to use to backups my databases on.

Did you know African elephants only have 4 teeth.

Tuesday, August 15, 2017

Database , SQL , SSMS