I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
Ted
Possibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
sql
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Friday, March 30, 2012
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPris
e
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the
backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time t
o run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPri
se
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER
to
> stop it. If I do this for any of the sample databases, the Restore dialogu
e
> opens. I even verified there were no open connections to the 2 databases i
n
> question, & it happens on both servers. I had to do a command line restore
.
> The backup, etc works fine, just the restore won't open. It did this on SQ
L
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec service
s,
> Trend Micro AV, etc.
> Ted
e
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the
backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time t
o run in order to delete
a lot of backup history).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPri
se
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER
to
> stop it. If I do this for any of the sample databases, the Restore dialogu
e
> opens. I even verified there were no open connections to the 2 databases i
n
> question, & it happens on both servers. I had to do a command line restore
.
> The backup, etc works fine, just the restore won't open. It did this on SQ
L
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec service
s,
> Trend Micro AV, etc.
> Ted
Restore won't open
I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Manager, when I right-click on my 2 main databases, choose All
Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
stop it. If I do this for any of the sample databases, the Restore dialogue
opens. I even verified there were no open connections to the 2 databases in
question, & it happens on both servers. I had to do a command line restore.
The backup, etc works fine, just the restore won't open. It did this on SQL
SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
terminated any non-critical services to no avail, i.e. Backup Exec services,
Trend Micro AV, etc.
TedPossibly a LOT of backuphistory to read. Check out the number of rows in the backup history tables
in msdb. Also, read about sp_delete_backuphistory (which takes a long time to run in order to delete
a lot of backup history).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ted Cole" <Ted Cole@.discussions.microsoft.com> wrote in message
news:99032F45-145B-4500-ABCB-9B922BB540E1@.microsoft.com...
>I have 2 SQL 2K servers, ea running Server2k SP4 and SQL2K SP4. In EnterPrise
> Manager, when I right-click on my 2 main databases, choose All
> Tasks...Restore, I then get a perpetual hour-glass & have to terminate ER to
> stop it. If I do this for any of the sample databases, the Restore dialogue
> opens. I even verified there were no open connections to the 2 databases in
> question, & it happens on both servers. I had to do a command line restore.
> The backup, etc works fine, just the restore won't open. It did this on SQL
> SP3 & I hoped installing SP4 would fix it, but no go. Any ideas? I even
> terminated any non-critical services to no avail, i.e. Backup Exec services,
> Trend Micro AV, etc.
> Ted
Monday, March 26, 2012
restore to new server causes problems
SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
restore to new server causes problems
SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql
restore to new server causes problems
SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might want to check out
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text reports no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sysservers? There are 3
> columns, srvname, datasource & srvnetname that all have the original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might want to check out
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text reports no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sysservers? There are 3
> columns, srvname, datasource & srvnetname that all have the original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.
Friday, March 23, 2012
Restore sys databases
Am i able to restore system databases to sql2005 from sql2k SP4 backup
dumps?
Thanks
No.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
|||thanks, Geoff. you saved me a lot of time searching it
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
sql
dumps?
Thanks
No.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
|||thanks, Geoff. you saved me a lot of time searching it
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud1tSqdUGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Am i able to restore system databases to sql2005 from sql2k SP4 backup
> dumps?
> Thanks
>
sql
Wednesday, March 7, 2012
Restore of database via Veritas 9.1 fails with communication error
We are running SQL 2000 sp4, only one user database (SAP) the database is 63 GB.
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.
Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.
My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.
Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.
When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.
Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.
Just in case anyone else hits this issue:
The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.
Follow on question:
THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.
without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.
It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.
Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.
Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.
My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.
Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.
When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.
Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.
Just in case anyone else hits this issue:
The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.
Follow on question:
THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.
without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.
It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.
Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too
Monday, February 20, 2012
restore MASTER database ( SQL 7.0 sp4)
when I start SQL 7.0 with single user model and restore Master database
sqlservr -c -m
in QA use command:
restore database master from disk='c:\sqldbbackup\master.bak'
with move 'master' to 'e:\mssql7\data\master.mdf',
move 'mastlog' to 'f:\mssql7\log\mastlog.ldf',
replace
go
Get error message:
Server: Msg 3708, Level 16, State 4, Line 1
Cannot drop the database 'master' because it is a system database.
Server: Msg 3166, Level 16, State 1, Line 1
RESTORE DATABASE could not drop database 'master'. Drop the database and then reissue the RESTORE DATABASE statement.
How can I do with this error, try to drop the master db, but fail?
Thanks,
GuyangWhy do you want to restore the MASTER database?
Try REBUILDM utility to do so which is recommended method.|||restore master will keep the login information from previous database.
actually, we are doing a test for moving database from one location to another ( sql 7 database, Windows 2000 server).
when I do the restore, without the "with move" option, it is success.
but if add " with move " option, give me the error messsage above.
RebuildM is alternative option when master db crash, but we want to try restore master way here.
Any suggestion.
Thanks|||Do not attempt to move master database, just restore using RESTORE statement.
Any database users previously associated with logins that need to be re-created are orphaned because the login is lost. To associate an existing database user to a new SQL Server login, see sp_addlogin. To associate an existing database user with a Windows NT user, see sp_grantlogin.
sqlservr -c -m
in QA use command:
restore database master from disk='c:\sqldbbackup\master.bak'
with move 'master' to 'e:\mssql7\data\master.mdf',
move 'mastlog' to 'f:\mssql7\log\mastlog.ldf',
replace
go
Get error message:
Server: Msg 3708, Level 16, State 4, Line 1
Cannot drop the database 'master' because it is a system database.
Server: Msg 3166, Level 16, State 1, Line 1
RESTORE DATABASE could not drop database 'master'. Drop the database and then reissue the RESTORE DATABASE statement.
How can I do with this error, try to drop the master db, but fail?
Thanks,
GuyangWhy do you want to restore the MASTER database?
Try REBUILDM utility to do so which is recommended method.|||restore master will keep the login information from previous database.
actually, we are doing a test for moving database from one location to another ( sql 7 database, Windows 2000 server).
when I do the restore, without the "with move" option, it is success.
but if add " with move " option, give me the error messsage above.
RebuildM is alternative option when master db crash, but we want to try restore master way here.
Any suggestion.
Thanks|||Do not attempt to move master database, just restore using RESTORE statement.
Any database users previously associated with logins that need to be re-created are orphaned because the login is lost. To associate an existing database user to a new SQL Server login, see sp_addlogin. To associate an existing database user with a Windows NT user, see sp_grantlogin.
Subscribe to:
Posts (Atom)