SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dropping a database
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

asher
Starting Member

Netherlands
36 Posts

Posted - 12/08/2012 :  09:14:49  Show Profile  Reply with Quote
<<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.
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/08/2012 :  11:57:54  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/10/2012 :  13:31:48  Show Profile  Reply with Quote
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];
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/10/2012 :  13:33:01  Show Profile  Reply with Quote
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.
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/10/2012 :  13:39:29  Show Profile  Reply with Quote
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

Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/10/2012 :  13:42:17  Show Profile  Reply with Quote
[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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/10/2012 :  16:54:16  Show Profile  Reply with Quote
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
Go to Top of Page

asher
Starting Member

Netherlands
36 Posts

Posted - 12/11/2012 :  04:45:28  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  09:58:26  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000