BACKUP

I need to start backing up my databases. They are almost past debug stage and about to be filled with data I will value. So far I have been copying the whole directory to an external HDD (USB connection) but I never restored anything from the backup. My databases are simple: No web connections, there is only one client: my C# application and most likely this will remain so for a long time. The databases may grow in size considerably, however.

I looked into BACKUP command. I have the following questions:

(1) What is wrong with simply doing it the way I've done it so far: copying a directory?
(2) I found a post by Glen WIlson at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=487447&SiteID=1 where he offers a C# code for a database backup. My next question is:
I could not find the class he is using: BackupDeviceItem in MSDN help for VS2005. How can I try the code if the class is nowhere to be found?

Thanks.

Hallo Alex,

BackupDeviceItem is in the Namespace Microsoft.SqlServer.Management.Smo

Maybe you must add a reference to Microsoft.SqlServer.Smo

For documatation about this class, have a look at MSDN2

Servus
Klaus

|||

Thank you very much, Klaus. It worked, however, I still cannot get the BackupDeviceItem class in my VS2006 management studio. It is just not there. I mean, it is now in the IntelliSense all right but not among the help items. According to Intellisense the class appears to be overloaded but naturally a single signature is given in the dropdown menu.

The link you've provided is helpful. I also found more info in MSDN by following the page references. Still cannot find a decent description of the class. There should be methods, I reckon. It is not a major problem, however, at this point.

I still want to get an answer to this question:

What is wrong with just copying a "Microsoft SQL Server" directory wholesale as a backup method?

Thanks.

|||OK, I have found all methods. That was a false alarm. Still I want to get an answer to my major question: can I just copy the SQL Server directory?|||

SQL Server keeps a lock on it's files while they're attached, you may get an error trying to copy them.

Additionally, if you copy the file while a transaction is in process, you'll get the file with an incomplete transaction. Take the cannonical example of moving $100 from Savings to Checking; the transaction consists of two parts, a withdrawl from Savings and a deposit to Checking. What happens if you make a copy of the file after the withdrawl but before the deposit? You database corrupts, so you put the copy back, but it only represents the withdrawl, so you just lost $100.

The whole point of a transactional database is to ensure that this doesn't happen. Blind file copy excludes this mechanism.

Mike

|||

"SQL Server keeps a lock on it's files while they're attached, you may get an error trying to copy them"

Thank you Mike. It has been taken care of. Before I copy I go to Task Manager and kill two exec files that will give me errors otherwise: sqlwriter.exe and sqlservr.exe. Then I copy directory with no problem.

My world is much too small for a corruption to occur while the files are being copied, I think. It is a quick process and I monitor it. USB-2 is a fast channel.

But in the future I will do classical backup. Now it is just too hectic.

Thanks.

|||

Hey Alex,

As long as you're releasing the lock, which you are, you should be find.

As alternative to killing the services arbitrarily, you could also just run a script to detach the database before you copy the files. This will close out any transactions before detaching and releases the locks. Then you can reattach. This would be the way to go if you're trying to automate things since it's scriptable.

Mike