December 14, 2008 at 11:35 pm
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?
December 15, 2008 at 12:11 am
ps (12/14/2008)
...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?
1) you cannot restrict the number of backups written to a backup file.
(you can only "clear" the file using WITH INIT)
2) Check out RESTORE in BOL.
restore database yourdb
from yourdevice
with file=2
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 15, 2008 at 12:36 am
had never used with File= clause....
Thanks it helped 🙂
December 15, 2008 at 10:44 pm
You can expire backups, but not sure how that works with files.
I always back up to a new file each day. If the file corrupts and you have multiple backups in there, you lose them all. Better to use separate files, IMHO.
December 16, 2008 at 3:54 am
Yes, i do take backups to a new file on a daily basis. was just experimenting with backup devices on test server when i came across that question.
---------------------------------
it seems expiredate and retaindays do not go together in 2000.
Server: Msg 3031, Level 16, State 1, Line 3
Option 'expiredate' conflicts with option(s) 'retaindays'. Remove the conflicting option and reissue the statement.
using these options together is possible in 2005 where RETAINDAYS takes precedence over EXPIREDATE. --msdn
---------------------------
It works with files as well.
---------------------------
What i was trying to figure out a way to expire or delete a particular backup set from a backup file(a backup file can contain multiple backups). Tried searching for this possibility... seems this is not possible..
with INIT overwrites the entire file and with SKIP overrides expirydate or retaindays clauses...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply