SQL Server Performance

Strange Backup

Discussion in 'Performance Tuning for DBAs' started by rweinstein, Nov 4, 2004.

  1. rweinstein New Member

    I was looking through the "Current" SQL Server log in EM and see the following:

    Database backed up: Database: DB1, creation date(time): 2004/05/24(12:17:14), pages dumped: 2318568, first LSN: 304999:29591:1, last LSN: 305102:744:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'DBASQL_DB_Test1_PID1300TID2632BID0TS97d2a832VD0'}).

    The issue I have is that I am not doing this backup. What I see is every night at 11:30pm, all the DB's are getting backed up (master, model, msdb, northwind, profiler, pubs, sqlbpa, and my prod DB).

    I have 2 maintenance plans, one runs at 6am, the other at 6pm and they only back up my Prod DB (I double-checked).

    I just can't seem to find out who or what or how all DB's are being backed up at 11:30pm.

    Is this my Network team doing their "Tape" backup? They indicated that they were doing it at 4am. What is a "Virtual Device"?

    I need to stop these backups because they are during my nightly refresh.

    Any help is greatly appreciated.

  2. thomas New Member

    Tape backups typically use a "Virtual Device". Sounds to me like (and it's deja vu for me!) the tape backup operators don't know what their software does very clearly, and they're backing your DB1 direct to tape every night without knowing it.

    Tom Pullen
    DBA, Oxfam GB
  3. rweinstein New Member


    You are correct. I just verified that this is a tape backup of all of my DB's. They said they reorganized the scheduler, which is why it is completing at midnight instead of 3am.

    Is there a good reason to backup all of the DB's in addition to my prod DB, like master, model, msdb, profiler, pubs, sqlbpa?

  4. thomas New Member

    I much prefer to back up to disk then copy the backup file to tape (this is a commonly adopted approach). Backups direct to tape can be a little problematic, especially restoring, you don't always get the options you may need (e.g. MOVE, REPLACE, etc.).

    And yes, you only need to backup each database once a night.

    Tom Pullen
    DBA, Oxfam GB
  5. vbkenya New Member

    It is a total waste of resources (time mainly) to backup the 'learning' databases - pubs and northwind. The network team is probably not aware of what to leave out. Please have a long talk with them.

    I consider the msdb and master database backups as ad hoc processes. I would only do them when I make changes to my system (server, databases, jobs, alerts, security etc.).

    Backing up the model database every night is pretty useless unless you make daily changes to it - and that would be a big surprise. Have you ever changed it since you installed SQL Server?

    Backup your production database based on your needs (and these are unique to every environment), backup master and msdb as appropriate and forget anything else that WILL not remind you of your job hunting skills.

    Nathan H.O.
  6. rweinstein New Member


    No, we are not making any changes to the model DB. I agree that there is not a need to do this backup or the other ones you suggested. I will ask my Network team to disable them from the nightly tape backup.

    I appreciate the input.


Share This Page