| Author |
 Topic  |
|
asher
Starting Member
Netherlands
36 Posts |
 Posted - 12/08/2012 : 09:14:49
|
<<If you install an applicaiton on my computer that creates a database and I unistall that program, I would want the database removed as well. That seems like a pretty common pattern from the database applications I've used.>>
This is precisely the issue. Thank you. I fail to understand why a program can generate a database to work with henceforth and why, when the user doesn't want to use the application any longer, that same user, using the same box, and the same application cannot delete the database, with code that is as simple as the code to generate the database in the first place. If his privileges were good enough to create a database, surely they should be good enough to get rid of it again. |
 |
|
|
asher
Starting Member
Netherlands
36 Posts |
Posted - 12/08/2012 : 11:57:54
|
| After running my "drop database code", I expected the directory entries for the database to have disappeared. I now suspect that this assumption was erroneous, namely that dynamically removing the database, still leaves the requirement to get rid of them physically. Would someone please confirm that this is the case? Many thanks. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 12/10/2012 : 13:31:48
|
quote:
Originally posted by Michael Valentine Jones
The code below should do the job.
Before running it, make sure that the login you are using has sysadmin privileges and does not have the database to be dropped as its default database. It would also be good to check that no other logins have that database as the default database. If you drop a user’s default database, then they will no longer be able to login.
USE MASTER
GO
ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatebase]
CODO ERGO SUM
You are correct. I was thinking to set it to single user mode, but wrote out OFFLINE. I'm not sure if one way is better than the other..
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DatabaseName]; |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 12/10/2012 : 13:33:01
|
quote:
Originally posted by asher
After running my "drop database code", I expected the directory entries for the database to have disappeared. I now suspect that this assumption was erroneous, namely that dynamically removing the database, still leaves the requirement to get rid of them physically. Would someone please confirm that this is the case? Many thanks.
I guess it depending on where you created the files. If you just created them in the default location, I think a drop would be sufficient. |
|
|
|
asher
Starting Member
Netherlands
36 Posts |
Posted - 12/10/2012 : 13:39:29
|
quote:
Originally posted by Michael Valentine Jones
The code below should do the job.
Before running it, make sure that the login you are using has sysadmin privileges and does not have the database to be dropped as its default database. It would also be good to check that no other logins have that database as the default database. If you drop a user’s default database, then they will no longer be able to login.
USE MASTER
GO
ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatebase]
CODO ERGO SUM
|
|
|
|
asher
Starting Member
Netherlands
36 Posts |
Posted - 12/10/2012 : 13:42:17
|
[quote]Originally posted by Michael Valentine Jones
The code below should do the job.
Before running it, make sure that the login you are using has sysadmin privileges and does not have the database to be dropped as its default database. It would also be good to check that no other logins have that database as the default database. If you drop a user’s default database, then they will no longer be able to login.
USE MASTER
GO
ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatebase]
------------------
How would this code be deployed in the code I posted previously -- apologies for asking this question I am totally new to Sql. I would be grateful if you would please show me.
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 12/10/2012 : 16:54:16
|
quote:
Originally posted by Lamprey
quote:
Originally posted by Michael Valentine Jones
The code below should do the job.
Before running it, make sure that the login you are using has sysadmin privileges and does not have the database to be dropped as its default database. It would also be good to check that no other logins have that database as the default database. If you drop a user’s default database, then they will no longer be able to login.
USE MASTER
GO
ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatebase]
CODO ERGO SUM
You are correct. I was thinking to set it to single user mode, but wrote out OFFLINE. I'm not sure if one way is better than the other..
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DatabaseName];
I avoid setting to SINGLE_USER because if someone else grabs the connection first, it can be a real pain to get them out.
With setting offline and back online, you can just keep trying.
CODO ERGO SUM |
|
|
|
asher
Starting Member
Netherlands
36 Posts |
Posted - 12/11/2012 : 04:45:28
|
Re: USE MASTER
GO
ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [MyDatebase] ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [MyDatebase]
How would I deploy these lines in a C# environment?
Would it be like this:?
command_string = "USE MASTER";
command_string += "GO";
command_string += "ALTER DATABASE [MyDatebase] OFFLINE WITH ROLLBACK IMMEDIATE";
and so forth. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/11/2012 : 09:58:26
|
GGO has to be on a line by itself. So you might try command_string += "\nGO\n";
|
Edited by - sunitabeck on 12/11/2012 09:59:15 |
|
|
|
Topic |
|
Dropping a database
