| Author |
Topic |
|
dwaine age
Starting Member
7 Posts |
 Posted - 2007-07-28 : 10:23:06
|
Hi, I'm new to sql db administration. I've managed to set up a scheme where I have a full backup every 1 week, differential every day and tlog every 15 min.However, a lot of times (about once a week) my backup chain breaks because of this random 6kb backup that goes to a location I have never heard of nor can I find. It's breaks my ability to apply differential backups that were made after this backup to full ones created before it.Any of you know what may be going on? I have SQL server agent disabled and run all my backups through osql.Thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 13:47:29
|
| Check backup job at that time and look for that backup's message in sql server log to get some idea. And ensure there is no other backup tool to do db backup. |
 |
|
|
dwaine age
Starting Member
7 Posts |
Posted - 2007-07-28 : 15:17:14
|
Oh good, I found some more info looking there - thanks.The databases seem to be freezing and then thawing for a snapshot. Then right after the database backup jobs show up.I remember now playing around with replication, but decided it wasn't for me. I thought I removed it completely but this must be something left over from it? Any advice on how I can go in and completely remove it?Backup job log:Database backed up: Database: TopsData, creation date(time): 2007/05/27(22:08:28), pages dumped: 1, first LSN: 235626:42:1, last LSN: 235626:53:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{CBD3AC93-A890-4D8E-9AF5-5B1CDB5431D3}6'}).Sreenshot: |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 18:56:11
|
| How did you remove replication? Used tools -> replication -> disable ..... in em? For backup, how do you backup db? With script? As sql job? Backup to device or disk? Is the one you highlighted a scheduled backup? |
|
|
|
dwaine age
Starting Member
7 Posts |
Posted - 2007-07-28 : 20:21:43
|
>> How did you remove replication? Used tools -> replication -> disable ..... in em?Oh.. you know what, I think that was actually before I moved the DB to a different server. So I'm not really so sure replication is what is trying to create the snapshots. Do you know what else might?>>For backup, how do you backup db? With script? As sql job? Backup to device or disk?I use the windows scheduler to execute batch files which run tsql backup scripts through osql. After that, the batch file compresses and archives the backup both locally and on the backup server.>>Is the one you highlighted a scheduled backup?The highlighted backup is one of those 6kb backups thats giving me the problems. It occurs on all databases right after that "IO is frozen/IO is thawed" sequence. I have no idea whats going on, but my scripts only touch topsdata.Example TSQL script:--DifferentialDECLARE @BackupName varchar(999)DECLARE @BackupName1 varchar(999)DECLARE @BackupName2 varchar(999)DECLARE @BackupName3 varchar(999)DECLARE @BackupName4 varchar(999)SET @backupname1 = 'C:\Temp Backup\SQL\Tops_Diff_' SET @backupname2 = CONVERT(char(8),getdate(),112)SET @backupname3 = LEFT(REPLACE(CONVERT(char(8),getdate(),108),':',''),4)SET @backupname4 = '.bak'set @backupname = @backupname1 + @backupname2 + @backupname3 + @backupname4BACKUP DATABASE [TopsData] TO DISK = @backupname WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'TopsData backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INTselect @i = position from msdb..backupset where database_name='TopsData'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='TopsData')RESTORE VERIFYONLY FROM DISK = @backupname WITH FILE = @i Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 20:39:34
|
| What's sql service pack level on the server? |
|
|
|
dwaine age
Starting Member
7 Posts |
Posted - 2007-07-28 : 20:52:05
|
| SP4 |
|
|
|
dwaine age
Starting Member
7 Posts |
Posted - 2007-07-28 : 21:50:36
|
| Well it just dawned on me to do a google search for the relevant strings from my error log and it looks as if ntbackup is causing this when volume shadow copy is employed on other files on the server.I still haven't found fix, so any suggestions would be appreciated.http://support.microsoft.com/kb/903643 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 22:22:11
|
| Contact Microsoft to get Hotfix 828481. |
|
|
|
dwaine age
Starting Member
7 Posts |
Posted - 2007-07-28 : 22:31:23
|
| I thought that just allowed ntbackup to backup the database. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-29 : 01:50:38
|
| Can you configure NTBackup to prevent it trying to backup the SQL Databases?Kristen |
|
|
|
dwaine age
Starting Member
7 Posts |
Posted - 2007-07-29 : 09:10:00
|
| I'm not trying to backup SQL with ntbackup. I think it's caused when volume shadow copy is turned on to backup another file.I've turned off VSS for my backups right now, not a very optimal solution, but the best I can find ATM. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 03:15:28
|
"I think it's caused when volume shadow copy is turned on"OK. I would hope there was an option to say "But don't bother with SQL files"  Kristen |
|
|
|
|
Weird random DB backup.