database still exists after deletion

hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

--Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

--The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

--The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data\\riskDatabase.mdf'";
string database_ldf = "'E:\\liveProgrammes\\cSharpWorkplace\\riskWizard\\ App_Data\\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}Have someone tried to create a database then drop it in runtime?

Database Restore

I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this file.
Try:
restore database MyDB
from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this
file.
|||Hi,
Or from Enterprise manager:-
1. Right click above the databases option
2. All Tasks...Restore database.. Give the database name in "Restore as
database:....
3.CLick the select devices command button
4. Click add and select the backup file
5. Click OK
6. In the main restore screen "Click OK
Thanks
hari
SQL Server mvp
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eqZHAc0sFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Try:
> restore database MyDB
> from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
> news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
> I made a very big mistake. In Enterprise Manager I right clicked on one
> of
> the databases and selected Delete. In addition I answered OK to the
> following dialog box. I realize that this was wrong. Now I need to
> restore
> it. Immediately before deleting I right-clicked, selected all tasks and
> Backup Database. I can see the file in the \\program files\Microsoft SQL
> Sever\MSSQL\BACKUP directory. How do I restore the database using this
> file.
>

Database Restore

I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this file
.Try:
restore database MyDB
from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this
file.|||Hi,
Or from Enterprise manager:-
1. Right click above the databases option
2. All Tasks...Restore database.. Give the database name in "Restore as
database:....
3.CLick the select devices command button
4. Click add and select the backup file
5. Click OK
6. In the main restore screen "Click OK
Thanks
hari
SQL Server mvp
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eqZHAc0sFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Try:
> restore database MyDB
> from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
> news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
> I made a very big mistake. In Enterprise Manager I right clicked on one
> of
> the databases and selected Delete. In addition I answered OK to the
> following dialog box. I realize that this was wrong. Now I need to
> restore
> it. Immediately before deleting I right-clicked, selected all tasks and
> Backup Database. I can see the file in the \\program files\Microsoft SQL
> Sever\MSSQL\BACKUP directory. How do I restore the database using this
> file.
>

Database Restore

I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this file.Try:
restore database MyDB
from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
I made a very big mistake. In Enterprise Manager I right clicked on one of
the databases and selected Delete. In addition I answered OK to the
following dialog box. I realize that this was wrong. Now I need to restore
it. Immediately before deleting I right-clicked, selected all tasks and
Backup Database. I can see the file in the \\program files\Microsoft SQL
Sever\MSSQL\BACKUP directory. How do I restore the database using this
file.|||Hi,
Or from Enterprise manager:-
1. Right click above the databases option
2. All Tasks...Restore database.. Give the database name in "Restore as
database:....
3.CLick the select devices command button
4. Click add and select the backup file
5. Click OK
6. In the main restore screen "Click OK
Thanks
hari
SQL Server mvp
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eqZHAc0sFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Try:
> restore database MyDB
> from disk = 'C:\Program Files\Microsoft SQL Sever\MSSQL\BACKUP\MyDB.bak'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Robert Brown" <RobertBrown@.discussions.microsoft.com> wrote in message
> news:BE05A364-2F57-4FCF-81DE-E4C1A1B2662D@.microsoft.com...
> I made a very big mistake. In Enterprise Manager I right clicked on one
> of
> the databases and selected Delete. In addition I answered OK to the
> following dialog box. I realize that this was wrong. Now I need to
> restore
> it. Immediately before deleting I right-clicked, selected all tasks and
> Backup Database. I can see the file in the \\program files\Microsoft SQL
> Sever\MSSQL\BACKUP directory. How do I restore the database using this
> file.
>

Database remove/delete

How can I remove/delete a database.

I try (below) but not work:

Dim wServer As New Server(strSqlName)

wServer.Databases("DataBaseName").Drop()

Thanks,

Try defining a Database object first, as in this sample:

Dim srv1 As Server
srv1 = New Server("MyServer")
'Define a Database object variable
Dim db1 As Database
'Reference the database
db1 = srv1.Databases("Test_Database")
Console.WriteLine(db1.CreateDate)
'Remove the database.
db1.Drop()

I've tested this and it works.

|||

Thanks Allen.

Very good.

I am tested too and it works very well.

Marsenne

DataBase referencial intergrity check

I allow the user to delete record1 from SQL Table1 and record2 from Table2. The only problem is, record1 and record2 refers to record3 in Table3 and I can′t allow the user to delete record1 if the is a field in record3 with record1 ref. code. I can′t set FK between them cuz there is more than one reference to the same field.

Can someone point the best solution for my problem?

Hope I'm not getting the tables mixed up, but try this:

Table 3 is a "parent" of table1, meaning that every row in table1 must have a matching ref code in table3 (iow, you can't delete a row in table3 if there's a matching ref code in table1)

alter table Table1
add constraint FK_Table1RefCode
foreign key (RefCode)
references Table3(RefCode)

Same goes for table2:

alter table Table2
add constraint FK_Table2RefCode
foreign key (RefCode)
references Table3(RefCode)

I hope I understood your question!

|||

That almost right, codefield1 Table1 and codefield2 Table2 and must ref. the same codefield in Table3(Parent Table); Table1 and 2 both have only two fields. I already added the constraints but Visual C# shows an error message when Insertin'

|||

Mr.Rezende:

Visual C# shows an error message when Insertin'

What's the error?

Also, please post your DDL

database recovery if no backup available

Hi,

If we delete a Database accidentally (no backup exists), Is it possible to recover. How?

Thanks

ven--

If you have dropped the database and with no backup it is not possible to recover, thats why BACKUP is an important arm on the basis availability.|||

Hi Venp,

As Satya said it very crucial to take backup daily basis other wise you will lose your important data, but some third party tolls like Stellar can recovery the files , how ever i only tried with demo version but not for the SQL Database Files, this is FYI only.

Hemantgiri S. Goswami

|||

You can try some third-party file recovery tools to attempt to un-delete the files, and then attach them to the instance.

You might get lucky. Otherwise, there's not much to be done. We can't fix a file that isn't there without a backup.

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup