CODEWITHSUNDEEP
c#sqlsql-server-2008
Exitos
Exitos

Reputation: 29740

How do I drop an entire database and recreate it using sql and c#?

I want to write a script that allows me to connect to a sql db, create the db to a textfile and then drop then entire database. Once this is done I would like to be able to re-create the entire database. Is this possible?

Upvotes: 2

Views: 627

Answers (2)

moi_meme
moi_meme

Reputation: 9318

Depends on your purpose but you could use a database project see

with this you can recreate a database on demand.

Upvotes: 0

Brook
Brook

Reputation: 6019

You can use the Microsoft.SqlServer.Management.Smo namespace to generate a script, just like you would in ssms. It might take a few tries to get the ScriptingOptions right, so don't get over zealous and start dropping things right away.

Once you've generated your script, it would be a simple ExecuteNonQuery("drop database foo") to drop your database.

To recreate, use the same namespace to execute the script. Using a normal ExecuteNonQuery will not work because the script will contain "GO", which is not valid sql. The SMO does contain logic to handle that though.

FileInfo file = new FileInfo("C:\\script.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection connection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(connection ));
server.ConnectionContext.ExecuteNonQuery(script);

This is assumed, of course, to be logged in with a user that has adequate permissions on the sql server.

Upvotes: 1

Related Questions