Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dropping a database

Author  Topic 

asher
Starting Member

36 Posts

Posted - 2012-12-06 : 07:08:28
I have been unable to find code for dropping a database. I am aware that "dropdatabase" can be put into a command string, but doing that has lead to error messages. 1. Or is removing the database entries in the directory simply enough? 2. Does someone have a simple code example for dropping a database?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-06 : 07:12:46
DROP DATABASE YourDatabaseName --------> will drops entire database

--
Chandu
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 07:14:09
You can use
DROP DATABASE YourDataBaseNameHere;
See more details in this article: http://msdn.microsoft.com/en-us/library/ms178613.aspx

In particular, "You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER."
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-12-06 : 07:16:39
error messages? Is it possible to post them here?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Mission123
Starting Member

1 Post

Posted - 2012-12-06 : 07:24:42
I have been unable to find code for dropping a database. I am aware that "dropdatabase" can be put into a command string, but doing that has lead to error messages. 1. Or is removing the database entries in the directory simply enough? 2. Does someone have a simple code example for dropping a database?

Re: Could you post the screenshot with error message.


--Sri
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-06 : 07:37:44
Code:

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using InfraStructure;

namespace FilesAndDatabase
{
public sealed class DropDatabase
{
public DropDatabase()
{
try
{
string file = @"c:\...\... .mdf";
if (File.Exists(file))
{
SqlConnection connection = new SqlConnection
("Server =.\\Sqlexpress;DataBase=[...];Integrated Security=true");
string command_string = "DROP DATABASE[...]";
SqlCommand command = new SqlCommand(command_string, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception x)
{
new Warning(x.Message);
}
}
}
}


Error message:

Cannot open database "[...Database]" requested by the login. The login failed.
Login failed for user 'VOS\...'.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 08:15:23
In connection string, specify Database=master. Also, make sure the user has sufficient privileges. "Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role."
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-06 : 08:15:57
I have posted the code and the error message as requested. Can someone assist?
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-06 : 08:25:31
Changing the name of the database to master does not solve the problem. "Cannot open database "master" requested by the login. The login failed. Login failed for user ""..."". How do I grant CONTROL permission?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 09:14:53
quote:
Originally posted by asher

Changing the name of the database to master does not solve the problem. "Cannot open database "master" requested by the login. The login failed. Login failed for user ""..."". How do I grant CONTROL permission?

Connect to the server using a admin account and security -> logins right click on the login name you want to use, properties, Usermapping, select the database and grant the db_owner role.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-06 : 17:45:15
The application is to be sold on the market; if I am not misunderstanding what sunita beck is saying, the procedure described (Connect to the server etc.) would not seem to allow for that. Apart from this, is sunita beck referring to SQL server manager? I have installed sqlserver.exe - and nothing else - which does not seem to permit doing what is being suggested.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-07 : 04:05:42
Is simply deleting the fies in the directory OK?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 07:20:03
quote:
Originally posted by asher

The application is to be sold on the market; if I am not misunderstanding what sunita beck is saying, the procedure described (Connect to the server etc.) would not seem to allow for that. Apart from this, is sunita beck referring to SQL server manager? I have installed sqlserver.exe - and nothing else - which does not seem to permit doing what is being suggested.

Are you DEVELOPING an application to be sold on the market, or are you working with an application that you bought?

If you are developing an application to be sold on the market, you should install SQL Server Management Studio (SSMS) in your development environment. That does not mean that you would need to install SSMS on every client machine where your application will be deployed to. SSMS is THE tool you would use to develop the database side of your application.

If the application is something that you bought from a vendor, the best course of action would be to consult the vendor.

Also, it is unusual for a client application to be dropping and recreating databases. So if you are developing an application, and if that calls for dropping databases, there may be room for design improvements.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 07:20:50
quote:
Originally posted by asher

Is simply deleting the fies in the directory OK?


Do you mean the mdf and ldf files? That would NOT be the right thing to do if you want to drop a database.
Go to Top of Page

asher
Starting Member

36 Posts

Posted - 2012-12-07 : 13:22:27
Dunita Beck Hello,

The situation is simpler than you assume. I want a client to be able to delete the application, get rid of all traces, that is all. I normally develop Windows applications using files, so this is a new ball game for me. If I create a database dynamically from within the application, which I do, I would expect it to be possible to delete the database dynamically as well, without a lot of paraphernalia. If the user credentials are good enough to create a database, why are they not good enough to get rid of it again, without running into all sorts of error messages.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-07 : 13:57:50
quote:
Originally posted by asher

Dunita Beck Hello,

The situation is simpler than you assume. I want a client to be able to delete the application, get rid of all traces, that is all. I normally develop Windows applications using files, so this is a new ball game for me. If I create a database dynamically from within the application, which I do, I would expect it to be possible to delete the database dynamically as well, without a lot of paraphernalia. If the user credentials are good enough to create a database, why are they not good enough to get rid of it again, without running into all sorts of error messages.

I wouldn't be able to answer that question without being able to access your code and the database server. I could guess, but several of my guesses have been wrong. The shortest path to victory would be to do what I already suggested.

But, perhaps some of the experts on the forum would be able to offer better suggestions.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-07 : 15:50:48
If you want to drop the database the command(s) are pretty simple. However, if you don't have access to drop the database, then that's a different issue.

If you just need to drop the database, I'd also suggest adding an ALTER first to make sure no one has it locked. Make sure you are exucuting in the MASTER db and run:
ALTER DATABASE <Database Name> OFFLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE <Database Name>
GO
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-07 : 16:15:21
quote:
Originally posted by asher

Dunita Beck Hello, Hello it is Sunita

The situation is simpler than you assume. I want a client to be able to delete the application, get rid of all traces, that is all. I normally develop Windows applications using files, so this is a new ball game for me. If I create a database dynamically from within the application, which I do, I would expect it to be possible to delete the database dynamically as well, without a lot of paraphernalia. If the user credentials are good enough to create a database, why are they not good enough to get rid of it again, without running into all sorts of error messages.


This is bad design to dynamically drop database.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-07 : 16:55:46
quote:
Originally posted by sodeep

[quote]Originally posted by asher
This is bad design to dynamically drop database.

I don't agree (unless I'm misunderstanding "dynamically"). 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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-07 : 18:19:45
quote:
Originally posted by Lamprey

If you want to drop the database the command(s) are pretty simple. However, if you don't have access to drop the database, then that's a different issue.

If you just need to drop the database, I'd also suggest adding an ALTER first to make sure no one has it locked. Make sure you are exucuting in the MASTER db and run:
ALTER DATABASE <Database Name> OFFLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE <Database Name>
GO




If you drop the database while it is OFFLINE, it will not delete the database files, so you would have to do that manually later.




CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-07 : 18:20:44
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
    Next Page

- Advertisement -