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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "Taking BackUp of SQL Server Database on a USB Drive"
  1. Robert L Davis said...
    June 11, 2011 at 11:30 AM

    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.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions