Backup using Device

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup using Device

Backup using Device

Rate Topic

Display Mode

Topic Options

Author

Message

ps.

Posted Sunday, December 14, 2008 11:35 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

ALZDBA

Posted Monday, December 15, 2008 12:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 6,861, Visits: 8,046

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #619409

ps.

Posted Monday, December 15, 2008 12:36 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

had never used with File= clause....
Thanks it helped :)

Pradeep Singh

Post #619416

Steve Jones - SSC Editor

Posted Monday, December 15, 2008 10:44 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:02 AM
Points: 31,410, Visits: 13,725

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

ps.

Posted Tuesday, December 16, 2008 3:54 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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 »

Permissions