February 8, 2007 at 3:33 pm
We have one log backup device that is supposed to have 10 transaction logs per day, one per hour for 10 hours. Because of an error that we found and fixed, we have 20 entries for 2 days. Is there a way to remove the unwanted 10 entries? We can see them in EM.Management.Backup by double-clicking on the device then clicking the View Contents... button, but there is no visible way to remove them in EM. Is there a way to remove them using T-SQL, DMO or something else?
February 8, 2007 at 6:08 pm
You can't remove appended backup files from a backup files.
I think you creating one backup file for each day...
Is there any reason appending the backup to single file?
MohammedU
Microsoft SQL Server MVP
February 9, 2007 at 8:33 am
Thank you for responding.
I am placing all the backups for a single day on a single backup device so that I only had to create a single backup device for the transactions. Actually, I thought that this was the normal way of automating the hourly transaction log backups. If it is not, please advise.
In a separate issue, yesterday I received an error when trying to restore the logs for one of the databases with a STOPAT, because I had transaction logs for more than one database on a single device. If I am understanding correctly, all transaction log backups for a single database need to go in a single backup device. In other words, if I am backing up the logs for 5 DBs, then I need 5 backup devices for the TLs.
February 11, 2007 at 7:17 pm
It will make things easier for the transaction log backups for each database to be in its own backup device.
You can still restore items from the mixed backup device, just use the FILE option during the restore e.g.
RESTORE LOG northwind FROM DISK = 'e:\backups\pubs.bak' WITH FILE = 2
where FILE = 2 indicates that you want to restore from the 2nd backup set on that file. You can view the backup sets contained in a particular backup file by using the RESTORE HEADERONLY command e.g.
RESTORE HEADERONLY FROM DISK = 'e:\backups\pubs.bak'
Peter Yeoh
Developer
Red Gate Software
February 12, 2007 at 10:34 am
My preference is to perform each backup to a separate device with the device/filename in the following format:
ServerName_dbName_yyyymmddhhmmss_buType.sbk
...where
ServerName = the SQL Instance name
dbName = the database name
yyyymmddhhmmss = date/time
buType = FULL | DIFF | LOG |FGRP (filegroup)
I have also done single device backups which started with a Full and subsequent Log backups for the day.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply