How to detach System Databases...

We have C:, D: and E: drives on our server box. C: drive is
partitioned and is big enough only to hold Operating system files. D:
and E: drives are what were supposed to be used by developers / dba's
to store / create SQL Server (system and user databases).

Well, some developers installed the entire SQL Server named instance
and their system and user defined databases on the C: drive. Is there
any way to move the system databases (master, msdb, distribution
etc.,) from the C: to the D: / E: drives?

Appreciate any feedback.

Thanks
Jagannathan SanthanamThe master, model, and tempdb databases cannot be detached/attached.

--
-- Anith
( Please reply to newsgroups only )|||Hi

Check out http://support.microsoft.com/defaul...kb;EN-US;304692

John

"Jagannathan Santhanam" <jags_32@.yahoo.com> wrote in message
news:605df08e.0310271102.e5deab7@.posting.google.co m...
> We have C:, D: and E: drives on our server box. C: drive is
> partitioned and is big enough only to hold Operating system files. D:
> and E: drives are what were supposed to be used by developers / dba's
> to store / create SQL Server (system and user databases).
> Well, some developers installed the entire SQL Server named instance
> and their system and user defined databases on the C: drive. Is there
> any way to move the system databases (master, msdb, distribution
> etc.,) from the C: to the D: / E: drives?
> Appreciate any feedback.
> Thanks
> Jagannathan Santhanam|||Hi

Check out http://support.microsoft.com/defaul...kb;EN-US;304692

John

"Jagannathan Santhanam" <jags_32@.yahoo.com> wrote in message
news:605df08e.0310271102.e5deab7@.posting.google.co m...
> We have C:, D: and E: drives on our server box. C: drive is
> partitioned and is big enough only to hold Operating system files. D:
> and E: drives are what were supposed to be used by developers / dba's
> to store / create SQL Server (system and user databases).
> Well, some developers installed the entire SQL Server named instance
> and their system and user defined databases on the C: drive. Is there
> any way to move the system databases (master, msdb, distribution
> etc.,) from the C: to the D: / E: drives?
> Appreciate any feedback.
> Thanks
> Jagannathan Santhanam

How to detach replication dbs?

There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.

Detach/attach is breaking the link between replication and these databases.

Depending on some specifics, there may be several ways to get what you want to do.

First, knowing what version and edition will tell us what tools we have to work with.

Off the top of my head, you could tear down your replication setup, detach/attach, and recreate the replication config. Not pretty, but you could automate it with scripts.

You can use backup/restore instead of detach/attach.

You could possibly create database snapshots of all the databases, and then to reset use RESTORE DATABASE <dbname> FROM DATABASE_SNAPSHOT=<snapshot name> to limit the amount of data being moved.

|||The situation is: my project is partitioned in two db servers, which all use SQL Server 2005. db01 is on server01 and has replication in server02, db02 is on server02 and has replication in server01. Each time there is a new build of the project, I have to do performance test for it. Unluckily, prepare for the performance test data need 2 days, each time do deployment for the new build, the db will be re-deployed, so all data will lose. I have keep the old build database and data, the most important thing is how to resotore data into new build. I ever used "bcp" to export and import data. But I don't know when replication will end up. So I think maybe detach/attach can help.

How to detach properly using SQL Server Express?

It’s easy to attach a database file in the connection string using AttachDbFilename. And that file gets closed when all connections to that database are closed (one might have to clear the connection pool, or set the connection to use master). The database file can now be moved or deleted. Sounds good...

...but that database still shows up in the attached databases! (select * from sysdatabases). So over time one could end up with hundreds of attached databases (especially if an application uses the attached databases like document files that users open and close frequently).

Question 1: Is there a performance hit having hundreds of attached databases?

OK, one can try to detach the database when the application is done with a specific file, using sp_detach_db after checking if other connections to that database are open (SELECT count(*) FROM master.dbo.sysprocesses WHERE dbid = DB_ID()). But that still does not detach if the application exists unexpectedly, or if one does not have a connection available anymore (e.g. during finalization when exiting the application). There should be a better way.

Question 2: Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

Question 3: Is there a connection string property that auto-detaches when the connection is closed?

Thanks for any tips

--Ralf

Is there a performance hit having hundreds of attached databases?

Depends on how hard this databases are being used. Perhaps, it would be hard a little to navigate through them in Management Studio or other management tool, but I think it's all.

Does anyone know of a good pattern on how an application should detach (preferably using a managed SqlConnection)?

It looks strange that application attaches database everytime it connected to the server and detaches database everytime it disconneted. Imho, it's unnecessary cycle - just keep databases attached.

especially if an application uses the attached databases like document files that users open and close frequently

Sounds not good. What's the reason to have multiple databases instead of one?

|||

The reason for having multiple databases that are attached and detached frequently is that the user uses our app to open a database just like he would use Word to open a word file. We need to store data acquired from an instrument in easily movable files.

I thought this would be a typical use case for SQL Server Express.

how to detach master db

Hi,
how do I detach master db?
what are the steps?What for?
AMB
"Britney" wrote:

> Hi,
> how do I detach master db?
> what are the steps?
>
>|||Hi Britney,
Look at this thread for common detaching.
http://groups.google.de/groups?q=ti...phx.gbl&rnum=1
But most of them wont work with the master db. Wonder why you want to
attach / detach the master db ?
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Britney" <britneychen_2001@.yahoo.com> schrieb im Newsbeitrag
news:uLDsr1ERFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> how do I detach master db?
> what are the steps?
>|||all I want to ask is if it's possible to do so.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:enPchDFRFHA.1416@.TK2MSFTNGP10.phx.gbl...
> Hi Britney,
> Look at this thread for common detaching.
>
http://groups.google.de/groups?q=ti...phx.gbl&rnum=1
> But most of them wont work with the master db. Wonder why you want to
> attach / detach the master db ?
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Britney" <britneychen_2001@.yahoo.com> schrieb im Newsbeitrag
> news:uLDsr1ERFHA.904@.tk2msftngp13.phx.gbl...
>|||You have to change the start paramters for the SQL Server to do so:
-dD:\MSSQL7\data\master.mdf <--
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf <--
for more details look here:
rl]
HTH, Jens Suessmeyer.
[url]http://www.sqlserver2005.de" target="_blank">http://support.microsoft.com/Defaul...qlserver2005.de
--
"Britney" <britneychen_2001@.yahoo.com> schrieb im Newsbeitrag
news:%23Cdc6LFRFHA.612@.TK2MSFTNGP14.phx.gbl...
> all I want to ask is if it's possible to do so.
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:enPchDFRFHA.1416@.TK2MSFTNGP10.phx.gbl...
> http://groups.google.de/groups?q=ti...phx.gbl&rnum=1
>

How to detach deleted databases?

I asked this question in the Tools General forum but received no response. Does anybody is this forum know how to resolve this problem? Here it is again with a bit more info:

When I ran sseutil -l, I discovered several databases that were still attached. However, they were old test projects that had previously been deleted. How do I detach them if they no longer exist? Using the detach command will not work because it just gives me a message: No valid database name matches the value specified.

Apparently, this question is too difficult. Doesn't anybody have a clue on how to do this?|||Did you try using
sseutil.exe -d<nameofthedatabase>
?
HTH, Jens Suessmeyer.|||Yes, of course. That's the problem. The test databases no longer exist so sseutil can't find them. So it won't detach them.

how to detach a read only database from Sql Express

Need help

I made a mistake an attached a read only database to the server (now it is grey marked).

When I try to select this read only database in the MS SQL Server Management Studio, the server hangs up.

I removed the read only attribute from the .mdf and .ldf File and made a reboot of the server. Still the same problem.

How can I detach this read only database or how can I set the attribute to read/write (always hangs up the server, when I try to access this db)

I tried also:

alter database readOnly_dbname set read_write

or

USE master;
GO
EXEC sp_dboption 'readOnly_dbname', 'read only', 'FALSE';

Many thanks for an answer.

Kusi

Did you use sp_detachdb 'DbName' ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I have the same problem. In management studio, the only thing that you can do is to make delete on the read-only database. You will have an error, but the database will be well released from SQL Server. I didn't find a way to make it back non read-only... by scripting or in the management studio.

Someone has a solution for that problem?

|||

I found a solution. The issue is related to the files security.

Be sure that the .mdf and .ldf files have the NETWORK SERVICE and the SQLServer2005MSSQLUser$YourPc$YourSqlInstanceName security users with Full Control permission checked on them.

How to detach a database with replication db?

There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.you cannot detach a replicated database. You could do backup/restore instead.