+ Post New Thread
Results 1 to 8 of 8
Windows Thread, SQL Express Db and shadow copies in Technical; When i run a windows backup on our WSUS box which is also running SQL express it lists the Db's ...
  1. #1
    cookie_monster's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    4,201
    Thank Post
    392
    Thanked 278 Times in 239 Posts
    Rep Power
    74

    SQL Express Db and shadow copies

    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.
    Last edited by cookie_monster; 21st August 2008 at 10:35 AM.

  2. #2

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    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.

  3. #3
    cookie_monster's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    4,201
    Thank Post
    392
    Thanked 278 Times in 239 Posts
    Rep Power
    74
    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.

  4. #4

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    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.

  5. #5
    cookie_monster's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    4,201
    Thank Post
    392
    Thanked 278 Times in 239 Posts
    Rep Power
    74
    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.

  6. #6

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    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.

  7. #7

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    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.

  8. #8
    cookie_monster's Avatar
    Join Date
    May 2007
    Location
    Derbyshire
    Posts
    4,201
    Thank Post
    392
    Thanked 278 Times in 239 Posts
    Rep Power
    74
    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.
    Last edited by cookie_monster; 21st August 2008 at 02:15 PM.

SHARE:
+ Post New Thread

Similar Threads

  1. Shadow Copies
    By Gambit in forum Network and Classroom Management
    Replies: 11
    Last Post: 28th February 2007, 02:27 PM
  2. Need MS hotfix for Shadow Copies bug - KB 833167
    By ajbritton in forum Windows
    Replies: 1
    Last Post: 8th January 2007, 03:46 PM
  3. BAsic question about Shadow Copies
    By Kyle in forum Windows
    Replies: 5
    Last Post: 6th March 2006, 07:56 PM
  4. Question about Shadow Copies.....?
    By Kyle in forum Windows
    Replies: 3
    Last Post: 6th March 2006, 12:41 AM
  5. Shadow Copies
    By ajbritton in forum Windows
    Replies: 10
    Last Post: 10th December 2005, 11:13 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •