SQL Server is backing up all databases- who told it to?
-
In my logs, I'm finding the following type of entries for all databases, which ocurrs every day at 01:01 am:
01:01:08.57 Database JLRXTestApp: IO is frozen for snapshot
01:01:09.19 Database JLRXApp: IO is thawed
01:01:09.27 Database backed up: Database: JLRXApp, creation date(time): 2005/08/15(12:30:30), pages dumped: 1, first LSN: 69534:35373:1, last LSN: 69534:35376:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{1B63D88F-7D4D-47C3-9330-7303C3DE12E2}10'}).
The Maintenance Plan for the database JLRXApp is set to:
* Backup & Verify the DB
* To Disk
* Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, at 11:00:00 PM.
And this is what I get at 23:00 hours each day which is what I would expect:
Database backed up: Database: JLRXApp, creation date(time): 2005/08/15(12:30:30), pages dumped: 1507244, first LSN: 69534:35278:1, last LSN: 69534:35370:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\Backups\JLRXApp\JLRXApp_db_200606142300.BAK'}).
So what is happening at 1am each day, and what is the "TYPE=VIRTUAL_DEVICE"?
-
Is it possible that it's your network operations folks using a third-party backup program with a Shadow Copy option, such as Veritas Backup Exec, for instance?
-
I feel like a real idiot. Yeah, the backup is running at 1am. G.
-
I would tell them to stop it and set their backpup to ignore/skip the .mdf/.ldf files.
Why? Because it's messing up your backups. If they are using a SQL Server agent, then when they do a full backup, your log backups are no longer any good (they don't apply to the full backup made by the network people).
Full backup by you
Log backup by you
log backup by you
log backup by you
Network full backup
log backup by you
log backup by you
Those last two log backups apply to the Network full backup. Will you remember that when it comes time to restore?
-SQLBill
-
While I agree that both groups performing backups can cause issues, I don't think they're using the agent to back up at 1:00. Based on the IO frozen, IO thawed messages, I'm guessing "Shadow Copy", which makes a point in time snapshot. Of course they could also be running an Agent backup later in the night, which would indeed cause the problems SQL Bill is pointing out.
I'd definitely work with the Ops team and figure out a solution that allows for full disaster recovery, as well as ironing out any conflicts. Oh, and test, test, test.
-
Well, this is how it's supposed to work:
12:45am onwards, every 4 hours, the transaction logs are backed up by the Maintenance Plan (MP) which creates TRN files and are stored on another volume on the server
(4:45am, 8:45am, 12:45pm, 4:45pm, 8:45pm, 12:45am)
11pm, a full backup of the database is performed by the MP creating a BAK file which is stored on another volume on the server
1am the next day, the full backup above should have finished, and BackupExec backs up the files produced by the above MP, database & logs (TRN & BAK), to tape.
I suppose I should have the tape backup moved from 1am to 11:59pm the previous day.......Thanks for the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply