In SQL Server Management Studio, you will only see the local drives available to SQL Server database engine. In this article we will take a look at the approach on how to configure SQL Server to access shared folder to perform database backup or restore commands, even further the SQL Server datafiles and logfiles can be stored on to shared folders in Oracle VirtualBox.
Install Guest Additions
The first thing is to install guest additions if not yet as per following instructions.
Navagite to Devices->Optical Drive->Choose a disk file-> highlight “VboxGuestAdditions” file.
Then click Open.
Double-click on this CD drive to launch the Oracle VM VirtualBox guest additional setup.
Click Next and select the destination folder. It requires approx 1 MB space in the drive.
In the next step, it shows the components to install.
Reboot the VM to complete the guest additional feature for a VM.
Set up VM Shared Folders
VM macine -> Settings->Shared Folders->Add New Shared Folder(+)->Choose Folder Path.
Click OK.
Go to File Explorer, we can see the following expected network diagram.
Enable xp_cmdshell Command in SQL Server
xp_cmdshell is disabled by default. so we need use sp_configure command to enable it as shown below:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'xp_cmdshell',1 GO RECONFIGURE GO
Define the Share Drive for SQL
Now we need define the shared folder with xp_cmdshell command of SQL server as below:
EXEC XP_CMDSHELL 'net use Z: \VBOXSVR\mssql'
To verify the new drive, we can use the below command that will show you all files in that newly mapped drive:
EXEC XP_CMDSHELL 'Dir Z:'
To un-map the shared folder drive from SQL server by:
exec xp_cmdShell 'net use Z: /delete'
Test the Shared Folder Drive in SQL Server
Backup Database onto Shared Folder Drive from SSMS
Backup Database onto Shared Folder Drive by Running SQL Script
BACKUP DATABASE [AdventureWorks15] TO DISK = N'Z:\AdventureWorks15.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks15-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 25016 pages for database 'AdventureWorks15', file 'AdventureWorks15' on file 2. 100 percent processed. Processed 296 pages for database 'AdventureWorks15', file 'AdventureWorks15_log' on file 2. BACKUP DATABASE successfully processed 25312 pages in 8.847 seconds (22.352 MB/sec). Completion time: 2020-09-21T18:07:50.3413113+10:00
Create a Database onto Shared Folder Drive
CREATE DATABASE [TestDatabase] ON (NAME = N'TestDatabase', FILENAME = N'Z:\TestDatabase.mdf', SIZE = 512MB, FILEGROWTH = 64MB) LOG ON (NAME = N'TestDatabase_log', FILENAME = N'Z:\TestDatabase_log.ldf', SIZE = 256MB, FILEGROWTH = 32MB) GO
Commands completed successfully. Completion time: 2020-09-21T18:15:32.9791694+10:00
Restore Database onto Shared Folder Drive
Query the logical file names of the database from the previous backup.
RESTORE FILELISTONLY FROM DISK = 'Z:\AdventureWorks15.bak'
Restore the backup to a new database by using the logicalname column of the previous step.
RESTORE DATABASE AdventureWorks15New FROM DISK = 'Z:\AdventureWorks15.bak' WITH MOVE 'AdventureWorks15' TO 'Z:\AdventureWorks15New.mdf', MOVE 'AdventureWorks15_log' TO 'Z:\AdventureWorks15New_log.ldf'
Processed 25016 pages for database 'AdventureWorks15New', file 'AdventureWorks15' on file 1. Processed 294 pages for database 'AdventureWorks15New', file 'AdventureWorks15_log' on file 1. RESTORE DATABASE successfully processed 25310 pages in 5.330 seconds (37.097 MB/sec). Completion time: 2020-09-21T18:29:04.4634020+10:00
So all works as expected. The shared folder now can be used to store backups, and also datafiles/logfiles of a SQL server.
Make DBA Life Easy 