SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup using Device


Backup using Device

Author
Message
ps.
ps.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3221 Visits: 3668
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
ALZDBA
ALZDBA
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11999 Visits: 8918
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ps.
ps.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3221 Visits: 3668
had never used with File= clause....
Thanks it helped Smile



Pradeep Singh
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61421 Visits: 19097
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
My Blog: www.voiceofthedba.com
ps.
ps.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3221 Visits: 3668
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search