SQL Server Database on External Hard Disk Drive – Setup Guide

databasehard drivesql server

Due to some security problems, My boss has asked me to store all sensitive data in external/removable storages like USB stick or external HDD and this specially includes the MDF/NDF/LDF files of SQL Server 2008 we're running.

I've been reading for these last three days with no luck to find a solution. Is there any solution at all? Has ever anybody done such thing?

Best Answer

Well, now I know the answer to my question!

According to http://support.microsoft.com/kb/304261, you may use SAN/NAS storage to store your databases' files using a TraceON flag. Something like:

DBCC TraceOn(1807);
GO

this command flags the 1807 TranceOn to let you use a UNC like "\Server-name\Path-to-Database-File.mdf" as the path to database files. Now you need to Create a folder on your removable USB HDD and use "Sharing and Security" to grant Full Control over that folder to "Network Service" or whatever user your SQL uses to interact with your Windows. Remember to remove everyone and Add Administrators too.

Now you're done; create the database and have fun!

DBCC TraceOn (1807);
Go

Use master;
GO

CREATE DATABASE [test001] 
    ON  PRIMARY (
        NAME = N'test001', 
        FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001.mdf' , 
        SIZE = 2048KB , 
        FILEGROWTH = 1024KB
        )
    LOG ON ( 
        NAME = N'test001_log', 
        FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001_log.ldf' , 
        SIZE = 1024KB , 
        FILEGROWTH = 10%
    )
GO
Related Topic