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.
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

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

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

Restore user db'd on servers with different service pack levels

Is there any documentation on comaptibility related to restoring
databases backed up from server A to server B as it pertains to
editions and service pack levels?
For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
database, and vice versa?take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-
48ebca266659.htm
in your SQL 2005 books online
or
search sp_dbcmptlevel for SQL 2000
SQL Server is forward Compatable as a rule of thumb. Meaning you can
usually go from an older version of 2000 to a 2005. It does not support
backwards compatablity moving from 2005 to 2000.
So to answer your question. Yes you can move forward and restore onto the
newest service pack as long as these are USER databases and not system.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Jeff McAhren" <mcahren@.gmail.com> wrote in message
news:1158004530.422905.146510@.i3g2000cwc.googlegroups.com...
> Is there any documentation on comaptibility related to restoring
> databases backed up from server A to server B as it pertains to
> editions and service pack levels?
> For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
> database, and vice versa?
>

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!
Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!
|||Darn NT!!!

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
sql

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>

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.

restore to new hardware

We wil be upgrading all of our sql servers to new hardware and from Windows
2000 to 2003. The servers are in a merge replication topology.
We hope to keep the server names the same.
It would be an extreme hardship to drop the publications and recreate them.
Given these parameters, what is the best approach?
Do one last sync. Take the publisher offline, back up all databases to tape,
restore on the new server. Reboot. It should come up automagically.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CHeineken" <CHeineken@.discussions.microsoft.com> wrote in message
news:AA8B3EBA-C4C7-468B-AB23-27CFC725DB48@.microsoft.com...
> We wil be upgrading all of our sql servers to new hardware and from
> Windows
> 2000 to 2003. The servers are in a merge replication topology.
> We hope to keep the server names the same.
> It would be an extreme hardship to drop the publications and recreate
> them.
> Given these parameters, what is the best approach?
>
|||Just to be clear...
When you take the publisher offline, do you mean stop all agents?
When restoring to new server, here is the plan:
Install SQLServer with same service packs and hotfixes as default instance
on server with the same name as original.
Start SQLServer in single user mode.
Restore master and msdb
Start SQLServer in normal mode.
Restore Distribution, model, tempdb and user databases.
Is this correct? Am I missing anything?
"Hilary Cotter" wrote:

> Do one last sync. Take the publisher offline, back up all databases to tape,
> restore on the new server. Reboot. It should come up automagically.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "CHeineken" <CHeineken@.discussions.microsoft.com> wrote in message
> news:AA8B3EBA-C4C7-468B-AB23-27CFC725DB48@.microsoft.com...
>
>
|||Forget what I said about restoring the tempdb...brain fart.
"CHeineken" wrote:
[vbcol=seagreen]
> Just to be clear...
> When you take the publisher offline, do you mean stop all agents?
> When restoring to new server, here is the plan:
> Install SQLServer with same service packs and hotfixes as default instance
> on server with the same name as original.
> Start SQLServer in single user mode.
> Restore master and msdb
> Start SQLServer in normal mode.
> Restore Distribution, model, tempdb and user databases.
> Is this correct? Am I missing anything?
> "Hilary Cotter" wrote:
|||Yes, run the agents one last time until they stop or report no replicated
data. Then follow your steps and in the final restore of the user database
use the keep_replication switch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CHeineken" <CHeineken@.discussions.microsoft.com> wrote in message
news:81899ABF-321B-42E2-9370-5C7143B02E63@.microsoft.com...[vbcol=seagreen]
> Just to be clear...
> When you take the publisher offline, do you mean stop all agents?
> When restoring to new server, here is the plan:
> Install SQLServer with same service packs and hotfixes as default instance
> on server with the same name as original.
> Start SQLServer in single user mode.
> Restore master and msdb
> Start SQLServer in normal mode.
> Restore Distribution, model, tempdb and user databases.
> Is this correct? Am I missing anything?
> "Hilary Cotter" wrote:

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

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

Restore Master Problem

We are in the process of upgrading our servers. We tried to Restore Master
Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL 2000
SP3 on WIN 2003 Server SP1. The restore says its successful but the service
doesn't start after restoring. I am using local system account on both the
machines.
Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
same problem.
I would really appreciate if you could give me some suggestions.on the original server do you have anything unusual like
- tempdb in ram
- max server memory greater than what is available on new server.
- additional tempdb on drive which does not exist etc . .
- what errors do you get in error log?
- did sql start with -m option?
- if all fails you may have to try -f option - see BOL for more details
HTH
"Pradip" wrote:
> We are in the process of upgrading our servers. We tried to Restore Master
> Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL 2000
> SP3 on WIN 2003 Server SP1. The restore says its successful but the service
> doesn't start after restoring. I am using local system account on both the
> machines.
> Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
> to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
> same problem.
> I would really appreciate if you could give me some suggestions.|||1. Start SQL from the console ( command prompt) ie sqlservr.exe -m
2. Then do the restore from the database backup.
3. thern Ctrl-C the console, and restart SQL Serve..
What error messages are you getting?
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:C4E333AA-055D-4EDC-8271-9DF88AF68B9C@.microsoft.com...
> We are in the process of upgrading our servers. We tried to Restore Master
> Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL
2000
> SP3 on WIN 2003 Server SP1. The restore says its successful but the
service
> doesn't start after restoring. I am using local system account on both the
> machines.
> Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
> to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
> same problem.
> I would really appreciate if you could give me some suggestions.

Restore Master Problem

We are in the process of upgrading our servers. We tried to Restore Master
Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL 2000
SP3 on WIN 2003 Server SP1. The restore says its successful but the service
doesn't start after restoring. I am using local system account on both the
machines.
Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
same problem.
I would really appreciate if you could give me some suggestions.on the original server do you have anything unusual like
- tempdb in ram
- max server memory greater than what is available on new server.
- additional tempdb on drive which does not exist etc . .
- what errors do you get in error log?
- did sql start with -m option?
- if all fails you may have to try -f option - see BOL for more details
HTH
"Pradip" wrote:

> We are in the process of upgrading our servers. We tried to Restore Master
> Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL 20
00
> SP3 on WIN 2003 Server SP1. The restore says its successful but the servic
e
> doesn't start after restoring. I am using local system account on both the
> machines.
> Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
> to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
> same problem.
> I would really appreciate if you could give me some suggestions.|||1. Start SQL from the console ( command prompt) ie sqlservr.exe -m
2. Then do the restore from the database backup.
3. thern Ctrl-C the console, and restart SQL Serve..
What error messages are you getting?
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:C4E333AA-055D-4EDC-8271-9DF88AF68B9C@.microsoft.com...
> We are in the process of upgrading our servers. We tried to Restore Master
> Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL
2000
> SP3 on WIN 2003 Server SP1. The restore says its successful but the
service
> doesn't start after restoring. I am using local system account on both the
> machines.
> Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
> to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
> same problem.
> I would really appreciate if you could give me some suggestions.

Restore Master Problem

We are in the process of upgrading our servers. We tried to Restore Master
Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL 2000
SP3 on WIN 2003 Server SP1. The restore says its successful but the service
doesn't start after restoring. I am using local system account on both the
machines.
Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
same problem.
I would really appreciate if you could give me some suggestions.
on the original server do you have anything unusual like
- tempdb in ram
- max server memory greater than what is available on new server.
- additional tempdb on drive which does not exist etc . .
- what errors do you get in error log?
- did sql start with -m option?
- if all fails you may have to try -f option - see BOL for more details
HTH
"Pradip" wrote:

> We are in the process of upgrading our servers. We tried to Restore Master
> Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL 2000
> SP3 on WIN 2003 Server SP1. The restore says its successful but the service
> doesn't start after restoring. I am using local system account on both the
> machines.
> Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
> to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
> same problem.
> I would really appreciate if you could give me some suggestions.
|||1. Start SQL from the console ( command prompt) ie sqlservr.exe -m
2. Then do the restore from the database backup.
3. thern Ctrl-C the console, and restart SQL Serve..
What error messages are you getting?
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:C4E333AA-055D-4EDC-8271-9DF88AF68B9C@.microsoft.com...
> We are in the process of upgrading our servers. We tried to Restore Master
> Database from SQL 2000 SP3 on Win NT to a new test machine that has SQL
2000
> SP3 on WIN 2003 Server SP1. The restore says its successful but the
service
> doesn't start after restoring. I am using local system account on both the
> machines.
> Also I have tried to restore Master from SQL 2000 SP3 on WIN 2000 Prof SP4
> to the same machine (SQL 2000 sp3 on WIN 2003 server sp1) but ran into the
> same problem.
> I would really appreciate if you could give me some suggestions.

Restore master on an another disk !

Hi,

I have 2 servers SQL7SP3 on W4SerSP6a. On my first server all the system db (master, model, etc) are on the unit C:.
I want restore master on the unit D: on the second server. The restore with the -m option successful, but the restart of sqlserver failed because it can't find master, model etc in C:\mssql7\data.

How can I do to force SQL to start on the D:\mssaql7\data ?

thxQ1 How can I do to force SQL to start on the D:\mssaql7\data ?

A1 For a temp fix, use service startup parameters (in Sql 2k, and probably also 7 this would be -lD:\mssaql7\data\Mastlog.ldf -dlD:\mssaql7\data\Master.mdf)

A2 You may edit the paths in your server's registry for a permanent fix.

Restore Master Database - Permisssions?

I have 2 database servers, 1 @. production and 1 @. DR site.

To restore the master database to DR on a regular basis, I have to rely on either the DBA or SysAdm to perform the restore. It's pretty expensive, as they have to dedicate some time to do so.

I'm exploring the possibility of getting the Data Centre staff to do this instead, as they are stationed at the DR site. Please advise the SQL permisssions required to restore master database.

We are running SQL Server 2000.Its sa only.....and unless you trust the DR people, I would be reluctant to have the sa pasword widely known...