Hi I've created a backup device for taking backups (Sql 2000)
sp_addumpdevice 'Disk', 'DailyActivity_Full_Backup', 'G:\Backup\DailyActivityFull.BAK'
I've taken full backup using
Backup database DailyActivity to DailyActivity_Full_Backup -- Backup1
I again took full backup using the above command (backup 2) and found that the BAK file size doubled. I was able to restore both backup1 and backup2 from the same backup file using Enterprise Manager as i could see the 2 backups in the DailyActivityFull.BAK file
Using query analyzer if i tried to restore, it restored the backup 1 from the file.
alter database dailyactivity set single_user with rollback immediate
restore database dailyactivity from disk='g:\backup\DailyActivityFull.bak'
My questions are:-
1. how to control the number of backup sets that can be written in a file using the device i created above( in above example 2 backups are there and i can add more).
2. Since there are multiple backups in that file, how can i restore the 2nd backup using t-sql
I was able to read the backup details by restoring the header of the backup file.
Restore headeronly from DailyActivity_Full_Backup
The column Position has value 1 and 2. Can it be used during restoration?