Backups

Question

Backups

September 17, 2012 at 7:06 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

demonfox (9/17/2012)
forgot Cell phone !! That is more of amber than blue ;D
btw, the link you provided were really informative.
Thanks for that...

You are welcome Avi:-)

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Paul White SSC Guru Points: 150468 More actions · Answer 2

Divine Flame (9/17/2012)
SQL Kiwi (9/17/2012)
Good question, I guess, though I would have preferred it without the INIT trap.
Thanks for the feedback Paul. Many of us (including me until I didn’t face this issue) assume that FORMAT & INIT work in the same manner, that's what prompted me to add INIT in this question. Hope it served the purpose.

It did. My personal preference (which no-one else may share, and that is fine) is for QotD questions to test one specific piece of knowledge. Not being able to combine compressed and non-compressed backups in the same set is one such piece of knowledge; knowing the difference between INIT and FORMAT is another. I might have written this as two questions, but again, this is just an opinion, nothing more than that.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLmountain SSCommitted Points: 1505 More actions · Answer 3

Divine Flame (9/17/2012)
SQL Kiwi (9/17/2012)
Good question, I guess, though I would have preferred it without the INIT trap.
Thanks for the feedback Paul. Many of us (including me until I didn’t face this issue) assume that FORMAT & INIT work in the same manner, that's what prompted me to add INIT in this question. Hope it served the purpose.

That it did. Nice.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 4

palotaiarpad (9/17/2012)
I guessed and won. I thought, SQL Server stores the compression flag in the header and not at the data part of the backup. And as the header remains, the second statement must fail. Actually i didn't find any clues what else is stored in the header.

I knew the header was left by INIT, guess I didn't think about the compression flag being in the header. Tell me why I don't like Mondays ...

Thanks for the question Divine!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

sestell1 SSChampion Points: 10230 More actions · Answer 5

Divine Flame (9/17/2012)...Many of us (including me until I didn’t face this issue) assume that FORMAT & INIT work in the same manner, that's what prompted me to add INIT in this question. Hope it served the purpose.

Thanks for the excellent question. I got it wrong and learned something important today!

Steve Nogradi Default port Points: 1430 More actions · Answer 6

Great question! I chose the wrong answer but I learned something new.

Error Handler Ten Centuries Points: 1086 More actions · Answer 7

Error Handler

Ten Centuries

Points: 1086

September 17, 2012 at 8:25 am

#1538638

Nice Question! GOt it

Best,
Naseer Ahmad
SQL Server DBA

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Good question.

But the referenced BoL page doesn't describe this behaviour, the problem isn't that a media set can't contain both compressed and uncompressed backups (if both backups worked in the script given in the question the media set would never contain more than one backup so it certainly wouldn't contain both a compressed backup and an uncompressed one, so exclusion of holding both doesn't preclude both those commands working). To account for the behaviour also requires the fact that "with INIT" doesn't reinitialise the media, just destroys any existing backup sets in it, and the result is that if a media set has ever contained a compressed backup it can never contain an uncompressed one even if the compressed one has been deleted (plus of course the same with compressed and uncompressed swapped).

Thanks to Raghu for the reference to something that does account for this behaviour.

So today I've learnt that INIT doesn't cause modification of the header. I guess I'll start using FORMAT everywhere I used to use INIT.

Tom

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 9

Divine Flame (9/17/2012)
Carlo Romagnano (9/17/2012)
You need add the clause "FORMAT" to succeed the second statement!
BACKUP DATABASE TestDatabase_1
TO DISK = 'C:\TestDatabaseBackup.BAK'
WITH COMPRESSION
GO
BACKUP DATABASE TestDatabase_2
TO DISK = 'C:\TestDatabaseBackup.BAK'
WITH NO_COMPRESSION, INIT
, FORMAT
I left the "FORMAT" deliberately just to make people aware about the difference between FORMAT & INIT. Many people think of FORMAT & INIT as the same.

Yup, that was me. I learned something today, too! Thanks for a good question.

Rob Schripsema
Propack, Inc.

Dave62 SSCertifiable Points: 7459 More actions · Answer 10

Thanks for the question. I got it right but first had to decide whether the "trick" to the question was the location 'C:\...' looking the same but being on 2 different servers. Same server wasn't specified and running the commands together as a batch wasn't specified. The correct answer would have been different if it wasn't the same server.

Enjoy!

Jim_K SSCrazy Points: 2418 More actions · Answer 11

Learned something today about the differences between Format and Init. Does the difference between the two gain you an advantage when you are backing up to tape (it's been years since any dbs I've been involved with backed up straight to tape)?

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 12

@tom - Glad to be help. 🙂

@jim_k - I am not sure if this can be an advantage, but to my knowledge -

Say the 1TB tape where it has backups taken each day from past 3 weeks.

(now we know that the tape's all backups are either in the compressed or uncompressed format (not both), but lets say compressed)

A.

Now if used the FORMAT command, the tape's header part is formatted, so it does not has the clue what kind of backups previously exists (as the entire media will be invalid), so the new first backup can be in compressed or in uncompressed format. Here FORMAT can be used for making the existing tape usable for new set of backups (any format).

B.

If used the INIT command, the tape's header is not been formatted, so it holds the details of what kind of backup it consists. Here when the backup is done even without using the COMPRESSION word the backup goes as compressed and it gets overwitten to the existing media (OR it will give the error message saying the format of the existing media and the new backup request is not matching and backup might fail). The backup always makes a default check on the HEADER section on the associated media and then act accordingly.

In the part B, if the FORMAT is used then the header details is removed and as the COMPRESS word is not mentioned so the data goes as uncompressed because the BACKUP commend could not find the details and it considers it as a new media.

In the sense of advantage

- By using FORMAT - I can say it just helps the tape to be reused for any type of backups completely.

- By using INIT - The backup needs to be continued in the same way as the previous backup format were and then reuse the entire tape's space.

Hope this helps.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 14

(Bob Brown)

SSCrazy

Points: 2705

September 18, 2012 at 4:20 am

#1538986

Thanks for the lesson. Learned something today!