database problem: sp4

Showing posts with label sp4. Show all posts

Friday, March 30, 2012

Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch Smith
Hi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:

> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>
|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>
|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:

> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>
sql

Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time
a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:

> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 record
s
> and subsequently crash the machine they are running from. I was wondering
if
> I could limit the maximum number of rows to 10,000 via a database setting?
I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the vie
w
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:

> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
>

Restrict result rows with a database setting

I am using SQL Server 2000 SP4. Is it possible to limit the number of
returned rows in a query via some sort of database setting? We have a
customer table with over 100,000 records and are not able to modify the
application that queries this table.
If our users do not put in selection criteria, they get all 100,000 records
and subsequently crash the machine they are running from. I was wondering if
I could limit the maximum number of rows to 10,000 via a database setting? I
know I could do that via the SQL that is called, but I co not have control
over that.
Thanks for any suggestions,
Butch SmithHi
I guess you could try setting the configuration value of "query governor
cost limit" to a given value, but then it could have adverse effects else
where. You should look at getting the application changed to limit the time a
query takes and also making sure that an excessive number of records are not
returned.
John
"Butch" wrote:
> I am using SQL Server 2000 SP4. Is it possible to limit the number of
> returned rows in a query via some sort of database setting? We have a
> customer table with over 100,000 records and are not able to modify the
> application that queries this table.
> If our users do not put in selection criteria, they get all 100,000 records
> and subsequently crash the machine they are running from. I was wondering if
> I could limit the maximum number of rows to 10,000 via a database setting? I
> know I could do that via the SQL that is called, but I co not have control
> over that.
>
> Thanks for any suggestions,
> Butch Smith
>
>|||You could try query govenor to limit the time a query can run for.
sp_configure 'query governor cost limit', '300'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>I am using SQL Server 2000 SP4. Is it possible to limit the number of
>returned rows in a query via some sort of database setting? We have a
>customer table with over 100,000 records and are not able to modify the
>application that queries this table.
> If our users do not put in selection criteria, they get all 100,000
> records and subsequently crash the machine they are running from. I was
> wondering if I could limit the maximum number of rows to 10,000 via a
> database setting? I know I could do that via the SQL that is called, but I
> co not have control over that.
>
> Thanks for any suggestions,
> Butch Smith
>|||Maybe you can try to create a view with a select top of the table and rename
the table of 100000 records. Then create instead of triggers to make the view
updatable. But the application could crash with other things after that...
"Hilary Cotter" wrote:
> You could try query govenor to limit the time a query can run for.
> sp_configure 'query governor cost limit', '300'
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Butch" <smithb@.R3M0V3_CAPStntfireworks.com> wrote in message
> news:%238kiT4r8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> >I am using SQL Server 2000 SP4. Is it possible to limit the number of
> >returned rows in a query via some sort of database setting? We have a
> >customer table with over 100,000 records and are not able to modify the
> >application that queries this table.
> >
> > If our users do not put in selection criteria, they get all 100,000
> > records and subsequently crash the machine they are running from. I was
> > wondering if I could limit the maximum number of rows to 10,000 via a
> > database setting? I know I could do that via the SQL that is called, but I
> > co not have control over that.
> >
> >
> > Thanks for any suggestions,
> >
> > Butch Smith
> >
>
>

Tuesday, March 20, 2012

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connect
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see the
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to connect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
> > Hi,
> >
> > I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> > backup database msdb to disk ='c:\msdb.dat'
> >
> > then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> > norecovery
> >
> > The restore seem to succeed. But I get the following messages:
> >
> > Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> > Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> > Server: Msg 927, Level 14, State 2, Line 1
> > Database 'msdb' cannot be opened. It is in the middle of a restore.
> > Server: Msg 3009, Level 16, State 3, Line 1
> > Could not insert a backup or restore history/detail record in the msdb
> > database. This may indicate a problem with the msdb database. The
> > backup/restore operation was still successful.
> > RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> > MB/sec).
> >
> > These messages aren't generated with SQL2005, nor do you see the messages if
> > you specify 'with recovery' on the restore statement.
> >
> > I would be appreciative if someone could explain these messages. If I ignore
> > them, it is still possible to recover the database (restore database msdb
> > with recovery).
> >
> > Thanks, Howard
> >
> >
> >
> >
> >
> >|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode.
So you may still need to restore the transaction log to a PIT after restoring
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and this
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
possible. One could argue that SQL server would be smart enough to produce only a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
enough to warrant any major effort... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mode.
> So you may still need to restore the transaction log to a PIT after restoring
> the database. Also, I understand that the msdb was indeed restored despite
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and this
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:
> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
> possible. One could argue that SQL server would be smart enough to produce only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> > Hi Ben,
> >
> > Thanks for your quick reply. I did consider using 'with recovery' on the
> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
> > So you may still need to restore the transaction log to a PIT after restoring
> > the database. Also, I understand that the msdb was indeed restored despite
> > the error message. What I really want to know is what to make of the error
> > message as I work with a program that deals with other people's data and this
> > type of message makes them nervous.
> >
> > Thanks again, Howard
> >
> >
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the update. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
>> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing
>> isn't
>> possible. One could argue that SQL server would be smart enough to produce only a warning or
>> similar
>> in these situations, of course. Perhaps you want to file an entry at
>> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
>> enough to warrant any major effort... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Howard" <Howard@.discussions.microsoft.com> wrote in message
>> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>> > Hi Ben,
>> >
>> > Thanks for your quick reply. I did consider using 'with recovery' on the
>> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
>> > So you may still need to restore the transaction log to a PIT after restoring
>> > the database. Also, I understand that the msdb was indeed restored despite
>> > the error message. What I really want to know is what to make of the error
>> > message as I work with a program that deals with other people's data and this
>> > type of message makes them nervous.
>> >
>> > Thanks again, Howard
>> >
>> >
>>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connec
t
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:

> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I igno
re
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see t
he
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to conn
ect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
>|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode
.
So you may still need to restore the transaction log to a PIT after restorin
g
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and thi
s
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the rest
ore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore
history writing isn't
possible. One could argue that SQL server would be smart enough to produce o
nly a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find t
his happening often
enough to warrant any major effort... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mo
de.
> So you may still need to restore the transaction log to a PIT after restor
ing
> the database. Also, I understand that the msdb was indeed restored despit
e
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and t
his
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and tr
y
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:

> This is a kind of catch-22 situation. Each RESTORE want to write to the re
store history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that resto
re history writing isn't
> possible. One could argue that SQL server would be smart enough to produce
only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find
this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the
update. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...[vbcol=seagreen]
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and
try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>

Monday, March 12, 2012

Restoring master and msdb on a different server

Hi
I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
Server 2003 SP1 and MS SQL Server 2000 SP3.
The SQL Server on the weaker machine has affinity mask turned on to use
only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
located on drive E:.
The SQL Server on the stronger machine has master and msdb located on
drive C:, affinity mask set off, and dynamic memory allocation set on.
Can I restore the master database from the weaker machine on the stronger
machine and is it a good idea?
Many thanks,
OskarCan you do it? Sure. Is it a good idea or not? That depends on why you
would want to do it. What are you trying to accomplish?
Also, the weaker machine, you said, is only a 2-way CPU box, why would you
have a 2 CPU affinity set on it?
Sincerely,
Anthony Thomas
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
Hi
I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
Server 2003 SP1 and MS SQL Server 2000 SP3.
The SQL Server on the weaker machine has affinity mask turned on to use
only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
located on drive E:.
The SQL Server on the stronger machine has master and msdb located on
drive C:, affinity mask set off, and dynamic memory allocation set on.
Can I restore the master database from the weaker machine on the stronger
machine and is it a good idea?
Many thanks,
Oskar|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manne
r.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and i
t
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>

Restoring master and msdb on a different server

Hi
I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
Server 2003 SP1 and MS SQL Server 2000 SP3.
The SQL Server on the weaker machine has affinity mask turned on to use
only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
located on drive E:.
The SQL Server on the stronger machine has master and msdb located on
drive C:, affinity mask set off, and dynamic memory allocation set on.
Can I restore the master database from the weaker machine on the stronger
machine and is it a good idea?
Many thanks,
Oskar
Can you do it? Sure. Is it a good idea or not? That depends on why you
would want to do it. What are you trying to accomplish?
Also, the weaker machine, you said, is only a 2-way CPU box, why would you
have a 2 CPU affinity set on it?
Sincerely,
Anthony Thomas

"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
Hi
I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
Server 2003 SP1 and MS SQL Server 2000 SP3.
The SQL Server on the weaker machine has affinity mask turned on to use
only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
located on drive E:.
The SQL Server on the stronger machine has master and msdb located on
drive C:, affinity mask set off, and dynamic memory allocation set on.
Can I restore the master database from the weaker machine on the stronger
machine and is it a good idea?
Many thanks,
Oskar
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>
|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
Many thanks,
Oskar
"Anthony Thomas" wrote:

> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>

Friday, March 9, 2012

Restoring master and msdb on a different server

Hi
I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
Server 2003 SP1 and MS SQL Server 2000 SP3.
The SQL Server on the weaker machine has affinity mask turned on to use
only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
located on drive E:.
The SQL Server on the stronger machine has master and msdb located on
drive C:, affinity mask set off, and dynamic memory allocation set on.
Can I restore the master database from the weaker machine on the stronger
machine and is it a good idea?
--
Many thanks,
OskarCan you do it? Sure. Is it a good idea or not? That depends on why you
would want to do it. What are you trying to accomplish?
Also, the weaker machine, you said, is only a 2-way CPU box, why would you
have a 2 CPU affinity set on it?
Sincerely,
Anthony Thomas
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
Hi
I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
Server 2003 SP1 and MS SQL Server 2000 SP3.
The SQL Server on the weaker machine has affinity mask turned on to use
only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
located on drive E:.
The SQL Server on the stronger machine has master and msdb located on
drive C:, affinity mask set off, and dynamic memory allocation set on.
Can I restore the master database from the weaker machine on the stronger
machine and is it a good idea?
--
Many thanks,
Oskar|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>|||I was hoping that this would be an easier way to migrate the SQL Server
information (e.g. logins, jobs etc.) from the weaker server to the stronger
one. But then when I started to think of moving the data files of master and
msdb databases, I wasn't sure if I that would be possible in the usual manner.
I have the affinity set on the weaker server so that only one logical CPU on
each of the physical Xeon CPUs employed. This is because the Windows 2000
Server can't tell the difference between the logical and physical CPUs and it
can assign an additinal thread to a busy CPU.
By now I have already accomplished the migration using the script transfer
approach.
--
Many thanks,
Oskar
"Anthony Thomas" wrote:
> Can you do it? Sure. Is it a good idea or not? That depends on why you
> would want to do it. What are you trying to accomplish?
> Also, the weaker machine, you said, is only a 2-way CPU box, why would you
> have a 2 CPU affinity set on it?
> Sincerely,
>
> Anthony Thomas
>
> --
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:5DEC7960-CE31-4F0F-815F-E7B75BD3CFA4@.microsoft.com...
> Hi
> I have two machines one of which is 2 CPU, 3.5 GB RAM, HP Proliant DL 380
> G3 with MS Windows 2000 Advanced Server SP4 and MS SQL Server 2000 SP3 on
> it, the other - 4 CPU, 4 GB RAM, HP Proliant DL 580 G3 with MS Windows
> Server 2003 SP1 and MS SQL Server 2000 SP3.
> The SQL Server on the weaker machine has affinity mask turned on to use
> only 2 CPUs, dynamic memory allocation set on, master, and msdb databases
> located on drive E:.
> The SQL Server on the stronger machine has master and msdb located on
> drive C:, affinity mask set off, and dynamic memory allocation set on.
> Can I restore the master database from the weaker machine on the stronger
> machine and is it a good idea?
> --
> Many thanks,
> Oskar
>
>

Saturday, February 25, 2012

Restoring databases on SQL Server 2000 while trying not to restore to the wrong database

Experts,
Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a couple
of development teams using databases on the same SQL Server. While they can
backup their databases, they cannot restore their own databases. I have
noticed that the SQL server role overwrite the DB roles on each user
database. Users have to have DB creator permission in order to restore the
existing database. However, this role also grants users to ability to
restore DB to other users database's. Is there any way around this, so that
I could give my development users rights to restore THEIR OWN database but
not right to restore database created by someone else?
--
SpinHi
You could do this by letting them have their own instance, but then you may
as well get copies of developer edition for them!
Why do you not trust them?
John
"Spin" wrote:
> Experts,
> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a couple
> of development teams using databases on the same SQL Server. While they can
> backup their databases, they cannot restore their own databases. I have
> noticed that the SQL server role overwrite the DB roles on each user
> database. Users have to have DB creator permission in order to restore the
> existing database. However, this role also grants users to ability to
> restore DB to other users database's. Is there any way around this, so that
> I could give my development users rights to restore THEIR OWN database but
> not right to restore database created by someone else?
> --
> Spin
>
>|||I do not want them accidentally restoring onto someone else's database.
--
Spin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:30C46FD5-B979-4B35-AEAE-0B6885F9EA57@.microsoft.com...
> Hi
> You could do this by letting them have their own instance, but then you
> may
> as well get copies of developer edition for them!
> Why do you not trust them?
> John
>
> "Spin" wrote:
>> Experts,
>> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a
>> couple
>> of development teams using databases on the same SQL Server. While they
>> can
>> backup their databases, they cannot restore their own databases. I have
>> noticed that the SQL server role overwrite the DB roles on each user
>> database. Users have to have DB creator permission in order to restore
>> the
>> existing database. However, this role also grants users to ability to
>> restore DB to other users database's. Is there any way around this, so
>> that
>> I could give my development users rights to restore THEIR OWN database
>> but
>> not right to restore database created by someone else?
>> --
>> Spin
>>|||Hi
Proper use of a source code contol system, and a regular backup regime for
you development server would reduce the potential for loss if this does
occur. Maybe you should allocate the privileges to more senior members of the
team only?
John
"Spin" wrote:
> I do not want them accidentally restoring onto someone else's database.
> --
> Spin
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:30C46FD5-B979-4B35-AEAE-0B6885F9EA57@.microsoft.com...
> > Hi
> >
> > You could do this by letting them have their own instance, but then you
> > may
> > as well get copies of developer edition for them!
> >
> > Why do you not trust them?
> >
> > John
> >
> >
> > "Spin" wrote:
> >
> >> Experts,
> >>
> >> Running Windows Server 2003 SP1 with SQL Server 2000 SP4. I have a
> >> couple
> >> of development teams using databases on the same SQL Server. While they
> >> can
> >> backup their databases, they cannot restore their own databases. I have
> >> noticed that the SQL server role overwrite the DB roles on each user
> >> database. Users have to have DB creator permission in order to restore
> >> the
> >> existing database. However, this role also grants users to ability to
> >> restore DB to other users database's. Is there any way around this, so
> >> that
> >> I could give my development users rights to restore THEIR OWN database
> >> but
> >> not right to restore database created by someone else?
> >>
> >> --
> >> Spin
> >>
> >>
> >>
>
>

Friday, March 30, 2012

Restrict result rows with a database setting

Restrict result rows with a database setting

Restrict result rows with a database setting

Tuesday, March 20, 2012

restoring msdb with SQL 2000

restoring msdb with SQL 2000

restoring msdb with SQL 2000

Monday, March 12, 2012

Restoring master and msdb on a different server

Restoring master and msdb on a different server

Friday, March 9, 2012

Restoring master and msdb on a different server

Saturday, February 25, 2012

Restoring databases on SQL Server 2000 while trying not to restore to the wrong database

database problem

Blog Archive

About Me