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, [email protected]
