SQL Express Db and shadow copies

[cookie_monster]

When i run a windows backup on our WSUS box which is also running SQL express it lists the Db's as being backed up in the event log and the Db appears on the tape, i haven't tried to restore one yet but i wondered if windows backup with shadow copies can now backup a SQL Express Db or is SQL creating a backup file somewhere else that is then backed up?

I also see this at the start of the backup which leads me to beleive that it is getting a clean copy of the Db.

 

I/O is frozen on database SUSDB. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

 

I know in the past with MSDE on 2k that you needed to either stop the instance or use osql to make the agent backup to a seperate file.

 

Anyone know?

 

Cheers.

Edited August 21, 2008 by cookie_monster

[Michael]

It is an excellent question. As far as I am aware SQL doesn't make a copy of the database elsewhere, but it should be relatively straight forward to create a backup to a file manually (to give you peace of mind).

 

I don't think SQL works with Shadow Copies as the database itself isn't necessarily hosted on a share. Shadow Copies only works with shares, not just any folder or directory.

 

I've used this technique before to move WSUS 3.0 to another server. You could also do the same, but just not restore it. If you had a server failure, you would re-install WSUS (which would also re-create the database), then restore the updates and the approval list.

[cookie_monster]

I'll take a look at that link cheers. I'm also going to have a read of the shadow copy documentation again i was under the impression that to use shadow copies with the previous versions client the directory had to be shared but the backup program could use the shadow copy writers anytime e.g when backing up open files.

[powdarrmonkey]

Are you using NTBackup?

Whether or not you have the database on a share, ntbackup will use shadow copies to back up the database because it is the cleanest way to take a snapshot of the database in the minimum time, then let it carry on serving clients while the backup takes place.

 

The event log message you see is the database being frozen to take the shadow copy, which only takes a few seconds. It is informational only, unless you find that the shadow copy is not releasing the database properly afterwards.

 

Edit: I've just read your post again and yes, it sounds like you're using NTBackup, so to answer your question yes it is SQL-aware.

[cookie_monster]

Ah ok that's very good news in the past i've read that this is not the case but only on blogs i've never found a concrete yes or no from MS. I wonder if that was in the 2003 SP1 improvements to shadow copies.

[Michael]

I'll take a look at that link cheers. I'm also going to have a read of the shadow copy documentation again i was under the impression that to use shadow copies with the previous versions client the directory had to be shared but the backup program could use the shadow copy writers anytime e.g when backing up open files.

 

Yes you're right, and this applies to files of any type within a share. Databases aren't considered to be a file as such, but more a directory of information. In saying that, this directory of information needs to be stored somewhere - probably a file

 

Shadow Copies would probably work if you manually backed up the database to a file for certain, but without manually creating this file, I don't know whether this would work with an active database.

[Michael]

Whether or not you have the database on a share, ntbackup will use shadow copies to back up the database because it is the cleanest way to take a snapshot of the database in the minimum time, then let it carry on serving clients while the backup takes place.

 

NTBackup does use the Shadow Copy Service when performing backups, but I think cookie_monster is talking about the Previous Versions tab working with active databases and I don't think it does.

[cookie_monster]

NTBackup does use the Shadow Copy Service when performing backups, but I think cookie_monster is talking about the Previous Versions tab working with active databases and I don't think it does.

 

Nope i must of generated some confusion by mentioning pervious versions, i was refering to the shadow copy service and NTBackups ability to use it to backup an open SQL Db.

 

Cheers anyway guys.

Edited August 21, 2008 by cookie_monster