Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup using Device Expand / Collapse
Author
Message
Posted Sunday, December 14, 2008 11:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:35 AM
Points: 2,223, Visits: 3,649
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?






Pradeep Singh
Post #619399
Posted Monday, December 15, 2008 12:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #619409
Posted Monday, December 15, 2008 12:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:35 AM
Points: 2,223, Visits: 3,649
had never used with File= clause....
Thanks it helped :)





Pradeep Singh
Post #619416
Posted Monday, December 15, 2008 10:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:49 PM
Points: 31,161, Visits: 15,607
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #620179
Posted Tuesday, December 16, 2008 3:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:35 AM
Points: 2,223, Visits: 3,649
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...







Pradeep Singh
Post #620275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse