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 Restore

Hi all,
We are in need to perform a restore on our staging db box, and we seem to ha
ve a problem. The backup software we use does not have a DB Connector, and a
pparently the .mdf file is simply copied to tape at night! Without SQL Serve
r or the Backup software pr
eforming any detachment or backup process on the file. So we now have a .mdf
file and desperatly need to restore it to a different DB Server - is this p
ossible, or should we forget it?
TIA
AdamYou cold try sp_attach_db or sp_attach_single_file_db, it might work. It is
only documented to work if you actually detached the database (and for
_single_file_, there are more restrictions, check Books Online). I suggest
you do the backup using SQL Server's backup command and pick up the backup
file(s) to tape in the future :-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Adam Stewart" <anonymous@.discussions.microsoft.com> wrote in message
news:76D720B7-3E5E-4900-B2AA-8C66DCF9FB7B@.microsoft.com...
> Hi all,
> We are in need to perform a restore on our staging db box, and we seem to
have a problem. The backup software we use does not have a DB Connector, and
apparently the .mdf file is simply copied to tape at night! Without SQL
Server or the Backup software preforming any detachment or backup process on
the file. So we now have a .mdf file and desperatly need to restore it to a
different DB Server - is this possible, or should we forget it?
> TIA
> Adam

Db order in Maintenance Plan

Hi,
having used the Maintenance Plan Wizard to perform back-ups for databases A,
B and C, this schedules the 'DB BAckup job for MyPlan'.
1) Are the databases backed up one at a time or are 3 back-up jobs started
at the same time?
2) If the back-ups are sequential, can I tell what order they run?
Thanks.I used this query to look at the historical data of the completed backups:
select backup_set_id,database_name, backup_start_date,backup_finish_date
from backupset
where database_name in ('master','msdb','model')
and backup_start_date > '20051115'
order by backup_set_id
And following are my conclusions:
1) Are the databases backed up one at a time or are 3 back-up jobs started
at the same time?
Databases will be backed up one after the other.
2) If the back-ups are sequential, can I tell what order they run?
In alphbetical order of the databases name.
"Gramps" wrote:
> Hi,
>
> having used the Maintenance Plan Wizard to perform back-ups for databases A,
> B and C, this schedules the 'DB BAckup job for MyPlan'.
> 1) Are the databases backed up one at a time or are 3 back-up jobs started
> at the same time?
> 2) If the back-ups are sequential, can I tell what order they run?
>
> Thanks.

Db order in Maintenance Plan

Hi,
having used the Maintenance Plan Wizard to perform back-ups for databases A,
B and C, this schedules the 'DB BAckup job for MyPlan'.
1) Are the databases backed up one at a time or are 3 back-up jobs started
at the same time?
2) If the back-ups are sequential, can I tell what order they run?
Thanks.
I used this query to look at the historical data of the completed backups:
select backup_set_id,database_name, backup_start_date,backup_finish_date
from backupset
where database_name in ('master','msdb','model')
and backup_start_date > '20051115'
order by backup_set_id
And following are my conclusions:
1) Are the databases backed up one at a time or are 3 back-up jobs started
at the same time?
Databases will be backed up one after the other.
2) If the back-ups are sequential, can I tell what order they run?
In alphbetical order of the databases name.
"Gramps" wrote:

> Hi,
>
> having used the Maintenance Plan Wizard to perform back-ups for databases A,
> B and C, this schedules the 'DB BAckup job for MyPlan'.
> 1) Are the databases backed up one at a time or are 3 back-up jobs started
> at the same time?
> 2) If the back-ups are sequential, can I tell what order they run?
>
> Thanks.

Db order in Maintenance Plan

Hi,
having used the Maintenance Plan Wizard to perform back-ups for databases A,
B and C, this schedules the 'DB BAckup job for MyPlan'.
1) Are the databases backed up one at a time or are 3 back-up jobs started
at the same time?
2) If the back-ups are sequential, can I tell what order they run?
Thanks.I used this query to look at the historical data of the completed backups:
select backup_set_id,database_name, backup_start_date,backup_finish_date
from backupset
where database_name in ('master','msdb','model')
and backup_start_date > '20051115'
order by backup_set_id
And following are my conclusions:
1) Are the databases backed up one at a time or are 3 back-up jobs started
at the same time?
Databases will be backed up one after the other.
2) If the back-ups are sequential, can I tell what order they run?
In alphbetical order of the databases name.
"Gramps" wrote:

> Hi,
>
> having used the Maintenance Plan Wizard to perform back-ups for databases
A,
> B and C, this schedules the 'DB BAckup job for MyPlan'.
> 1) Are the databases backed up one at a time or are 3 back-up jobs started
> at the same time?
> 2) If the back-ups are sequential, can I tell what order they run?
>
> Thanks.

db maintanence plan failure

Hi all!
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
> supposed
>> to perform optimizations and integrity checks on all user databases. This
> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
> jobs
>> seems to complete OK
>> TIA!
>>
>|||If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases. This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>> OK. But wouldnt I want it to fix any problems? What is the best way then?
>> thanks!
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database
>> to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases.
>> This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL
>> Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>>
>|||Maint wiz has an option to create a report file for each execution. Here you will find the error
messages.
In your situation, the problem is most likely not corruption. The problem is that maint wiz is
trying to set the database in single user mode (because of that option is checked), and you have
users connected to the database. Uncheck the option, and that problem will go away.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...
>I agree I need to know what is causing the problem. But how can I find that out? The database seems
>to appear functional from the application perspective. How can I determine if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>> If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
>> down when I travel at high speed. I.e., if you get such problem, you want to do root-cause
>> analysis why they happen. Often it is because hardware problems.
>> In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
>> removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
>> the REPAIR_FAST option.
>> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if
>> you have users in the database, the command and subsequently job will fail.
>> Also, you might want to check out: http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>> OK. But wouldnt I want it to fix any problems? What is the best way then?
>> thanks!
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>> Param
>> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
>> SQL Server is trying to repair some problems but it requires database to be
>> in single user mode.
>>
>> "Param R." <pr@.nospam.com> wrote in message
>> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
>> Hi all!
>> I have a DB Maintanence plan configured to run once a week that is
>> supposed
>> to perform optimizations and integrity checks on all user databases. This
>> is
>> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
>> The
>> optimizations is set to run at 4 am & the integrity at 5 am. It appears
>> that
>> the integrity check job keeps failing. The log says something about
>> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
>> jobs
>> seems to complete OK
>> TIA!
>>
>>
>>
>>
>

db maintanence plan failure

Hi all!
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!
Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>
|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> supposed
> is
> The
> that
> jobs
>
|||If my car keep breaking down, I'd like to know why that happen so I can avoid it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execute DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in single user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
>
|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>
|||Maint wiz has an option to create a report file for each execution. Here you will find the error
messages.
In your situation, the problem is most likely not corruption. The problem is that maint wiz is
trying to set the database in single user mode (because of that option is checked), and you have
users connected to the database. Uncheck the option, and that problem will go away.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...
>I agree I need to know what is causing the problem. But how can I find that out? The database seems
>to appear functional from the application perspective. How can I determine if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>

db maintanence plan failure

Hi all!
I have a DB Maintanence plan configured to run once a week that is supposed
to perform optimizations and integrity checks on all user databases. This is
split into 2 seperate jobs when I look at the Jobs node under SQL Agent. The
optimizations is set to run at 4 am & the integrity at 5 am. It appears that
the integrity check job keeps failing. The log says something about
'mosesdb' needs to be in single user mode. Any ideas? The optimization jobs
seems to complete OK
TIA!Param
Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
SQL Server is trying to repair some problems but it requires database to be
in single user mode.
"Param R." <pr@.nospam.com> wrote in message
news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a DB Maintanence plan configured to run once a week that is
supposed
> to perform optimizations and integrity checks on all user databases. This
is
> split into 2 seperate jobs when I look at the Jobs node under SQL Agent.
The
> optimizations is set to run at 4 am & the integrity at 5 am. It appears
that
> the integrity check job keeps failing. The log says something about
> 'mosesdb' needs to be in single user mode. Any ideas? The optimization
jobs
> seems to complete OK
> TIA!
>|||OK. But wouldnt I want it to fix any problems? What is the best way then?
thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eSii%23klEFHA.2032@.tk2msftngp13.phx.gbl...
> Param
> Under Tab 'Integrity' uncheck 'Attempt to repair any minor problems'
> SQL Server is trying to repair some problems but it requires database to
> be
> in single user mode.
>
> "Param R." <pr@.nospam.com> wrote in message
> news:ed%23BifjEFHA.1012@.TK2MSFTNGP14.phx.gbl...
> supposed
> is
> The
> that
> jobs
>|||If my car keep breaking down, I'd like to know why that happen so I can avoi
d it keeping breaking
down when I travel at high speed. I.e., if you get such problem, you want to
do root-cause analysis
why they happen. Often it is because hardware problems.
In other words, the option in maint wiz is IMO not well thought through, and
AFAIK, it will be
removed in next version of SQL Server. The option, btw makes maint wiz execu
te DBCC CHECKDB using
the REPAIR_FAST option.
To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in sin
gle user mode, so if you
have users in the database, the command and subsequently job will fail.
Also, you might want to check out: http://www.karaszi.com/SQLServer/in...
uspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Param R." <pr@.nospam.com> wrote in message news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...[
vbcol=seagreen]
> OK. But wouldnt I want it to fix any problems? What is the best way then?
> thanks!
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:eSii%23klEFHA.2032@.t
k2msftngp13.phx.gbl...
>[/vbcol]|||I agree I need to know what is causing the problem. But how can I find that
out? The database seems to appear functional from the application
perspective. How can I determine if it is corrupt and if so what is causing
it to be corrupt? The link below gives me some info, but not all.
thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> If my car keep breaking down, I'd like to know why that happen so I can
> avoid it keeping breaking down when I travel at high speed. I.e., if you
> get such problem, you want to do root-cause analysis why they happen.
> Often it is because hardware problems.
> In other words, the option in maint wiz is IMO not well thought through,
> and AFAIK, it will be removed in next version of SQL Server. The option,
> btw makes maint wiz execute DBCC CHECKDB using the REPAIR_FAST option.
> To be able to run DBCC CHECKDB with FAST_REPAIR, the database must be in
> single user mode, so if you have users in the database, the command and
> subsequently job will fail.
> Also, you might want to check out:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Param R." <pr@.nospam.com> wrote in message
> news:%23Czx7SqEFHA.1188@.tk2msftngp13.phx.gbl...
>|||Maint wiz has an option to create a report file for each execution. Here you
will find the error
messages.
In your situation, the problem is most likely not corruption. The problem i
s that maint wiz is
trying to set the database in single user mode (because of that option is ch
ecked), and you have
users connected to the database. Uncheck the option, and that problem will g
o away.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Param R." <pr@.nospam.com> wrote in message news:u5DxcdrEFHA.2232@.TK2MSFTNGP14.phx.gbl...[vb
col=seagreen]
>I agree I need to know what is causing the problem. But how can I find that
out? The database seems
>to appear functional from the application perspective. How can I determine
if it is corrupt and if
>so what is causing it to be corrupt? The link below gives me some info, but
not all.
> thanks!
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e20IkmqEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]

DB Disk Hot Split question

Hello:
I am using SAN storage to store the DB files. The SAN disk is in
mirror. Does any config that I can perform the hot split the SAN mirror
disk and the DB files can be use as backup purpose?
It was because I have try to split the disk but those DB files can't be
mount on the same DB server.
Thanks!
Most SAN vendors support this type of mirror split. EMC calls it BCV split.
To use the split mirror for backup, it has to be integrated with SQL Server
VDI so that I/Os can be quiesced briefly to get a rcoveable image.
You need to follow the SAN vendor's instructions to do this right.
Linchi
"Blue Fish" wrote:

> Hello:
> I am using SAN storage to store the DB files. The SAN disk is in
> mirror. Does any config that I can perform the hot split the SAN mirror
> disk and the DB files can be use as backup purpose?
> It was because I have try to split the disk but those DB files can't be
> mount on the same DB server.
> Thanks!
>
|||Thanks a lot for your information. If I am using HDS, that means I need
to purchase the SplitSecond to do that, right?
Thanks!
Linchi Shea wrote:[vbcol=seagreen]
> Most SAN vendors support this type of mirror split. EMC calls it BCV split.
> To use the split mirror for backup, it has to be integrated with SQL Server
> VDI so that I/Os can be quiesced briefly to get a rcoveable image.
> You need to follow the SAN vendor's instructions to do this right.
> Linchi
> "Blue Fish" wrote:

DB Disk Hot Split question

Hello:
I am using SAN storage to store the DB files. The SAN disk is in
mirror. Does any config that I can perform the hot split the SAN mirror
disk and the DB files can be use as backup purpose?
It was because I have try to split the disk but those DB files can't be
mount on the same DB server.
Thanks!Most SAN vendors support this type of mirror split. EMC calls it BCV split.
To use the split mirror for backup, it has to be integrated with SQL Server
VDI so that I/Os can be quiesced briefly to get a rcoveable image.
You need to follow the SAN vendor's instructions to do this right.
Linchi
"Blue Fish" wrote:
> Hello:
> I am using SAN storage to store the DB files. The SAN disk is in
> mirror. Does any config that I can perform the hot split the SAN mirror
> disk and the DB files can be use as backup purpose?
> It was because I have try to split the disk but those DB files can't be
> mount on the same DB server.
> Thanks!
>|||Thanks a lot for your information. If I am using HDS, that means I need
to purchase the SplitSecond to do that, right?
Thanks!
Linchi Shea wrote:
> Most SAN vendors support this type of mirror split. EMC calls it BCV split.
> To use the split mirror for backup, it has to be integrated with SQL Server
> VDI so that I/Os can be quiesced briefly to get a rcoveable image.
> You need to follow the SAN vendor's instructions to do this right.
> Linchi
> "Blue Fish" wrote:
>> Hello:
>> I am using SAN storage to store the DB files. The SAN disk is in
>> mirror. Does any config that I can perform the hot split the SAN mirror
>> disk and the DB files can be use as backup purpose?
>> It was because I have try to split the disk but those DB files can't be
>> mount on the same DB server.
>> Thanks!

DB Disk Hot Split question

Hello:
I am using SAN storage to store the DB files. The SAN disk is in
mirror. Does any config that I can perform the hot split the SAN mirror
disk and the DB files can be use as backup purpose?
It was because I have try to split the disk but those DB files can't be
mount on the same DB server.
Thanks!Most SAN vendors support this type of mirror split. EMC calls it BCV split.
To use the split mirror for backup, it has to be integrated with SQL Server
VDI so that I/Os can be quiesced briefly to get a rcoveable image.
You need to follow the SAN vendor's instructions to do this right.
Linchi
"Blue Fish" wrote:

> Hello:
> I am using SAN storage to store the DB files. The SAN disk is in
> mirror. Does any config that I can perform the hot split the SAN mirror
> disk and the DB files can be use as backup purpose?
> It was because I have try to split the disk but those DB files can't be
> mount on the same DB server.
> Thanks!
>|||Thanks a lot for your information. If I am using HDS, that means I need
to purchase the SplitSecond to do that, right?
Thanks!
Linchi Shea wrote:[vbcol=seagreen]
> Most SAN vendors support this type of mirror split. EMC calls it BCV split
.
> To use the split mirror for backup, it has to be integrated with SQL Serve
r
> VDI so that I/Os can be quiesced briefly to get a rcoveable image.
> You need to follow the SAN vendor's instructions to do this right.
> Linchi
> "Blue Fish" wrote:
>