Author's Note: I received an interesting question from a reader about backup and restore and thought I'd share the information. The Jump Start column will be back in the next edition of the newsletter.

Q: I tried to back up my database by using T-SQL commands. However, when I entered the following code to back up my database to a non-default directory, the process failed.

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup', 'C:\testing.Bak'
BACKUP DATABASE testDB TO testing_Backup

What should I do to back up and restore my database from a non- default directory?
-- Chau Cheng Chai

A: You raise an interesting question, Chau Cheng Chai. SQL Server 2005 Express is not restricted to backing up to just the default directory; you can back up to other directories as well. You didn't send me the error message you received while attempting the backup operation, but I can tell you that the two most likely causes of backup and restore errors usually pertain to the database or file-system permissions or locations. In your code, you attempted to back up your database to the root directory. The sp_addumpdevice can't use the root directory as a backup target. Instead, you should create another directory, for example c:\sqlbackup. Try the following code:

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'c:\sqlbackup\testing.bak'
BACKUP DATABASE testDB TO testing_Backup

Hope this helps, and as always it's great to hear from our readers.
-- Michael Otey, mikeo@windowsitpro.com