Database in Standby / Readonly

I recently restored a database to a 2005 SQL Server and it is now
reporting that it is in standby / readonly mode. I've seen some
suggestion that this could be solved through re-restoring the database
but the database restore isn't an option. I've tried taking it offline
so that I could get access to restore it and haven't had any luck
there as well. Is there something I'm missing here or do I just need
to drop and restore the database all over again?
RESTORE DATABASE [database_name] WITH RECOVERY
"johnvmc@.gmail.com" wrote:

> I recently restored a database to a 2005 SQL Server and it is now
> reporting that it is in standby / readonly mode. I've seen some
> suggestion that this could be solved through re-restoring the database
> but the database restore isn't an option. I've tried taking it offline
> so that I could get access to restore it and haven't had any luck
> there as well. Is there something I'm missing here or do I just need
> to drop and restore the database all over again?
>

Database files compatible with Win2K & Win2003

SQL Server 2000/SP3 runs on Win2K.
I've failover clustering server run on Win2003. Are the system database
files compatible on both Win2K & Win003 ? That I can switch the databases on
Win2k to Win2003.
JT
Hi
SQL Server 2000 data files and backups, on 32 or 64 bit, are compatible on
any platform. The base OS does not matter. OS does not play a role here.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny" wrote:

> SQL Server 2000/SP3 runs on Win2K.
> I've failover clustering server run on Win2003. Are the system database
> files compatible on both Win2K & Win003 ? That I can switch the databases on
> Win2k to Win2003.
> JT

Database Files (file name)

I've detached a database and then reactached it specifing a new name for
the database within SQL Server 2000. But, if i right click and select
the database properties and then select the "Data Files" tab the file
name is still the same as the original database. How do i go about
changing that? Thanks in advance.
Vincent
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Hi Vincent
Changing the database name only changes one value, in the sysdatabases table
in the master database. It doesn't change any info in the database itself,
including the logical names associated with the database files which are
stored in the sysfiles table in the database itself.
To change the logical file names, you can use ALTER DATABASE... MODIFY FILE,
and specify a value for NEWNAME.
Please see the complete ALTER DATABASE syntax in the Books Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vincent Cristiano" <vincec@.mail.com> wrote in message
news:eTK%23YXruEHA.1984@.TK2MSFTNGP14.phx.gbl...
> I've detached a database and then reactached it specifing a new name for
> the database within SQL Server 2000. But, if i right click and select
> the database properties and then select the "Data Files" tab the file
> name is still the same as the original database. How do i go about
> changing that? Thanks in advance.
> Vincent
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql

Database disappeared from sysdatabases after running out of file space

Guys and gals, I'm in all sorts of trouble here. I've been nursing a server
with very little free space for my sql databases while I get a new one
built, and it's been pretty safe, until a power cut the other day caused
some sort of huge write to the log file and it ran out of space. I mean,
really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
30gig. So, I needed to backup the transaction log, reorganise and shrink
the database. Couldn't do that, no elbow room, so I thought I'd move the
files to a USB hdd I have on there, slow but ok temporarily, and do the
reorg there. Unfortunately, detatching the database has, somehow caused the
sysdatabases table to lose any knowledge of it. I've got the files in their
new location and would dearly love to be able to reattach them somehow to
shrink them down but I'm at a loss now.
I'd really appreciate a pointer here, I've run out of ideas.
Cheers
Chris Weston
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
the
> sysdatabases table to lose any knowledge of it. I've got the files in
their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
Just to follow up here, I've tried attaching the database from the context
menu on my databases folder, but it complains about the log file (all 22g of
it) being faulty. Can't I attach the mdf and let the log file be recreated?
I'm no sql expert but the mdf is the important part, surely?
Thanks,
Chris Weston
|||sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
It will generate a new log file.
good luck.
d.
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:tIYSf.2054$H%3.875@.newsfe5-gui.ntli.net...[vbcol=seagreen]
> "Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
> news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> server
mean,[vbcol=seagreen]
shrink[vbcol=seagreen]
the[vbcol=seagreen]
> the
> their
to
> Just to follow up here, I've tried attaching the database from the context
> menu on my databases folder, but it complains about the log file (all 22g
of
> it) being faulty. Can't I attach the mdf and let the log file be
recreated?
> I'm no sql expert but the mdf is the important part, surely?
> Thanks,
> --
> Chris Weston
>
|||"d" <d@.d.com> wrote in message news:ruZSf.1311$I7.1016@.trnddc03...
> sp_attach_single_file_db [ @.dbname = ] 'dbname'
> , [ @.physname = ] 'physical_name'
> It will generate a new log file.
>
> good luck.
>
I appreciate the sentiment However, I'm not sure where to run this
command? In a query window in Enterprise Manager?
Many thanks,
Chris Weston
|||Hi Chris
This is expected behavior. When you detach a database, it is completely
removed from the system. It is different from DROP in that the files still
exist on disk.
To get the db back, you have to attach it.
If you are attaching through Enterprise Manager, and SQL Server complains
about the log, you can try changing the name of the log file on disk so EM
can't find it, and see if a new log will be rebuilt.
Or, as d suggested, you can run the procedure from sp_attach_single_file_db
from a query window in Query Analyzer.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
> server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
> the
> sysdatabases table to lose any knowledge of it. I've got the files in
> their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
> Cheers
> Chris Weston
>
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi Chris
> This is expected behavior. When you detach a database, it is completely
> removed from the system. It is different from DROP in that the files still
> exist on disk.
> To get the db back, you have to attach it.
> If you are attaching through Enterprise Manager, and SQL Server complains
> about the log, you can try changing the name of the log file on disk so EM
> can't find it, and see if a new log will be rebuilt.
> Or, as d suggested, you can run the procedure from
sp_attach_single_file_db
> from a query window in Query Analyzer.
Still, no joy - I have tried both these methods but still get an error
saying that it can't find the .ldf file. How can I get past this?
Thanks,
Chris Weston
|||Hi
The methods described by Kalen should work if you followed them exactly.
Can you post the exact sp_attach_single_file_db command that you have used?
Make sure that you have move or renamed the ldf file and there is enough
space to create a new one.
Make sure that the mdf file does exist in the directory you have specified.
Make sure that directory where it wants to create the ldf file exists and
that the permissions allow you to create a new file.
John
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:dz8Tf.2219$g76.437@.newsfe2-gui.ntli.net...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> xslmailinglist@.btopenworld.com>> from a query window in Query Analyzer.
> Still, no joy - I have tried both these methods but still get an error
> saying that it can't find the .ldf file. How can I get past this?
> Thanks,
> Chris Weston
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Hi
> The methods described by Kalen should work if you followed them exactly.
> Can you post the exact sp_attach_single_file_db command that you have
used?
> Make sure that you have move or renamed the ldf file and there is enough
> space to create a new one.
> Make sure that the mdf file does exist in the directory you have
specified.
> Make sure that directory where it wants to create the ldf file exists and
> that the permissions allow you to create a new file.
It exists, it's my default sql data directory and I'm logged in as
administrator. The mdf exists. I'm using
EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
@.physname = N'h:\systemp\SyscompA.mdf'
but I get an error saying that 'the physical file name 'E:\program
files\...\data\syscompanyA_log.ldf may be incorrect'
So is it saying it can't create or that it can't find the ldf file?
Chris Weston
|||HI Chris
Was E:\ the original drive? It looks like when SQL Server creates a new log
file, it wants to create it on the drive where it originally existed.
I have use sp_attach_db to move a db, or to rebuild a log, but never at the
same time.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:o0hTf.2330$g76.772@.newsfe2-gui.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> used?
> specified.
> It exists, it's my default sql data directory and I'm logged in as
> administrator. The mdf exists. I'm using
> EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
> @.physname = N'h:\systemp\SyscompA.mdf'
> but I get an error saying that 'the physical file name 'E:\program
> files\...\data\syscompanyA_log.ldf may be incorrect'
> So is it saying it can't create or that it can't find the ldf file?
> --
> Chris Weston
>
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
> HI Chris
> Was E:\ the original drive? It looks like when SQL Server creates a new
log
> file, it wants to create it on the drive where it originally existed.
> I have use sp_attach_db to move a db, or to rebuild a log, but never at
the
> same time.
Kalen,
E:\ was the original drive, and it still exists. I've no problem with it
creating the log there if need be.
Chris Weston

Database diagram, connector on fields?

Hi there!
Currently I've managed to etablish relationsships on fields to/from
different tables. But the 'visual' result is a bit off from what I wish.
Currently MSSQL diagram designer hooks up the relations from table to table.
I would very much like to change that to 'field to field' instead, as to
make it easier to visually determine relationsships. Or alternative get
MSSQL to automatically change the shown labels to say 'id = catID'.
What options do I have regarding this?
I doubt, therefore I might be.
a different tool
ErWin or ER-Studio are what I recommend
Greg Jackson
PDX, Oregon
|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23aR2GdzVFHA.2700@.TK2MSFTNGP12.phx.gbl

> ErWin or ER-Studio are what I recommend
Thanks for the recommendation, however it seems that it costs money, and
because I'm not going to use this kind of tool very often (maybe 5-10 times
a year), I really can't justify paying for this. Is there any free tool on
the market, that is easy to use? I only need a few things to visualize.
I doubt, therefore I might be.

Database Detach - orphan users

I've used the sp_'s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
And still got orphaned users.
Here's what I did:
1. Script users on master server with sp_'s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_'s output
user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins)
I had to use sp_change_users_login but I should not have had to.
What went wrong ?
Possibly logins already existed with the same name on the destination database. Did you get any
errors from step 5? That would explain it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
> I've used the sp_'s from this article:
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133
> And still got orphaned users.
> Here's what I did:
> 1. Script users on master server with sp_'s in KB article.
> 2. Detach
> 3. FTP
> 4. Attach
> 5. Load users from sp_'s output
> user databases still had no link to sql server logins
> (not using windows auth by the way, only sql server logins)
> I had to use sp_change_users_login but I should not have had to.
> What went wrong ?
>
>
|||There were no errors and the users did not exist.
The second server was a fresh install
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Possibly logins already existed with the same name on the destination
database. Did you get any
> errors from step 5? That would explain it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ben" <null@.void.com> wrote in message
news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>
|||Then I have no ideas I'm afraid. It should not happen. I guess you could compare the file produced
by sp_help_revlogins with the logins on the originating system and with sysusers on both originating
server as well as the restored database. That should give you the whole picture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <null@.void.com> wrote in message news:%23qdaZrNqFHA.364@.TK2MSFTNGP11.phx.gbl...
> There were no errors and the users did not exist.
> The second server was a fresh install
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:unZSCBIqFHA.544@.TK2MSFTNGP11.phx.gbl...
> database. Did you get any
> news:%23yKtezCqFHA.3424@.TK2MSFTNGP14.phx.gbl...
>

Database Design Standards

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
NO HELP So far
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server? How can they only
have
> read access to the SQL server databases? What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
|||On Sun, 24 Oct 2004 23:45:32 -0500, Shash Goyal wrote:

>NO HELP So far
Hi Shash,
There are everal replies in some of the other groups wherre you posted
this same question. I recommend you follow up on those replies instead of
complaining about lack of replies in this grooup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
"Shash Goyal" <Shash703@.gmail.com> ha scritto nel messaggio
news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl
big deal, isn't it? =;-D

> Well i've been given a big job of copying all the databases from an
> old server to a new server. In order to provide better security,
> availabilty, performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal
> services to connect to it.
> 1. I have two logical partitions in the server. Is it a good
> practice to store the OS and SQL server software itself on C:\ and
> all the data on d:\??(Will it help me in anyways to achieve better
> performance? Can i make separate directories for each databaseon
> d:\. and further on extending it to sub directories for data and log
> files?
I usually go this way on desktops... just for a schema order where data is
not directly bound and mixed with exes...
you do not gain better performance as drive and controller are the same..
perhaps you could get worser performance as the disk headers will have more
work... but for sure you do not gain benefits this way..
you are not granted against disk failures as one of them wil probably trash
exes and data..
again.. I do it myself the same way.. but only for a schema mapping order,
not for physical matters..
to achieve better througoutput performance, you should go for separate
controllers and drives... RAID solutions as well for improving security...
further basic info about RAID levels at http://www.acnc.com/04_00.html
as regard te second part of the question, yes, you can... no problem with
that..

> 2. Should i copy all objects such as logins, DB plans, jobs etc.
> from the old server or is it a better a practice to start all the
> plans over (create new plans) to achieve better results and only copy
> the databases?
I do not think you'll get problems copying all original objects to the new
server... I see non penalties in that... yo could just get orphaned objects
if your original server is not clean, but I do not see problems at all..

> 3. What is a good strategy for backup plans? For Log Files? For
> Primary Files?
hey, this is trickie... you have to think about your own needs... there' no
one size fits all strategy... depending on your needs you could be happy
with a dayly full backup for a little database, even a weekly full backup
for a quite read-only database..
if you are subscriber at SQL Server Magazine, you can have a look at good
articles like
http://www.windowsitpro.com/Article/...647/39647.html
http://www.windowsitpro.com/Article/...657/39657.html
this is free to read,
http://www.winnetmag.com/Article/Art...915/25915.html , for Very Large
Databases secenarios, with full, differential an log backup features
explanations..
again... it all depends on your data, your needs, your shadow time limits...
http://www.winnetmag.com/Article/Art...340/24340.html is free too...
by "Notre Dame SQL Server", Kalen Delaney

> 4. How to come up with a good Disaster Recovery Plan? What are all
> the things you need to have in order to create a good DR plan? what
> is a good way to test it?
same as above... plus add OS, cluster settings, applications CD to the
recovery schema...
a disaster can strike you down to your knees, so you'll have all (really
all) to start a new database server, application server, web server, farm
and all..
so, to your standard backups tapes, you should add all the CDs to install
and ugrade to your current service pack levels your OS and applications..
and please do not store them on the main server top =;-D
you should consider how important is your data and how you want to protect
all your IT scenario.. you can perhaps maintain a local copy and dayly ship
a backup copy to your bank or to specialised storing company..
it's a matter of costs, requirements and so on...

> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And
> how can i give people read only access to the databases if they have
> access to the server? Do they even need access to the server?
> How can they only have read access to the SQL server databases?
> What tools do i need? Since i have to use remote desktop to conncet
> to the servers, how can i give my clients that just want read access
> to the all the data files including log files? What do they need
> installed / or use in order to achieve this?
only users needing access to the data should be given right to access the
database server.. and only to the databases they need to access, with the
minimal privileges they need for theyr required activities..
the tool you need to implement security depends on your skill... Query
Analyzer could be enought to execute GRANT/DENY DCL statements to database
objects, where sp_addlogin/sp_grantlogin are required for server accces..
and again, give them only access (via sp_adduser) to the database they need
to access..
there's no readonly privilege to the database.. you are legitimated to
access it or not... you can give readonly privileges to tables and views
objects granting SELECT only privileges to some or all of them, but my
preferred access schema is via GRANT EXECUTE to
retrival/insert/update/delete stored procedures only and DENY all privileges
on table objects.. you can give them access to view object if needed..
a good starting point about secuirty and some best practice can be found at
http://www.sql-server-performance.co...l_security.asp , as long as
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
...
you users do not need access to the physical files of your databases, nor do
they need access to the log files at all.. your database are only logical
matters for them, and this should remain that way..
they only need the application(s) they use to interact with data, both
windowsform or web interfaces, and, usually MDAC, in order to provide the
connectivity tools and SQL Server drivers.. not more, not less.. standar
users do not need acces to Enterprise Manager and/or Query Analyzer.. give
them only what they really need.. not more.. better less =;-D

> 6. Is there any way you can come up with Roles scheme for certain
> users? Lets say a particular group of users should have a certain
> permissions? Can we create a something like that? that need to be
> done on the OS level rather than SQL level.?
this is a best practice and standard idea.. instead of maintaining
thousounds of users privileges, create at database level different roles..
make each user part of the corresponding role and manage privileges at role
level... you will not become insane and you can achieve the granularity you
usually need..
roles have to be managed at database level, and are known as user defined
database roles... not at the OS level... roles at OS level can be usefull,
but you have to deal with database and related security...
have a look at
http://msdn.microsoft.com/library/de..._addp_33s5.asp

> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
do you mean you don't know if our meanings will be appreciated? ROTFL
you are right... no one but you knows better your own needs ... I can only
give you some hints based on my own experience and based on my (poor) skill,
but you have the key of your success in this area..
good luck
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much Andrea!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2u6on1F26o6hvU1@.uni-berlin.de...
> hi,
> "Shash Goyal" <Shash703@.gmail.com> ha scritto nel messaggio
> news:ejQCy58tEHA.348@.tk2msftngp13.phx.gbl
> big deal, isn't it? =;-D
>
> I usually go this way on desktops... just for a schema order where data is
> not directly bound and mixed with exes...
> you do not gain better performance as drive and controller are the same..
> perhaps you could get worser performance as the disk headers will have
more
> work... but for sure you do not gain benefits this way..
> you are not granted against disk failures as one of them wil probably
trash
> exes and data..
> again.. I do it myself the same way.. but only for a schema mapping order,
> not for physical matters..
> to achieve better througoutput performance, you should go for separate
> controllers and drives... RAID solutions as well for improving security...
> further basic info about RAID levels at http://www.acnc.com/04_00.html
> as regard te second part of the question, yes, you can... no problem with
> that..
>
> I do not think you'll get problems copying all original objects to the new
> server... I see non penalties in that... yo could just get orphaned
objects
> if your original server is not clean, but I do not see problems at all..
>
> hey, this is trickie... you have to think about your own needs... there'
no
> one size fits all strategy... depending on your needs you could be happy
> with a dayly full backup for a little database, even a weekly full backup
> for a quite read-only database..
> if you are subscriber at SQL Server Magazine, you can have a look at good
> articles like
> http://www.windowsitpro.com/Article/...647/39647.html
> http://www.windowsitpro.com/Article/...657/39657.html
> this is free to read,
> http://www.winnetmag.com/Article/Art...915/25915.html , for Very
Large
> Databases secenarios, with full, differential an log backup features
> explanations..
> again... it all depends on your data, your needs, your shadow time
limits...
> http://www.winnetmag.com/Article/Art...340/24340.html is free too...
> by "Notre Dame SQL Server", Kalen Delaney
>
> same as above... plus add OS, cluster settings, applications CD to the
> recovery schema...
> a disaster can strike you down to your knees, so you'll have all (really
> all) to start a new database server, application server, web server, farm
> and all..
> so, to your standard backups tapes, you should add all the CDs to install
> and ugrade to your current service pack levels your OS and applications..
> and please do not store them on the main server top =;-D
> you should consider how important is your data and how you want to protect
> all your IT scenario.. you can perhaps maintain a local copy and dayly
ship
> a backup copy to your bank or to specialised storing company..
> it's a matter of costs, requirements and so on...
>
> only users needing access to the data should be given right to access the
> database server.. and only to the databases they need to access, with the
> minimal privileges they need for theyr required activities..
> the tool you need to implement security depends on your skill... Query
> Analyzer could be enought to execute GRANT/DENY DCL statements to database
> objects, where sp_addlogin/sp_grantlogin are required for server accces..
> and again, give them only access (via sp_adduser) to the database they
need
> to access..
> there's no readonly privilege to the database.. you are legitimated to
> access it or not... you can give readonly privileges to tables and views
> objects granting SELECT only privileges to some or all of them, but my
> preferred access schema is via GRANT EXECUTE to
> retrival/insert/update/delete stored procedures only and DENY all
privileges
> on table objects.. you can give them access to view object if needed..
> a good starting point about secuirty and some best practice can be found
at
> http://www.sql-server-performance.co...l_security.asp , as long as
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
> ..
> you users do not need access to the physical files of your databases, nor
do
> they need access to the log files at all.. your database are only logical
> matters for them, and this should remain that way..
> they only need the application(s) they use to interact with data, both
> windowsform or web interfaces, and, usually MDAC, in order to provide the
> connectivity tools and SQL Server drivers.. not more, not less.. standar
> users do not need acces to Enterprise Manager and/or Query Analyzer.. give
> them only what they really need.. not more.. better less =;-D
>
> this is a best practice and standard idea.. instead of maintaining
> thousounds of users privileges, create at database level different roles..
> make each user part of the corresponding role and manage privileges at
role
> level... you will not become insane and you can achieve the granularity
you
> usually need..
> roles have to be managed at database level, and are known as user defined
> database roles... not at the OS level... roles at OS level can be usefull,
> but you have to deal with database and related security...
> have a look at
>
http://msdn.microsoft.com/library/de..._addp_33s5.asp
>
> do you mean you don't know if our meanings will be appreciated? ROTFL
> you are right... no one but you knows better your own needs ... I can only
> give you some hints based on my own experience and based on my (poor)
skill,
> but you have the key of your success in this area..
> good luck
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Database design question

I’m relatively new at database design so forgive me if I’ve overlooked
something stupid.
I’m designing a teacher grading application on the new SQL 2005 Express.
Right now I’ve got a Student table that lists info on each student. I the
n
have a Grades table that lists grades for each assignment that is linked to
an assignment table. The grades table and the student table are linked with
a relationship. I’d like to create a view that lists the student name wit
h
each corresponding grade for each assignment in a new column. When do a
simple select statement I get a row for each grade, I just want one row for
each student with a assignment grade in separate columns. Is this possible?It can be done on the server (in T-SQL, even) but it *really should* be done
in the presentation layer.
Read more here:
http://www.windowsitpro.com/Article...5608/15608.html
Data seems more user-friendly using this technique, however, an important
informational value is lost this way - the relationship. So, I'd suggest
using this technique for presentation purposes only, better yet - let the
presentation engine take care of it.
ML

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
I suggest you to start with SQL 2000 Operations Guide -
http://www.microsoft.com/technet/pro.../sqlops0.mspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:OdySK88tEHA.2128@.TK2MSFTNGP11.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
> 5. What is the best way to secure SQL server? Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server? How can they only
have
> read access to the SQL server databases? What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>

Database Design

Well i've been given a big job of copying all the databases from an old
server to a new server. In order to provide better security, availabilty,
performance.
My servers are in a DMZ, so i have to use remote desktop/terminal services
to connect to it.
1. I have two logical partitions in the server. Is it a good practice to
store the OS and SQL server software itself on C:\ and all the data on
d:\??(Will it help me in anyways to achieve better performance? Can i
make separate directories for each databaseon d:\. and further on extending
it to sub directories for data and log files?
2. Should i copy all objects such as logins, DB plans, jobs etc. from the
old server or is it a better a practice to start all the plans over (create
new plans) to achieve better results and only copy the databases?
3. What is a good strategy for backup plans? For Log Files? For Primary
Files?
4. How to come up with a good Disaster Recovery Plan? What are all the
things you need to have in order to create a good DR plan? what is a good
way to test it?
5. What is the best way to secure SQL server? Who should have what
access? Which people should have access to the server itself? And how can
i give people read only access to the databases if they have access to the
server? Do they even need access to the server? How can they only have
read access to the SQL server databases? What tools do i need? Since i
have to use remote desktop to conncet to the servers, how can i give my
clients that just want read access to the all the data files including log
files? What do they need installed / or use in order to achieve this?
6. Is there any way you can come up with Roles scheme for certain users?
Lets say a particular group of users should have a certain permissions? Can
we create a something like that? that need to be done on the OS level
rather than SQL level.?
I know this is asking for a lot, but its really important to me, your
valuable knowledge on all this issues would be much much appreciated?
Thank you guys very much
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Well i've been given a big job of copying all the databases from an old
> server to a new server. In order to provide better security, availabilty,
> performance.
> My servers are in a DMZ, so i have to use remote desktop/terminal services
> to connect to it.
> 1. I have two logical partitions in the server. Is it a good practice to
> store the OS and SQL server software itself on C:\ and all the data on
> d:\??(Will it help me in anyways to achieve better performance? Can i
> make separate directories for each databaseon d:\. and further on
extending
> it to sub directories for data and log files?
It really doesn't make a difference performance wise if they are the same
physical volume.
However, an argument can be made for maintenance to at least put the
databases on the D: drive.
I would not do a separate directory for each DB though.

> 2. Should i copy all objects such as logins, DB plans, jobs etc. from
the
> old server or is it a better a practice to start all the plans over
(create
> new plans) to achieve better results and only copy the databases?
>
"It depends". It really does.
In my recent move, I moved all the logins, etc. There's a KB article on
this.

> 3. What is a good strategy for backup plans? For Log Files? For Primary
> Files?
>
I prefer to back them up to a NAS via a UNC. From there to tape is also
recommended. Do as often as business requirements dictate.

> 4. How to come up with a good Disaster Recovery Plan? What are all the
> things you need to have in order to create a good DR plan? what is a
good
> way to test it?
>
First, determine your needs. Are you a 24/7 company expecting 100% uptime.
How much recovery time is allowed. (i.e. if you have to be up and running in
5 minutes you may go with clustering or log-shipping and a lot of additional
cost. If you can wait 5 hours, just restoring from a backup may be ok.)
Again, what are the business needs?

> 5. What is the best way to secure SQL server?
MS has some white papers on this. Ideally give as little permissions as
possible.

>Who should have what
> access? Which people should have access to the server itself? And how
can
> i give people read only access to the databases if they have access to the
> server? Do they even need access to the server?
Generalyl not.

> How can they only have
> read access to the SQL server databases?
Read up on DB Roles.
DBdatareader may work for what you want.

> What tools do i need? Since i
> have to use remote desktop to conncet to the servers, how can i give my
> clients that just want read access to the all the data files including log
> files? What do they need installed / or use in order to achieve this?
> 6. Is there any way you can come up with Roles scheme for certain users?
> Lets say a particular group of users should have a certain permissions?
Can
> we create a something like that? that need to be done on the OS level
> rather than SQL level.?
>
Well, I can't answer all your questions, but hopefully this gives you a
start.

> I know this is asking for a lot, but its really important to me, your
> valuable knowledge on all this issues would be much much appreciated?
> Thank you guys very much
>
|||For your answer of question #1 whats the reason that you should not create a
separate directory for each DB?
As far as how critical the Db' are-- the server holds all the data for
different websites, so i guess they are pretty critical. so whats the most
cost effective DR plan we can establish?
thanks for all your help
And thanks for all your help so far
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:vQ_dd.313544$bp1.178867@.twister.nyroc.rr.com. ..[vbcol=seagreen]
> "Shash Goyal" <Shash703@.gmail.com> wrote in message
> news:%23J1QB78tEHA.3916@.TK2MSFTNGP10.phx.gbl...
availabilty,[vbcol=seagreen]
services[vbcol=seagreen]
to[vbcol=seagreen]
i[vbcol=seagreen]
> extending
> It really doesn't make a difference performance wise if they are the same
> physical volume.
> However, an argument can be made for maintenance to at least put the
> databases on the D: drive.
> I would not do a separate directory for each DB though.
>
> the
> (create
> "It depends". It really does.
> In my recent move, I moved all the logins, etc. There's a KB article on
> this.
Primary[vbcol=seagreen]
> I prefer to back them up to a NAS via a UNC. From there to tape is also
> recommended. Do as often as business requirements dictate.
the
> good
> First, determine your needs. Are you a 24/7 company expecting 100%
uptime.
> How much recovery time is allowed. (i.e. if you have to be up and running
in
> 5 minutes you may go with clustering or log-shipping and a lot of
additional[vbcol=seagreen]
> cost. If you can wait 5 hours, just restoring from a backup may be ok.)
> Again, what are the business needs?
>
> MS has some white papers on this. Ideally give as little permissions as
> possible.
> can
the[vbcol=seagreen]
> Generalyl not.
>
> Read up on DB Roles.
> DBdatareader may work for what you want.
log[vbcol=seagreen]
users?
> Can
> Well, I can't answer all your questions, but hopefully this gives you a
> start.
>
>
|||"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:e$fJvu%23tEHA.3476@.TK2MSFTNGP14.phx.gbl...
> For your answer of question #1 whats the reason that you should not create
a
> separate directory for each DB?
No need in my book. Just extra path info to type, etc.

> As far as how critical the Db' are-- the server holds all the data for
> different websites, so i guess they are pretty critical. so whats the
most
> cost effective DR plan we can establish?
>
Again, what's the cost if the sites go down?
I deal with sites that downtime is measured in thousands of dollars per
minute. Even then it was hard to justify a clustered server configuration.
(Which can run $50K and up. Since list price for SQL Server 2000 Enterprise
Edition is ~$20K/CPU license, it gets expensive very quickly.)
Before that, I had log-shipping. Still required two servers, but I didn't
need a SAN or SQL 2000 EE licenses.
At one point it was simply, "make sure the hardware is really really
robust."
So, again, how much can you pay?
As a consultant I could design plans that cost next to nothing to cost
$250K. It would all depend on what a client needs and is willing to pay.
The usual test is ask your business team "what's service level agreement do
I need to provide for." Then go away, figure out how much it will cost and
then go back to them. I find generally folks very quickly get much more
realistic in their needs.
(i.e. they may say, "we want 99.999% uptime, guaranteed." You come back
with a $250K price tag and then all of a sudden 99% uptime (which you can do
for say $25K) is MUCH more palatable to them. :-)

> thanks for all your help
>
> And thanks for all your help so far
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:vQ_dd.313544$bp1.178867@.twister.nyroc.rr.com. ..
old[vbcol=seagreen]
> availabilty,
> services
practice[vbcol=seagreen]
> to
Can[vbcol=seagreen]
> i
same[vbcol=seagreen]
from[vbcol=seagreen]
> Primary
> the
> uptime.
running[vbcol=seagreen]
> in
> additional
how[vbcol=seagreen]
> the
my[vbcol=seagreen]
> log
this?[vbcol=seagreen]
> users?
permissions?
>

Database deployment - SQL Express

I have an application being developed in VS 2005 using SQL Express. I've built up some new roles and have several users pre-populated that I'd like to deploy to a shared hosting evironment.

The problem is that I seem to keep getting:

Database 'd:\hosting\member\asanga\App_Data\ASPNETDB.mdf' already exists. Choose a different database name.
Cannot attach the file 'd:\hosting\member\r2agility\site1\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.

I saw another post with a similar issue, but that resolution has not seemed to help. Can anybody give me insights as to where the "asanga" is coming from? That might help me diagnose the issue.

Thanks in advance

Problem solved. Just had to rename the "Initial Catalog" value from ASPNETDB to ASPNETDB1