Restore Timing of Full Backup + Transaction Logs

I have a full database backup that kicked off at 3:41am and completed at
3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
start and completion times are variable depending on how long the prior step
s
took. The database also has hourly transaction log backups scheduled that
take place on the half hour, so in this case, there was a transaction log
backup that kicked off at 3:30 and finished at 3:43.
In this case, it's pretty obvious that I will restore the full backup and
then restore subsequent logs starting with the next log that kicked off at
4:30, but this possibility of overlap got me wondering about a scenario wher
e
the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
is the full backup capturing just the exact snapshot that it has at 3:20, an
d
the 3:30 log would still apply in order to get the changes from 3:20 on? Or
will the full backup account for all data and changes to data from 3:20-3:40
?
--Shelley
SQL Server 2000 Enterprise Edition, sp3I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:

> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:

> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion
of the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion
of the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> It's true that the log file backup that are done while the full database
> backup runs is redundant, but IF it runs you need the log file if you
> have to do a restore. Even though the changes in the log might already
> be included in the FULL database backup file, SQL server records that a
> log file backup has been performed and it's needed as a part of the
> logfile backup sequence.
>
Good catch, I meant to include that in my "To avoid confusion"
paragraph... :-(|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator

Restore Timing of Full Backup + Transaction Logs

I have a full database backup that kicked off at 3:41am and completed at
3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
start and completion times are variable depending on how long the prior steps
took. The database also has hourly transaction log backups scheduled that
take place on the half hour, so in this case, there was a transaction log
backup that kicked off at 3:30 and finished at 3:43.
In this case, it's pretty obvious that I will restore the full backup and
then restore subsequent logs starting with the next log that kicked off at
4:30, but this possibility of overlap got me wondering about a scenario where
the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
is the full backup capturing just the exact snapshot that it has at 3:20, and
the 3:30 log would still apply in order to get the changes from 3:20 on? Or
will the full backup account for all data and changes to data from 3:20-3:40?
--Shelley
SQL Server 2000 Enterprise Edition, sp3I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
> start and completion times are variable depending on how long the prior steps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario where
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
> is the full backup capturing just the exact snapshot that it has at 3:20, and
> the 3:30 log would still apply in order to get the changes from 3:20 on? Or
> will the full backup account for all data and changes to data from 3:20-3:40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion of the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
> start and completion times are variable depending on how long the prior steps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario where
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
> is the full backup capturing just the exact snapshot that it has at 3:20, and
> the 3:30 log would still apply in order to get the changes from 3:20 on? Or
> will the full backup account for all data and changes to data from 3:20-3:40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
> start and completion times are variable depending on how long the prior steps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario where
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
> is the full backup capturing just the exact snapshot that it has at 3:20, and
> the 3:30 log would still apply in order to get the changes from 3:20 on? Or
> will the full backup account for all data and changes to data from 3:20-3:40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
>> I have a full database backup that kicked off at 3:41am and completed
>> at 3:56am. The backup is step 4 of a multi-step job that begins at
>> 3am, so the start and completion times are variable depending on how
>> long the prior steps took. The database also has hourly transaction
>> log backups scheduled that take place on the half hour, so in this
>> case, there was a transaction log backup that kicked off at 3:30 and
>> finished at 3:43.
>> In this case, it's pretty obvious that I will restore the full backup
>> and then restore subsequent logs starting with the next log that
>> kicked off at 4:30, but this possibility of overlap got me wondering
>> about a scenario where the full backup kicks off at say 3:20. and
>> finishes at 3:40. In that case, is the full backup capturing just the
>> exact snapshot that it has at 3:20, and the 3:30 log would still apply
>> in order to get the changes from 3:20 on? Or will the full backup
>> account for all data and changes to data from 3:20-3:40?
>> --Shelley
>> SQL Server 2000 Enterprise Edition, sp3
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
>> To avoid confusion, it might be best to not run t-log backups at the
>> same time as a full backup. My full backup job disables my t-log
>> backup job, re-enabling it when finished:
>> Job step Task
>> -- --
>> 1 Disable t-log backup job
>> 2 Full backup, "On success" and "On failure" go to #3
>> 3 Enable t-log backup job
> It's true that the log file backup that are done while the full database
> backup runs is redundant, but IF it runs you need the log file if you
> have to do a restore. Even though the changes in the log might already
> be included in the FULL database backup file, SQL server records that a
> log file backup has been performed and it's needed as a part of the
> logfile backup sequence.
>
Good catch, I meant to include that in my "To avoid confusion"
paragraph... :-(sql

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisR
I would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO
|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegr oups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>
|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert
|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegr oups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>
|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
--
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
--
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>> I restored MSDB onto a new box and my jobs won't run now:
>> Unable to connect to SQL Server '(local)'. The step failed.
>> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
>> answer to this? If so, what about moving the DTS Packages?
>> --
>> SQL2K SP3
>> TIA, ChrisR
>>
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> > Thanks Andrew.
> >
> > The "Save as" option suits me just fine. But assuming I do that in place
> > of
> > Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> > Packages
> > and those that don't. I know the other options are to script them or to
> > use
> > DTS to transfer them. Is anyone aware of the pros/ cons for either one?
If
> > I
> > do either of these methods, will I be missing something vital from NOT
> > Restoring MSDB? I don't think so based on past experience, but its been
a
> > while.
> >
> > TIA, ChrisR
> >
> >
> >
> > "ChrisR" <bla@.noemail.com> wrote in message
> > news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> >> I restored MSDB onto a new box and my jobs won't run now:
> >>
> >> Unable to connect to SQL Server '(local)'. The step failed.
> >>
> >> Am I better off to use the Transfer Jobs Task in DTS or is there a
simple
> >> answer to this? If so, what about moving the DTS Packages?
> >>
> >> --
> >> SQL2K SP3
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple[vbcol=seagreen]
>