Backup Questions... You there Denny, SQL Bill et al?

[SQLBI]

Hi,

I was recently asked to perform a number of backup operations by, in order not to upset anyone in Seattle, my "client" . I was only allowed to use SSMS, which may not have been installed correctly as many of the features were unavailable .

[blue]1.

My "client" instructed me to perform a backup to capture all activity since the last transaction log backup, however the database in question was fixed in simple Recovery Mode.[/blue]

My "client" was very unhelpful in explaining the requirement further so i opted for a Full backup, appending to the exisiting media stipulated by my "client".

[blue]2.

My "client" assked me to perform another backup since the last Transaction Log backup, again on a DB fixed in Simple Recovery Mode but this time making the resulting backup file as small as possible.[/blue]

Again no further info was forthcoming from my "client" and i opted for a Differential backup, Over writing the existing media set.

[blue]3.

My "client asked me to perform a backup capturing all activity since the last transaction Log backup, again keeping the resulting file size to a minimum and again on a DB fixed in Simple Recovery mode, however this particular DB was used in Replication which shouldn't be affected by the backup.[/blue]

Again no further info was available so I opted for a full backup appending to the exiting media.

My "client" didn't tell me whether or not my particular solutions were correct or not, merly that i needed to do better overall...

My initial feelings were that these questions we're misleading as to my knowledge, it isn't possible to perform a Log backup in Simple Recovery mode, or could this have been an attempt by my "client" to catch me out?

Also, are there other ways of keeping the backup files small, other than what i did?

Finally, how would my backup routines affect Replication?

How would anyone else have approached this particular task... maybe you've been asked to do the same thing by one of your "clients"?

Any suggestions would be gratefully received.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[onpnt]

Log backups are not available on simple recovery. Typically replicated database are set that way.

You can't just backup a replicated database like non-replicated databases also.

http://msdn2.microsoft.com/en-us/library/ms151152.aspx

read up a bit. Apologies as I do not have much time to reply now.

Also, "Do not refer to members in your initial post!"

No one here is obligated to help you. Putting Denny or Bill's name in the header creates that scenario. There are several members in this community that are well suited to help you and if one of them has time I'm sure they will.
Thanks...

[sub]____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own[/sub]

 

[SQLBI]

My sincerest apologies, i didn't mean to imply any obligation. I see your point, duly noted.

Any help is most gratefully received, whoever provides it.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[SQLBI]

Hi,
I've spent the weekend going over the above scenarios and i think i've sorted points 1 and 2.

Firstly, i beleive any reference made by my "client" to Transaction Log backups has been a red herring to get me thinking about Differential backups which as we know, capture activity since the last Full backup, therefore my solution to 2. was incorrect. I now believe i should have performed a full backup, specifying overwrite All Existing Backup Sets (WITH INT).

I'm still however stumped by 3. I've read through the link and associated documents provided by onpnt yet i can't see how i can do anything from the SSMS backup pages to specifically allow for a database involved in replication.

If anyone can elaborate futher on this I would be grateful.

Thanks in advance.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[SQLBill]

We backup databases being replicated all the time without any problem.

If it's the replicated database, you really shouldn't need to back it up.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Replication doesn't change the methods used to backup the publisher. Upon restore the KEEP_REPLICATION flag needs to be set when restoring the publisher. If subscribers are receiving transactions from a publisher only (no tables which aren't replicated) then backups aren't needed as you can simply republish the data in the event that the database is lost.

For #3 a full backup should do the trick.

I would be curious to know where they are going with the "keeping the resulting file size to a minimum". Are they looking for you to recommend a third party backup tool, to compress the file or what? Since it's simple you can't to a T/Log backup and a diff as you pointed out if everything since the last full backup.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBI]

I would be curious to know where they are going with the "keeping the resulting file size to a minimum". Are they looking for you to recommend a third party backup tool, to compress the file or what?

My assumption initially was that they we're looking for a differential, but that's only relevant with Full backups, none were mentioned and the "Contents" option for the existing backup device was disabled. I'm reasonably happy that they wanted me to overwrite the existing media set as this was created in #1. however not referenced in #2 or #3. Nowhere was there an opportunity to make any recommendations.

We backup databases being replicated all the time without any problem.

If it's the replicated database, you really shouldn't need to back it up.

I have no idea what difference that made either, the only screen available was the 2 pages of Right Click DB > Task > Backup... Nothing even remotely relevant to replication other than the statement "...the database is also involved in replication...".

All in all, clear as mud!

Thanks for your input guys, i'll report back if i find anything else out.

The worst thing is that the "client" feedback is as clear as the questioning and i've no idea which response was correct or otherwise... only that i need to find +7% extra from somewhere . I would like to know either way, if only to understand the concept more.





Cheers,
Leigh

The problem with common sense is that it isn't that common!