May 14, 2010

Taking BackUp of SQL Server Database on a USB Drive

While carrying out a normal database BackUp operation, I thought of taking my database backup on a USB Drive too. So I opened up my SQL Server Management Studio > expanded ‘Server Objects’ > Right-clicked ‘Backup Devices’, and then clicked onNew Backup Device’. This is what I saw. The Tape option was grayed out.

image

Note: Microsoft says “Support for tape backup devices will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Here’s what I did to take the backup of my database on a USB. I used the sp_adddumpdevice to register the USB based tape device. What this stored proc does it adds a backup device to the sys.backup_devices catalog view. Once done, the device can then be referred to logically in BACKUP statements

QUERY

-- Add a Tape BackUp Device
USE master;
GO
EXEC
sp_addumpdevice 'tape', 'TpDumpOne', '\\.\Tp0';

-- Back Up DB to the newly create Tape
USE <YourDB>
GO
Backup Database
<YourDB>
TO TpDumpOne


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

Robert L Davis said...

Most USB drives appear as a regular disk drive. You can back up to it normally like any other drive. But if it doesn't this would be an interesting solution.