Implementing a Flexible Backup Strategy
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnis
-
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?
-
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.
-
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
*****************/
-
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,
-
"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 -
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.
-
Great article, makes life so much easier for a DBA.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply