Backups

Question

Backups

Divine Flame

SSCoach

Points: 15943
More actions
September 15, 2012 at 12:31 pm

#262010

Comments posted to this topic are about the item Backups
Sujeet Singh

Viewing 15 posts - 1 through 15 (of 31 total)

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 1

Thank you for the question, was nice and good brain exercise on Sunday 🙂

Got the answer wrong and I kind of tricked myself into it with the INIT as it indicates that "If INIT is specified, any existing backup set on that device is overwritten..." (and RETAIN and EXPIRYDATE is not mentioned, so)

Even though I have noticed the COMPRESSION and NO_COMPRESSION, considering the INIT and it saying I thought both will succeed and but the .BAK file will contain only the 2nd database (as it is going to overwrite). If the default NOINIT was mentioned then the "Restriction" might would have apply.

So here the INIT in the second batch has no significance at all.

I couldn't test the code on my local as it is running on 2K8-R2 Express edition

Msg 1844, Level 16, State 1, Line 3

BACKUP DATABASE WITH COMPRESSION is not supported on Express Edition with Advanced Services.

EDIT;

I see here that the HEADER is untouched when the INIT is used so the information is not entirely erased and it checks to see if the media is been compressed or not.http://blogs.msdn.com/b/sql_pfe_blog/archive/2010/08/09/backup-compression-behavior-when-appending-backups-to-an-existing-media-set.aspx">

http://blogs.msdn.com/b/sql_pfe_blog/archive/2010/08/09/backup-compression-behavior-when-appending-backups-to-an-existing-media-set.aspx

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

demonfox SSCertifiable Points: 6289 More actions · Answer 2

nice question ... I got it wrong anyways.. was suppose to mark the another answer 🙁

I need coffee ...

thanks for the question..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

Wonderful question Divine. I would recommend taking a look at the following link as well:

http://msdn.microsoft.com/en-us/library/ms178062.aspx

It talks about Media Sets, Media Families, and Backup Sets in SQL Server.

~ 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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

demonfox (9/16/2012)
nice question ... I got it wrong anyways.. was suppose to mark the another answer 🙁
I need coffee

Me too got this question wrong...

Took a wrong turn on the way and reached office half an hour late...

Forgot to carry my cell phone...

My my my ...Monday morning Blues....Whoopppp! :w00t:

~ 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

Ravi SQL SSCommitted Points: 1714 More actions · Answer 5

Ravi SQL

SSCommitted

Points: 1714

September 17, 2012 at 12:31 am

#1538410

Good question...

Regards,
Ravi.

okbangas SSChampion Points: 11773 More actions · Answer 6

In most scenarios, I would say that both should fail as SQL Server does/should not have access to create files on the system drive, but that is a different scenario I guess. I got this wrong, because I thought init would cause an overwrite and thus allow the backup file to become uncompressed. Nice question.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 7

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.

Paul White SSC Guru Points: 150467 More actions · Answer 8

Good question, I guess, though I would have preferred it without the INIT trap.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

demonfox SSCertifiable Points: 6289 More actions · Answer 9

Lokesh Vij (9/17/2012)
demonfox (9/16/2012)
nice question ... I got it wrong anyways.. was suppose to mark the another answer 🙁
I need coffee
Me too got this question wrong...
Took a wrong turn on the way and reached office half an hour late...
Forgot to carry my cell phone...
My my my ...Monday morning Blues....Whoopppp! :w00t:

forgot Cell phone !! That is more of amber than blue ;D

btw, the link you provided were really informative.

Thanks for that...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Carlo Romagnano SSC-Insane Points: 22664 More actions · Answer 10

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

Divine Flame SSCoach Points: 15943 More actions · Answer 11

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.

Sujeet Singh

Divine Flame SSCoach Points: 15943 More actions · Answer 12

Lokesh Vij (9/16/2012)
Wonderful question Divine. I would recommend taking a look at the following link as well:
http://msdn.microsoft.com/en-us/library/ms178062.aspx
It talks about Media Sets, Media Families, and Backup Sets in SQL Server.

Thanks for the feedback & for providing the informative link Lokesh.

Sujeet Singh

Divine Flame SSCoach Points: 15943 More actions · Answer 13

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.

Sujeet Singh

Lokesh Vij SSChampion Points: 10836 More actions · Answer 14

Thanks for the feedback & for providing the informative link Lokesh.

You are welcome Divine 🙂

~ 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