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.
Showing posts with label installed. Show all posts
Showing posts with label installed. Show all posts
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.|||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 9, 2012
Restore only few tables of the database
HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the">
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using
triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to
separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a fi
legroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as
you probably do
structural changes for these tables and structural changes affects the syste
m tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let
you restore a database
so different parts of the database are from different points in time. This w
ould be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make
sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clie
nts execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and w
ill give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.g
bl...
> HI
> I'm designing a database. This database will be installed in a few servers
.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity betwee
n
> installations (Replication isn't relevant). What we thougth to do is one o
f
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only th
e
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the">
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using
triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to
separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a fi
legroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as
you probably do
structural changes for these tables and structural changes affects the syste
m tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let
you restore a database
so different parts of the database are from different points in time. This w
ould be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make
sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clie
nts execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and w
ill give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.g
bl...
> HI
> I'm designing a database. This database will be installed in a few servers
.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity betwee
n
> installations (Replication isn't relevant). What we thougth to do is one o
f
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only th
e
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael
Restore only few tables of the database
HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a filegroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as you probably do
structural changes for these tables and structural changes affects the system tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let you restore a database
so different parts of the database are from different points in time. This would be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clients execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and will give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity between
> installations (Replication isn't relevant). What we thougth to do is one of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a filegroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as you probably do
structural changes for these tables and structural changes affects the system tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let you restore a database
so different parts of the database are from different points in time. This would be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clients execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and will give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity between
> installations (Replication isn't relevant). What we thougth to do is one of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael
Wednesday, March 7, 2012
Restore Newbie
I am trying to install and run a prototype VB.net application for review
I have desktop edition of SQL2K installed and running.
I have a .bak file that I need to load for the applications data
How do I do restore?
I've seen posts where one runs the restore command, but I cannot find an executable called restore.exe?Allan,
It's a T-SQL command.
RESTORE DATABASE.
Or, you can do it from Enterprise Manager.
Either way, look at RESTORE DATABASE in Books Online.
James Hokes
"Allan" <anonymous@.discussions.microsoft.com> wrote in message
news:E467A676-703B-4636-BAE0-19B4BB6DD55B@.microsoft.com...
> I am trying to install and run a prototype VB.net application for review
> I have desktop edition of SQL2K installed and running.
> I have a .bak file that I need to load for the applications data
> How do I do restore?
> I've seen posts where one runs the restore command, but I cannot find an
executable called restore.exe?
>
>
I have desktop edition of SQL2K installed and running.
I have a .bak file that I need to load for the applications data
How do I do restore?
I've seen posts where one runs the restore command, but I cannot find an executable called restore.exe?Allan,
It's a T-SQL command.
RESTORE DATABASE.
Or, you can do it from Enterprise Manager.
Either way, look at RESTORE DATABASE in Books Online.
James Hokes
"Allan" <anonymous@.discussions.microsoft.com> wrote in message
news:E467A676-703B-4636-BAE0-19B4BB6DD55B@.microsoft.com...
> I am trying to install and run a prototype VB.net application for review
> I have desktop edition of SQL2K installed and running.
> I have a .bak file that I need to load for the applications data
> How do I do restore?
> I've seen posts where one runs the restore command, but I cannot find an
executable called restore.exe?
>
>
Subscribe to:
Posts (Atom)