Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implementing a Flexible Backup Strategy


Implementing a Flexible Backup Strategy

Author
Message
Eli Leiba
Eli Leiba
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 19
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnis
sklein
sklein
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 78
The automation and flexibility of this backup strategy appears safe. I, however, would be concerned with the fact that it never does an incremental backup, which is the only way to truncate the transaction log. Does anyone else have this same concern, or am I simply missing something?



kenwallacedesign
kenwallacedesign
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 59

I think you are quibbling with semantics.

In terms of SQL Server Backups, I was under the impression that the term "Differential" is synonymous with "Incremental". If you look at your choices in EM, you can choose Complete or Differential, as oppposed, say, to a Full-Text Index, where you can do Full Populations or Incremental Populations. The Micro$oft team simply wasn't consistent with their terminology.

The Differential truncates the Transaction Log.





DCPeterson
DCPeterson
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1305 Visits: 432

Incremental and Differential backups are the same thing, or the terms are used interchangebly. However, differential backups DO NOT truncate the transaction log, only a transaction log backup does that. So the first poster is correct, this is not a comprehensive backup solution, but it could easily be made into one...

The design of the DatabasesBackupStrategy table is very poor. The odd kind of pseudo bit masking with the 'CCCCCCC' and substring function is very awkward and breaks one of the fundamental rules of database design (atomicity.) With a bit more thought and/or knowledge one could easily improve the design of this system, making it much more flexible. What if you can't do either a differential or a full backup daily? What if you want to do more than one per day? This design will not address these issues and potentially many others.



/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
abidali
abidali
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
when i execute exec sp_Flex_Db_Backup 'd:\sqlserverbkup' its display this error
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@BACKUPDESCRIPTION'.

what is this error could you trace and tell,
John Scarborough
John Scarborough
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 52
"The design of the DatabasesBackupStrategy table is very poor."

Ouch. Might be nicer to say ""The design of the DatabasesBackupStrategy table could be improved upon by doing this...".

Yes, we deal in data, hence truth, but the truth can be presented in friendly terms.

John Scarborough
MCDBA, MCSA
Alexander Yuryshev
Alexander Yuryshev
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
Good article.
It's not about everything, but can be extended if needed.
I think about implementing such thing on our customers servers.
I will extend settings table, maybe we'll write GUI and it will be better than 20 backup jobs.
bobby hathiramani-303695
bobby hathiramani-303695
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1
Great article, makes life so much easier for a DBA.
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