Can anyone recommend any best practices for restoring local packages? In a
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?
In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>
|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.
> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Thursday, March 22, 2012
DTS disaster recovery
Can anyone recommend any best practices for restoring local packages? In a
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.
> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.
> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?
DTS disaster recovery
Can anyone recommend any best practices for restoring local packages? In a
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.
> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?
disaster recovery situation I will have 1 SQL Server box rather than 4 so I
won't be able to restore all of my local packages by restoring MSDB. Any
thoughts?In the same situation, I would restore the MSDB databases from each server
as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Also, save out each DTS package as a Structured Storage file and dump that
into a version control application such as Source Safe.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Terri" <terri@.cybernets.com> wrote in message
news:dml9p8$vqu$1@.reader2.nmix.net...
> Can anyone recommend any best practices for restoring local packages? In a
> disaster recovery situation I will have 1 SQL Server box rather than 4 so
> I
> won't be able to restore all of my local packages by restoring MSDB. Any
> thoughts?
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:evifdFg9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> In the same situation, I would restore the MSDB databases from each server
> as MSDB1, MSDB2, etc, then move the data into the main MSDB database...
Would anyone have any code that would select and insert this data.
> Also, save out each DTS package as a Structured Storage file and dump that
> into a version control application such as Source Safe.
Does this need to be done with the GUI?
More generally would named instances help here?
Wednesday, March 7, 2012
DTS - Recovery Model
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.
Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.
1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?
Any suggestion is really appreciate.
Thank you very much - kind regards.
FrancoYikes!
If I read this correctly:
1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting
As far as I know, nothing regarding the database recovery model was changed in SP3. There were, however, many security changes to SP3. It may be possible that the switch to the Simple model is failing due to a permissions issue (the context under which DTS is running may not be permitted to alter database settings).
You do realize, of course, that your backups and transaction logs are useless after the DTS package runs? I mean, you can recover your data up to the point that the DTS package starts, but everything after that is toast?
I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences.
Hugh Scott
Originally posted by franco
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.
Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.
1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?
Any suggestion is really appreciate.
Thank you very much - kind regards.
Franco|||This is the explanation I have from another forum that makes light on the subject:
The problem was that as well as switching on select into/bulk copy it also performed a
dump tran databasename with no_log
This invalidated your log backup chain and required a full database backup to be performed immediately to maintain recoverability. It was never intended to do this and there was no real indication on this (it was in the event log but it was not a documented side effect of the copy objects task) so this bug was fixed in SP3. As the transaction log is no longer truncated the log chain is kept valid. However it still uses bcp functionality and should be minimally logged BUT your transaction log backups will be much bigger. I have done testing to see that this was fixed but must say I haven't checked the log sizes. I will do some testing and post back. Hopefully my above rambling explains why this change was made.
HTH
Jasper Smith|||RE:
Yikes!
If I read this correctly:
1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting
Q1 I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences. Hugh Scott
A1 As with many things, much depends on available resources, the nature of the application / purpose of the DB, the size / importance / purpose of the loads involved, etc., etc., etc..
For example, in some mission critical (and resource / budget constrained) production environments sometimes load schemes amount to something like: Full (final TL dump) --> Simple (dbo / single user) --> (perform data loads) --> Diff dump --> Full (multi user). Obviously, if large numbers of OLTP inserts / updates, etc. must be allowed in a fully logged fashion (7/24) such a scheme is not appropriate.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.
Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.
1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?
Any suggestion is really appreciate.
Thank you very much - kind regards.
FrancoYikes!
If I read this correctly:
1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting
As far as I know, nothing regarding the database recovery model was changed in SP3. There were, however, many security changes to SP3. It may be possible that the switch to the Simple model is failing due to a permissions issue (the context under which DTS is running may not be permitted to alter database settings).
You do realize, of course, that your backups and transaction logs are useless after the DTS package runs? I mean, you can recover your data up to the point that the DTS package starts, but everything after that is toast?
I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences.
Hugh Scott
Originally posted by franco
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.
Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.
1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?
Any suggestion is really appreciate.
Thank you very much - kind regards.
Franco|||This is the explanation I have from another forum that makes light on the subject:
The problem was that as well as switching on select into/bulk copy it also performed a
dump tran databasename with no_log
This invalidated your log backup chain and required a full database backup to be performed immediately to maintain recoverability. It was never intended to do this and there was no real indication on this (it was in the event log but it was not a documented side effect of the copy objects task) so this bug was fixed in SP3. As the transaction log is no longer truncated the log chain is kept valid. However it still uses bcp functionality and should be minimally logged BUT your transaction log backups will be much bigger. I have done testing to see that this was fixed but must say I haven't checked the log sizes. I will do some testing and post back. Hopefully my above rambling explains why this change was made.
HTH
Jasper Smith|||RE:
Yikes!
If I read this correctly:
1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting
Q1 I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences. Hugh Scott
A1 As with many things, much depends on available resources, the nature of the application / purpose of the DB, the size / importance / purpose of the loads involved, etc., etc., etc..
For example, in some mission critical (and resource / budget constrained) production environments sometimes load schemes amount to something like: Full (final TL dump) --> Simple (dbo / single user) --> (perform data loads) --> Diff dump --> Full (multi user). Obviously, if large numbers of OLTP inserts / updates, etc. must be allowed in a fully logged fashion (7/24) such a scheme is not appropriate.
Subscribe to:
Posts (Atom)