DB2, SQL Server, and Oracle

Hello all, I am curious to know if anyone has done a trade study on DB2, SQL Server, and Oracle databases (all Enterprise editions). Basically I am trying to identify which one to chose based on "Ease of implementation", Ease of maintenance", "Pricing" and "Ease of technical support". The criteria I am looking at can be obtained by interviewing people who have worked with these products. I appreciate any input I can get. Thanks, Shaunplease do not cross-post

http://www.dbforums.com/t1101738.htmlsql

db_owner VS db_ddladmin roles

We have several departamental "database administrators" that needs access to their databases "only" and cannot perform maintenance tasks administrative tasks such as backup and create new server login. We basically function as a "database hosting services" to these departamental dbsa. I granted rights to these departamental dbas to their database and I assigned the db_ddladmin role to them. They can create the objects within their database but they cannot read the records because when the table was created it belongs to the dbo schema - I don't want to assign them to the db_owner role, this role is much more permission that they need.

My question is: What is the best way to give these departamental dbas rights to manage their databases without having too much permission to maintain the database permission and settings?

You need to provide more information.

Please list the actions you wish to allow, and the actions you wish to prohibit

Then we may be able to help you determine the proper mix of roles.

|||

If you are using SQL 2005 then you can take help of EXECUTE AS and audit the events to ensure they are not misusing the privilege.

All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement and also check BOL for SQL 2005 for more information.

|||

The Departmental DBAs should be able to:

-Create, select, modify any objects in the database they have rights to.

-Give permission to users (such as developers that work under them) to access some objects that the departmental dbas own.

Basically they should be able to do anything needed in the database they own.

The Departmental DBAs should NOT be able to:

Create, shrink, backup databases

Basically they should not be able to change any database structure, size or settings.

BTW Do you know if there is a way for them to "see" only their database under Mngmt Studio?

thanks again

|||

In order to accomplish your goal, you would benenfit from a good understanding of how SQL 2005 uses Schemas. I suggest that you start by referring to Books Online, Topic: User-Schema Separation.

I think that by properly creating a schema, and granting your departmental dbas ownership of that schema, and then having ALL objects belong to that schema, you will be able to set this up as you want.

Using the built-in database roles, including db_owner, does NOT accomplish your goal, since the db_owner can see other databases, and even delete their database.

sql

db_owner

Hello. What would be the best role assignment for a temporary consultant to
access one of our user created databases on our sql2k server to allow this
person to develop freely but only within this database and not any of our
other databases including the obvious system databases? Would it be ok to
give this consultant a windows domain login and assign it as a db_owner or
would assigning a combination of the other system roles within this db be
better?
Thanks in advance.Yes, db_owner will pretty much give the consultant full control but only
within that database.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hello. What would be the best role assignment for a temporary consultant
> to access one of our user created databases on our sql2k server to allow
> this person to develop freely but only within this database and not any of
> our other databases including the obvious system databases? Would it be
> ok to give this consultant a windows domain login and assign it as a
> db_owner or would assigning a combination of the other system roles within
> this db be better?
> Thanks in advance.
>|||Thanks for your speedy confirmation Dan. Much appreciated :-)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
> Yes, db_owner will pretty much give the consultant full control but only
> within that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||I just created a Windows domain user something like 'DomainAUser\JoeTest'
and assigned him to the db_owner role to one of our user created databases.
When I set up an odbc or .udl using this Windows domain user credentials how
come this user is able to see our system databases and a couple other in the
default database drop down box which kind of is concerning.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
> Yes, db_owner will pretty much give the consultant full control but only
> within that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||All logins can access databases with the guest user enabled. This includes
sample and system databases but permissions in the system databases are
minimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:O0%23a8Y5NIHA.3852@.TK2MSFTNGP06.phx.gbl...
>I just created a Windows domain user something like 'DomainAUser\JoeTest'
>and assigned him to the db_owner role to one of our user created databases.
>When I set up an odbc or .udl using this Windows domain user credentials
>how come this user is able to see our system databases and a couple other
>in the default database drop down box which kind of is concerning.
> Thanks.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
>|||Is there a way to disable all of the 'guest' accounts in all of the system
and user databases since I noticed this Microsoft article recommends not to
remove the 'guest' account
http://support.microsoft.com/default.aspx/kb/315523
Thanks Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:740E18FB-57E3-4F74-84B4-8327509943F9@.microsoft.com...
> All logins can access databases with the guest user enabled. This
> includes sample and system databases but permissions in the system
> databases are minimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:O0%23a8Y5NIHA.3852@.TK2MSFTNGP06.phx.gbl...
>|||> Is there a way to disable all of the 'guest' accounts in all of the system
> and user databases since I noticed this Microsoft article recommends not
> to remove the 'guest' account
> http://support.microsoft.com/default.aspx/kb/315523
There is only one guest user ("guest") in the system databases, which is
required for proper operation. The guest user inherits only minimal
permissions from the public role so permissions are quite limited in master
and tempdb.
You might consider revoking public execute permissions on the msdb database
sp_add_job and sp_add_dtspackage if you want to prevent non-sysadmins from
creating jobs or saving DTS packages in msdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:Ozqp05DOIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Is there a way to disable all of the 'guest' accounts in all of the system
> and user databases since I noticed this Microsoft article recommends not
> to remove the 'guest' account
> http://support.microsoft.com/default.aspx/kb/315523
> Thanks Dan.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:740E18FB-57E3-4F74-84B4-8327509943F9@.microsoft.com...
>|||Interesting. Thanks Dan, much appreciated. Take cares.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4266998A-E270-473E-9D98-3A44908D1891@.microsoft.com...
> There is only one guest user ("guest") in the system databases, which is
> required for proper operation. The guest user inherits only minimal
> permissions from the public role so permissions are quite limited in
> master and tempdb.
> You might consider revoking public execute permissions on the msdb
> database sp_add_job and sp_add_dtspackage if you want to prevent
> non-sysadmins from creating jobs or saving DTS packages in msdb.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:Ozqp05DOIHA.1208@.TK2MSFTNGP05.phx.gbl...
>sql

db_datareader role**

Hi
I defined a new login"login1" in SQL Server 2000 and
tried to make an access(public+db_datareader)
to one of databases called "db1" successfully.
then in query analyzer I logined by "login1" and
saw the name of more than one databases in left window
,there were ("master","tempdb","distribution",
"db1","northwind","pubs","msdb"),and when I tried to open them ,for
example I selected "tempdb" and
then clicked the right key of mouse on a view
called "dbo.sysconstraints" and it opened successfully,why? I want my user
just read the information of one database called "db1"!!!!
second question: why didn't appear other databases
in left window of query analyzer?
3th question ,what's the best selection to access
"login1" just reading the information(just select statement) stored in
"db1"?(for example:
is it correct to be member of public role and then
check the check boxes of select column in permission
section of all tables in "db1" one by one!?)
any help would be greatly appreciated.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/A login can access a database only if the login is a user in the database or
the guest user is enabled. Since any login can access a database containing
the guest user, the other databases listed must have the guest user enabled
and this is why 'Login1' can access those databases even without a database
userid. Note that the guest user is required in the master and tempdb
system databases but can be removed from other databases if you don't want
all logins to have access to those databases.

> I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why
The public role has SELECT permissions on system tables and views. This is
needed in order to retrieve meta-data needed by database access APIs.

> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
Adding the user to only the db_datareader role will provide SELECT
permissions on tables and views. If you need more granular permissions, you
can create your own database role and grant the desired permissions to the
role. This allows you to control user database permissions via role
membership without granting direct permissions to individual users.
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4uk7scihqligo@.msnews.microsoft.com...
> Hi
> I defined a new login"login1" in SQL Server 2000 and
> tried to make an access(public+db_datareader)
> to one of databases called "db1" successfully.
> then in query analyzer I logined by "login1" and
> saw the name of more than one databases in left window
> ,there were ("master","tempdb","distribution",
> "db1","northwind","pubs","msdb"),and when I tried to open them ,for
> example I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why? I want my user
> just read the information of one database called "db1"!!!!
> second question: why didn't appear other databases
> in left window of query analyzer?
> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
> any help would be greatly appreciated.
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||How can I recognize a guest user in a database?
On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:

> A login can access a database only if the login is a user in the
> database or
> the guest user is enabled. Since any login can access a database
> containing
> the guest user, the other databases listed must have the guest user
> enabled
> and this is why 'Login1' can access those databases even without a
> database
> userid. Note that the guest user is required in the master and tempdb
> system databases but can be removed from other databases if you don't
> want
> all logins to have access to those databases.
>
> The public role has SELECT permissions on system tables and views. This
> is
> needed in order to retrieve meta-data needed by database access APIs.
>
> Adding the user to only the db_datareader role will provide SELECT
> permissions on tables and views. If you need more granular permissions,
> you
> can create your own database role and grant the desired permissions to
> the
> role. This allows you to control user database permissions via role
> membership without granting direct permissions to individual users.
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> How can I recognize a guest user in a database?
To see if the guest user is enabled in a particular database:
USE MyDatabase
sp_helpuser 'guest'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4vwbsg8hqligo@.msnews.microsoft.com...
> How can I recognize a guest user in a database?
> On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
>
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?
This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:

> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>
|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:

> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:

DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep(
):
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep(
):
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removin
g
SQLData from the script resolved the problem.
"David Grant" wrote:

> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. W
e
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Slee
p():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Slee
p():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the databa
se
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so remov
ing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:

> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>

DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> > This problem was caused by Veritas Backup Exec trying to backup the database
> > files (which were obviously in use!). The scripts used to include the
> > SQLBackup folder but had been changed to include SQLData as well, so removing
> > SQLData from the script resolved the problem.
> >
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>> David Grant wrote:
>> > This problem was caused by Veritas Backup Exec trying to backup the database
>> > files (which were obviously in use!). The scripts used to include the
>> > SQLBackup folder but had been changed to include SQLData as well, so removing
>> > SQLData from the script resolved the problem.
>> >
>> Veritas shouldn't have been able to take the databases offline like
>> this, the data files should have been "in use" by SQL at the time. The
>> only way I can picture this happening is if you have "Auto-Close"
>> enabled on your databases. If SQL "closed" the database, Veritas comes
>> along and starts backing up the data file, and SQL tries to "open" the
>> database again, it's going to fail. You shouldn't use the AutoClose or
>> AutoShrink options on a production database.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com

DB Stuck on (Loading) After Restore

We are using MS SQL Server 2000.
I am not a proficient SQL user.
I have created many databases based on a set of instructions from a former
consultant. This involves doing a restore from a SQL backup (to disk) of an
existing "shell" database and "restore as" a new database name. When
selecting options for the restore, I select "leave database operational."
Yesterday when I tried to do this, the progress bar went all the way across,
but then an error popped up at the end that said:
The log in this backup set begins at LSN 30200000 blah blah blah which is
too late to apply to the database. An earlier log backup that includes
30020202020 can be restored. RESTORE LOG is terminating abnormally.
In enterprise manager, my new database appears, but it is grayed out with
the word (loading) next to it.
I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
have no idea where or how to do this, or even if this is the right thing to
do.
Would someone point me in the right direction?
Thank youmike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
I
> have no idea where or how to do this, or even if this is the right thing t
o
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
I
> have no idea where or how to do this, or even if this is the right thing t
o
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||Thank you both|||Thank you both

DB Stuck on (Loading) After Restore

We are using MS SQL Server 2000.
I am not a proficient SQL user.
I have created many databases based on a set of instructions from a former
consultant. This involves doing a restore from a SQL backup (to disk) of an
existing "shell" database and "restore as" a new database name. When
selecting options for the restore, I select "leave database operational."
Yesterday when I tried to do this, the progress bar went all the way across,
but then an error popped up at the end that said:
The log in this backup set begins at LSN 30200000 blah blah blah which is
too late to apply to the database. An earlier log backup that includes
30020202020 can be restored. RESTORE LOG is terminating abnormally.
In enterprise manager, my new database appears, but it is grayed out with
the word (loading) next to it.
I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
have no idea where or how to do this, or even if this is the right thing to
do.
Would someone point me in the right direction?
Thank youmike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
> have no idea where or how to do this, or even if this is the right thing to
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||Thank you both

db server slow - SET TRANSACTION LEVEL READ COMMITTED causing it?

Our entire server is extremely slow right now. We are getting locks left
and right. We've got locks on databases that we don't even use. When we
look at the activity and the locks that are taking the longest, we notice
that this statement is occurring over and over again:
SET TRANSACTION LEVEL READ COMMITTED
This is confusing to me because I thought this was the default? So I guess
I thought that this was being internally executed, or the equivalent, all
along. So, I don't know what to do or what to look at. Is there a setting
that could have been changed that would cause this behavior?
thanks,
Cory
Cory
> This is confusing to me because I thought this was the default?
Yes , it is
Do you have set SET TRANSACTION LEVEL READ COMMITTED in all stored
procedures. Its per connection ?
Try running SQL Server Profiler to see what is going on
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uRW4%2370LHHA.536@.TK2MSFTNGP02.phx.gbl...
> Our entire server is extremely slow right now. We are getting locks left
> and right. We've got locks on databases that we don't even use. When we
> look at the activity and the locks that are taking the longest, we notice
> that this statement is occurring over and over again:
> SET TRANSACTION LEVEL READ COMMITTED
> This is confusing to me because I thought this was the default? So I
> guess I thought that this was being internally executed, or the
> equivalent, all along. So, I don't know what to do or what to look at.
> Is there a setting that could have been changed that would cause this
> behavior?
>
> thanks,
> Cory
>
>

db server slow - SET TRANSACTION LEVEL READ COMMITTED causing it?

Our entire server is extremely slow right now. We are getting locks left
and right. We've got locks on databases that we don't even use. When we
look at the activity and the locks that are taking the longest, we notice
that this statement is occurring over and over again:
SET TRANSACTION LEVEL READ COMMITTED
This is confusing to me because I thought this was the default? So I guess
I thought that this was being internally executed, or the equivalent, all
along. So, I don't know what to do or what to look at. Is there a setting
that could have been changed that would cause this behavior?
thanks,
CoryCory
> This is confusing to me because I thought this was the default?
Yes , it is
Do you have set SET TRANSACTION LEVEL READ COMMITTED in all stored
procedures. Its per connection ?
Try running SQL Server Profiler to see what is going on
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uRW4%2370LHHA.536@.TK2MSFTNGP02.phx.gbl...
> Our entire server is extremely slow right now. We are getting locks left
> and right. We've got locks on databases that we don't even use. When we
> look at the activity and the locks that are taking the longest, we notice
> that this statement is occurring over and over again:
> SET TRANSACTION LEVEL READ COMMITTED
> This is confusing to me because I thought this was the default? So I
> guess I thought that this was being internally executed, or the
> equivalent, all along. So, I don't know what to do or what to look at.
> Is there a setting that could have been changed that would cause this
> behavior?
>
> thanks,
> Cory
>
>

db server slow - SET TRANSACTION LEVEL READ COMMITTED causing it?

Our entire server is extremely slow right now. We are getting locks left
and right. We've got locks on databases that we don't even use. When we
look at the activity and the locks that are taking the longest, we notice
that this statement is occurring over and over again:
SET TRANSACTION LEVEL READ COMMITTED
This is confusing to me because I thought this was the default? So I guess
I thought that this was being internally executed, or the equivalent, all
along. So, I don't know what to do or what to look at. Is there a setting
that could have been changed that would cause this behavior?
thanks,
CoryCory
> This is confusing to me because I thought this was the default?
Yes , it is
Do you have set SET TRANSACTION LEVEL READ COMMITTED in all stored
procedures. Its per connection ?
Try running SQL Server Profiler to see what is going on
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:uRW4%2370LHHA.536@.TK2MSFTNGP02.phx.gbl...
> Our entire server is extremely slow right now. We are getting locks left
> and right. We've got locks on databases that we don't even use. When we
> look at the activity and the locks that are taking the longest, we notice
> that this statement is occurring over and over again:
> SET TRANSACTION LEVEL READ COMMITTED
> This is confusing to me because I thought this was the default? So I
> guess I thought that this was being internally executed, or the
> equivalent, all along. So, I don't know what to do or what to look at.
> Is there a setting that could have been changed that would cause this
> behavior?
>
> thanks,
> Cory
>
>sql

DB restore with Full Text Catalogs

I have what I believe to be a bizarre scenario.
I have a series of databases that I've backed up and restored onto another
machine.
However, the full text catalogues were not included in the backup...should
they have been?
I see that the catalogues are actually stored in the folder
MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
data across or does it need to be rebuilt locally?
I ask because I am creating a nightly job to backup the databases on
serverA, copy them to serverB and then restore them. I could use ROBOCOPY
to copy the contents of /MSSQL/FTDATA/ too if required.
Thanks
Griffhave a look at this kb article for more information on restored full text
catalogs with databases
http://support.microsoft.com/default.aspx?scid=kb;en-us;240867
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:#0iD5B6#DHA.2804@.tk2msftngp13.phx.gbl...
> I have what I believe to be a bizarre scenario.
> I have a series of databases that I've backed up and restored onto another
> machine.
> However, the full text catalogues were not included in the backup...should
> they have been?
> I see that the catalogues are actually stored in the folder
> MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
> data across or does it need to be rebuilt locally?
> I ask because I am creating a nightly job to backup the databases on
> serverA, copy them to serverB and then restore them. I could use ROBOCOPY
> to copy the contents of /MSSQL/FTDATA/ too if required.
> Thanks
> Griff
>

DB restore with Full Text Catalogs

I have what I believe to be a bizarre scenario.
I have a series of databases that I've backed up and restored onto another
machine.
However, the full text catalogues were not included in the backup...should
they have been?
I see that the catalogues are actually stored in the folder
MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
data across or does it need to be rebuilt locally?
I ask because I am creating a nightly job to backup the databases on
serverA, copy them to serverB and then restore them. I could use ROBOCOPY
to copy the contents of /MSSQL/FTDATA/ too if required.
Thanks
Griffhave a look at this kb article for more information on restored full text
catalogs with databases
http://support.microsoft.com/defaul...kb;en-us;240867
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:#0iD5B6#DHA.2804@.tk2msftngp13.phx.gbl...
> I have what I believe to be a bizarre scenario.
> I have a series of databases that I've backed up and restored onto another
> machine.
> However, the full text catalogues were not included in the backup...should
> they have been?
> I see that the catalogues are actually stored in the folder
> MSSQL/FTDATA/ - would it have been sufficient to have simply copies this
> data across or does it need to be rebuilt locally?
> I ask because I am creating a nightly job to backup the databases on
> serverA, copy them to serverB and then restore them. I could use ROBOCOPY
> to copy the contents of /MSSQL/FTDATA/ too if required.
> Thanks
> Griff
>

DB restore error to new server

Hi,
We have just built a new Windows 2003 Std Ed, SP1 server with SQL
Server 2005 no SP.
We are attempting to restore the databases from an old server to this
new one, however after the restore process gets to 100% we recive the
following error:
SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
error log or system event log may provide more detail. This is a severe
error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB)
Does anybody know what might be the problem?
Regards
Paul RobertsBob wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>
Sounds like a corrupt backup. That error is occurring as SQL tries to
"recover" the new database to bring it online. It's telling you that
database has an internal problem of some kind. I would run a new backup
of the original database to restore from. Barring that, use DBCC
CHECKDB to attempt a repair of the restored database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
We tried that too with no luck.
This database is from a replicated database being restored to a
non-replication system.|||Bob wrote:
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>
Tried the new backup, or tried running DBCC? If the new backup gave the
same error, you might want to run DBCC CHECKDB against the original
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The RESTORE command has a CONTINUE_AFTER_ERROR option. That should at lest g
et the database there,
so you can do something with it (like last resort emergency mode). But you s
hould really CHECKDB the
source database. If that db is clean, you shouldn't get corrupted backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob" <pmroberts@.talk21.com> wrote in message
news:1152811634.990074.129800@.p79g2000cwp.googlegroups.com...
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>|||2 questions -
1. How are you copying over the .bak file between the 2 machines?
2. Is the file sie the same?
"Bob" wrote:

> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>|||Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?|||How do I use DBCC CHECKDB?|||Bob wrote:
> Hi,
> The files are being copied using windows drag and drop.
> The file sizes are the same.
> I am using SQL server management studio, I cannot see the RESTORE
> command has a CONTINUE_AFTER_ERROR, I tried this option under the
> backup option but with no luck.
> I also tried new backups.
> Is there a limit to the size of the backups?
>
You might need to do the restore with the sql command in order to
specify the CONTINUE AFTER ERROR option.
Try to look up RESTORE Database in Books On Line.
Also, have you tried to restore the files to a different drive? It might
not be the case, but you could have a bad spot on the disk and that's
why it fails (..I know it's a wild guess but it should be fairly easy to
try...).
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hi,
Problem solved.
The issue is due to the fact that they are replication databases.
Since I am restoring to a different server, without replication, the
SQL server is supposed to realise this and remove replication status,
however it does not and you have to remove this manually with the
command: sp_removedbreplication dbname
You can then bring the database online.
See http://msdn2.microsoft.com/en-us/library/ms151782(d=ide).aspx
for more information.

DB restore error to new server

Hi,
We have just built a new Windows 2003 Std Ed, SP1 server with SQL
Server 2005 no SP.
We are attempting to restore the databases from an old server to this
new one, however after the restore process gets to 100% we recive the
following error:
SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
error log or system event log may provide more detail. This is a severe
error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB)
Does anybody know what might be the problem?
Regards
Paul RobertsBob wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>
Sounds like a corrupt backup. That error is occurring as SQL tries to
"recover" the new database to bring it online. It's telling you that
database has an internal problem of some kind. I would run a new backup
of the original database to restore from. Barring that, use DBCC
CHECKDB to attempt a repair of the restored database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
We tried that too with no luck.
This database is from a replicated database being restored to a
non-replication system.|||Bob wrote:
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>
Tried the new backup, or tried running DBCC? If the new backup gave the
same error, you might want to run DBCC CHECKDB against the original
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The RESTORE command has a CONTINUE_AFTER_ERROR option. That should at lest get the database there,
so you can do something with it (like last resort emergency mode). But you should really CHECKDB the
source database. If that db is clean, you shouldn't get corrupted backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob" <pmroberts@.talk21.com> wrote in message
news:1152811634.990074.129800@.p79g2000cwp.googlegroups.com...
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>|||2 questions -
1. How are you copying over the .bak file between the 2 machines?
2. Is the file sie the same?
"Bob" wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>|||Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?|||How do I use DBCC CHECKDB?|||This is a multi-part message in MIME format.
--090202070601080406010907
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Bob wrote:
> Hi,
> The files are being copied using windows drag and drop.
> The file sizes are the same.
> I am using SQL server management studio, I cannot see the RESTORE
> command has a CONTINUE_AFTER_ERROR, I tried this option under the
> backup option but with no luck.
> I also tried new backups.
> Is there a limit to the size of the backups?
>
You might need to do the restore with the sql command in order to
specify the CONTINUE AFTER ERROR option.
Try to look up RESTORE Database in Books On Line.
Also, have you tried to restore the files to a different drive? It might
not be the case, but you could have a bad spot on the disk and that's
why it fails (..I know it's a wild guess but it should be fairly easy to
try...).
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--090202070601080406010907
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Bob wrote:
<blockquote
cite="mid1152874083.247364.97190@.35g2000cwc.googlegroups.com"
type="cite">
<pre wrap="">Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?
</pre>
</blockquote>
<font size="-1"><font face="Arial">You might need to do the restore
with the sql command in order to specify the CONTINUE AFTER ERROR
option.<br>
Try to look up RESTORE Database in Books On Line.<br>
<br>
Also, have you tried to restore the files to a different drive? It
might not be the case, but you could have a bad spot on the disk and
that's why it fails (..I know it's a wild guess but it should be fairly
easy to try...).<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090202070601080406010907--|||Hi,
Problem solved.
The issue is due to the fact that they are replication databases.
Since I am restoring to a different server, without replication, the
SQL server is supposed to realise this and remove replication status,
however it does not and you have to remove this manually with the
command: sp_removedbreplication dbname
You can then bring the database online.
See http://msdn2.microsoft.com/en-us/library/ms151782(d=ide).aspx
for more information.

DB Restore

I often have to restore databases from backups sent to me by clients.
I do a
RESTORE FILELISTONLY FROM DISK='c:\db.bak'
which gives me the data and log file names, say 'data_file' and
'data_file_log'
and then a
RESTORE DATABASE DbName
FROM DISK = 'c:\db.bak'
WITH MOVE 'data_file' TO 'c:\test\db.mdf',
MOVE 'data_file_log' TO 'c:\test\db.ldf'
Does anyone know a way that I can read the names of the data and log files
into variables
in the 'RESTORE FILELISTONLY' command and then substitute them
into the 'RESTORE DATABASE' command ?
TIA
SteveYou should be able to do like below:
CREATE TABLE #FileDetails(...)
INSERT #FileDetails(col1, col2...)
EXEC('RESTORE FILELISTDETAILS...')
Now you can read the stuff off of that table and get the stuff into TSQL
variables. And then use those variables in the RESTORE command. In case SQL
Server doesn't accepts variables in RESTORE for the relevant option, you can
use dynamic SQL to execute the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Steve W" <lsl@.btconnect.com.no_spam> wrote in message
news:OZXew4N7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> I often have to restore databases from backups sent to me by clients.
> I do a
> RESTORE FILELISTONLY FROM DISK='c:\db.bak'
> which gives me the data and log file names, say 'data_file' and
> 'data_file_log'
> and then a
> RESTORE DATABASE DbName
> FROM DISK = 'c:\db.bak'
> WITH MOVE 'data_file' TO 'c:\test\db.mdf',
> MOVE 'data_file_log' TO 'c:\test\db.ldf'
> Does anyone know a way that I can read the names of the data and log files
> into variables
> in the 'RESTORE FILELISTONLY' command and then substitute them
> into the 'RESTORE DATABASE' command ?
> TIA
> Steve
>

DB Restore

I often have to restore databases from backups sent to me by clients.
I do a
RESTORE FILELISTONLY FROM DISK='c:\db.bak'
which gives me the data and log file names, say 'data_file' and
'data_file_log'
and then a
RESTORE DATABASE DbName
FROM DISK = 'c:\db.bak'
WITH MOVE 'data_file' TO 'c:\test\db.mdf',
MOVE 'data_file_log' TO 'c:\test\db.ldf'
Does anyone know a way that I can read the names of the data and log files
into variables
in the 'RESTORE FILELISTONLY' command and then substitute them
into the 'RESTORE DATABASE' command ?
TIA
SteveYou should be able to do like below:
CREATE TABLE #FileDetails(...)
INSERT #FileDetails(col1, col2...)
EXEC('RESTORE FILELISTDETAILS...')
Now you can read the stuff off of that table and get the stuff into TSQL
variables. And then use those variables in the RESTORE command. In case SQL
Server doesn't accepts variables in RESTORE for the relevant option, you can
use dynamic SQL to execute the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Steve W" <lsl@.btconnect.com.no_spam> wrote in message
news:OZXew4N7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> I often have to restore databases from backups sent to me by clients.
> I do a
> RESTORE FILELISTONLY FROM DISK='c:\db.bak'
> which gives me the data and log file names, say 'data_file' and
> 'data_file_log'
> and then a
> RESTORE DATABASE DbName
> FROM DISK = 'c:\db.bak'
> WITH MOVE 'data_file' TO 'c:\test\db.mdf',
> MOVE 'data_file_log' TO 'c:\test\db.ldf'
> Does anyone know a way that I can read the names of the data and log files
> into variables
> in the 'RESTORE FILELISTONLY' command and then substitute them
> into the 'RESTORE DATABASE' command ?
> TIA
> Steve
>

db permissions in EM

i've got a user that has permissions to read and write in two
databases. that user has enterprise manager installed and uses it (i
know he shouldn't really have it, but that's another topic). he can see
all of the databases on the server. is there any way to make EM only
show him the two databases which he can access?ch
Unfortunatly,NO
"ch" <ch@.dontemailme.com> wrote in message
news:41862283.D2817F00@.dontemailme.com...
> i've got a user that has permissions to read and write in two
> databases. that user has enterprise manager installed and uses it (i
> know he shouldn't really have it, but that's another topic). he can see
> all of the databases on the server. is there any way to make EM only
> show him the two databases which he can access?|||i think i figured out how to do it.
a few changes to master.dbo.sp_MSdbuseraccess
Uri Dimant wrote:
> ch
> Unfortunatly,NO
> "ch" <ch@.dontemailme.com> wrote in message
> news:41862283.D2817F00@.dontemailme.com...
> > i've got a user that has permissions to read and write in two
> > databases. that user has enterprise manager installed and uses it (i
> > know he shouldn't really have it, but that's another topic). he can see
> > all of the databases on the server. is there any way to make EM only
> > show him the two databases which he can access?|||Which means you no longer have a supported SQL server installation. Be careful when modifying system
objects. Not supported.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>|||ch
You can hide a system databases from seeing them with EM , but user
databases you cannot.
"ch" <ch@.dontemailme.com> wrote in message
news:41862EC4.7EF0C91A@.dontemailme.com...
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
>
> Uri Dimant wrote:
> >
> > ch
> > Unfortunatly,NO
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:41862283.D2817F00@.dontemailme.com...
> > > i've got a user that has permissions to read and write in two
> > > databases. that user has enterprise manager installed and uses it (i
> > > know he shouldn't really have it, but that's another topic). he can
see
> > > all of the databases on the server. is there any way to make EM only
> > > show him the two databases which he can access?|||this is sort of goofy.
query analyzer uses master.dbo.sp_MShasdbaccess intead of
master.dbo.sp_MSdbuseraccess.
sp_MShasdbaccess only returns the databases that the user can get into
or the db's that have a guest account.
sp_MSdbuseraccess returns all databases (when run from master).
query analyzer therefore only shows the user the databases that user can
actually get into which is what i'd really like enterprise manager to
do. i wonder why the two different standards between the two tools?
ch wrote:
> i think i figured out how to do it.
> a few changes to master.dbo.sp_MSdbuseraccess
> Uri Dimant wrote:
> >
> > ch
> > Unfortunatly,NO
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:41862283.D2817F00@.dontemailme.com...
> > > i've got a user that has permissions to read and write in two
> > > databases. that user has enterprise manager installed and uses it (i
> > > know he shouldn't really have it, but that's another topic). he can see
> > > all of the databases on the server. is there any way to make EM only
> > > show him the two databases which he can access?

Db Owner

Hi,

I currently have two databases (DEV & TEST) with the same users but
different owners. The TEST database is on a remote machine with 2
database users - USR1 & DBO. The DEV database is exactly the same.
However, when I create procedures etc the DEV database recognises the
owner as DBO and the TEST database USR1.

I have made USR1 an owner on DEV and I login using the correct
credentials. However it still is not recognising this when creating
procedures.

This is very frustrating when doing a SQL Compare as it thinks the
stored procs are different!

I have done a sp_changedbowner and this didn't work. There must be
another config value that need changing?

Any ideas?

Thanks.
jack-b@.humlog.com wrote:
> Hi,
> I currently have two databases (DEV & TEST) with the same users but
> different owners. The TEST database is on a remote machine with 2
> database users - USR1 & DBO. The DEV database is exactly the same.
> However, when I create procedures etc the DEV database recognises the
> owner as DBO and the TEST database USR1.
> I have made USR1 an owner on DEV and I login using the correct
> credentials. However it still is not recognising this when creating
> procedures.
> This is very frustrating when doing a SQL Compare as it thinks the
> stored procs are different!
> I have done a sp_changedbowner and this didn't work. There must be
> another config value that need changing?
> Any ideas?
> Thanks.

Could you try:

CREATE PROCEDURE dbo.procname

The Owner can be included in the create syntax

--
David Rowland
NEW DBMonitor Version Out Now! New EMAIL Alert Feature!
http://dbmonitor.tripod.com|||(jack-b@.humlog.com) writes:
> I currently have two databases (DEV & TEST) with the same users but
> different owners. The TEST database is on a remote machine with 2
> database users - USR1 & DBO. The DEV database is exactly the same.
> However, when I create procedures etc the DEV database recognises the
> owner as DBO and the TEST database USR1.
> I have made USR1 an owner on DEV and I login using the correct
> credentials. However it still is not recognising this when creating
> procedures.
> This is very frustrating when doing a SQL Compare as it thinks the
> stored procs are different!
> I have done a sp_changedbowner and this didn't work. There must be
> another config value that need changing?

Not really sure that I understood your setup correctly. But if you
login as USR1 and USR1 is the owner of DEV, the login USR1 will map
to the user dbo.

I'm not really sure what you want to achieve, but the simplest is to
have dbo as owner of all stored procedures. You can use
sp_changeobjectowner to change the owners of the procedure in TEST.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp