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 status check

Hey guys,

A question about database status !

I would like to do 2 checks through T-sql :

* Check if a database exists in my Sql Server

- I found following T-Sql code and it does the trick, so no problem here I guess :

Code Snippet

select * from sys.databases where name = 'Testing'

* Check if the database is Online or Offline

Here I'm having trouble selecting a good approach.

At first I would just launch a query to one of my tables, but it got me thinking, this would create a timeout if the database is offline, not ?

So how could I avoid this timeout ? I found out that the SysDatabases table has a status field I can query, but I can't find any document reference for the difference in Status Online or Offline ( I see a change when I do the test local, but I can't get a int reference to really check the value ).

NOTE : I'm on sql 2000 !

So any suggestions ?

Hey Depechie,

check out this tip: http://www.mssqltips.com/tip.asp?tip1033

success,

hansco

Database spanning multiple partitions.....help

I posted a message earlier, thinking I knew the how. However I didn't
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, ho
w
can I set it up?
Thanks for any help
GordonYou thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordonsql

Database spanning multiple partitions.....help

I posted a message earlier, thinking I knew the how. However I didn't
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, how
can I set it up?
Thanks for any help
GordonYou thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
--
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordon

Database spanning multiple partitions.....help

I posted a message earlier, thinking I knew the how. However I didn't
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, how
can I set it up?
Thanks for any help
Gordon
You thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordon

Database removable

Hello,is possible attach and use the mdf exists in a pen drive or an usb
hard disk ?
What commands are involved in this operation ?
Thanks in advance.
I have a lot of DB allocated on my USB (or Firewire) external disk; even a
pen drive is managed by the OS as an external, removable disk.
The only attention you hav to pay is to remember detach you removable
database when you suppose the next startup of your PC will be without the
external disks containing your databases.
When you need to use such databases you'll attach it again
Gilberto
"Luis Tarzia" wrote:

> Hello,is possible attach and use the mdf exists in a pen drive or an usb
> hard disk ?
> What commands are involved in this operation ?
> Thanks in advance.
>
>
|||I attach the mdf with the sp_attachd but when i execute any sql command the
sql mark an error of access a memory and exit.
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> escribi en
el mensaje news:5BF5A51B-83A5-4A23-B540-B104BBCDD7AC@.microsoft.com...
> I have a lot of DB allocated on my USB (or Firewire) external disk; even
a[vbcol=seagreen]
> pen drive is managed by the OS as an external, removable disk.
> The only attention you hav to pay is to remember detach you removable
> database when you suppose the next startup of your PC will be without the
> external disks containing your databases.
> When you need to use such databases you'll attach it again
> Gilberto
> "Luis Tarzia" wrote:

Database removable

Hello,is possible attach and use the mdf exists in a pen drive or an usb
hard disk ?
What commands are involved in this operation '
Thanks in advance.I have a lot of DB allocated on my USB (or Firewire) external disk; even a
pen drive is managed by the OS as an external, removable disk.
The only attention you hav to pay is to remember detach you removable
database when you suppose the next startup of your PC will be without the
external disks containing your databases.
When you need to use such databases you'll attach it again
Gilberto
"Luis Tarzia" wrote:

> Hello,is possible attach and use the mdf exists in a pen drive or an usb
> hard disk ?
> What commands are involved in this operation '
> Thanks in advance.
>
>|||I attach the mdf with the sp_attachd but when i execute any sql command the
sql mark an error of access a memory and exit.
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> escribi en
el mensaje news:5BF5A51B-83A5-4A23-B540-B104BBCDD7AC@.microsoft.com...
> I have a lot of DB allocated on my USB (or Firewire) external disk; even
a[vbcol=seagreen]
> pen drive is managed by the OS as an external, removable disk.
> The only attention you hav to pay is to remember detach you removable
> database when you suppose the next startup of your PC will be without the
> external disks containing your databases.
> When you need to use such databases you'll attach it again
> Gilberto
> "Luis Tarzia" wrote:
>

Database removable

Hello,is possible attach and use the mdf exists in a pen drive or an usb
hard disk ?
What commands are involved in this operation '
Thanks in advance.I have a lot of DB allocated on my USB (or Firewire) external disk; even a
pen drive is managed by the OS as an external, removable disk.
The only attention you hav to pay is to remember detach you removable
database when you suppose the next startup of your PC will be without the
external disks containing your databases.
When you need to use such databases you'll attach it again
Gilberto
"Luis Tarzia" wrote:
> Hello,is possible attach and use the mdf exists in a pen drive or an usb
> hard disk ?
> What commands are involved in this operation '
> Thanks in advance.
>
>|||I attach the mdf with the sp_attachd but when i execute any sql command the
sql mark an error of access a memory and exit.
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> escribió en
el mensaje news:5BF5A51B-83A5-4A23-B540-B104BBCDD7AC@.microsoft.com...
> I have a lot of DB allocated on my USB (or Firewire) external disk; even
a
> pen drive is managed by the OS as an external, removable disk.
> The only attention you hav to pay is to remember detach you removable
> database when you suppose the next startup of your PC will be without the
> external disks containing your databases.
> When you need to use such databases you'll attach it again
> Gilberto
> "Luis Tarzia" wrote:
> > Hello,is possible attach and use the mdf exists in a pen drive or an usb
> > hard disk ?
> > What commands are involved in this operation '
> > Thanks in advance.
> >
> >
> >

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.