Restoring database problem

i have developed an application where in one form i placed two buttons, backup and restore. when i want to take backup from database to which i currenly login, i can .

But when i want to restore database that bakup to database to which i currently login, i get an exception.

So then add following method

myConnection.Close()

RestoreBackup();

even though it is not restoring.

the resore backup method is following

public void RestoreBackup()

{

// If there was a SQL connection created

if (srvSql == null)

{

Connect();

}

if (srvSql != null)

{

openBackupDialog.InitialDirectory = "./Backup";

openBackupDialog.DefaultExt = "bak";

// If the user has chosen the file from which he wants the database to be restored

if (openBackupDialog.ShowDialog() == DialogResult.OK)

{

// Create a new database restore operation

Restore rstDatabase = new Restore();

// Set the restore type to a database restore

rstDatabase.Action = RestoreActionType.Database;

// Set the database that we want to perform the restore on

rstDatabase.Database = "test";

// Set the backup device from which we want to restore, to a file

BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);

// Add the backup device to the restore type

rstDatabase.Devices.Add(bkpDevice);

// If the database already exists, replace it

rstDatabase.ReplaceDatabase = true;

rstDatabase.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(rstDatabase_Complete);

// Perform the restore

try

{

rstDatabase.SqlRestore(srvSql);

}

catch (FailedOperationException ex)

{

//Log.WriteLine("");

//Log.WriteLine("Error: (BackupManager.RestoreBackup)");

//Log.Write(ex);

MessageBox.Show("the Database which which u want to restore is in use kindly close connection first");

throw new Exception("Error while restoring backup.", ex);

}

}

}

else

{

throw new Exception("Could not connect to database.");

}

}

YOu will either have to drop all existing connections to the database first. Current Connections won′t be dropped automatically, see my post on the blog for this issue concerning DROP DATABASE:

http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Two things you must remember when attempting to restore: 1) you must have sysadmin or dbcreator privileges, and 2) you cannot be connected to the database you're attempting to restore. Set your current database to 'master' before issuing the restore command.

|||

How to drop all connections and

How to master,

Thanks

|||Hi,

drop all connections is listed in the blog. The default database can be tweaked by either changing the default database of the user, or using it directly within the connection string -> Initial Catalog=master

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thank you sir, you really helped me, my problem solved by following steps

public void RestoreBackup()
{
Global.con.Close(); // connection of my database for normal transaction

string databaseName = "test"; //database which i want to restore from test.bak

if (srvSql == null) // Server
{

Connect1(); //Opening a new connection (conForMasterDatabase) to master database

}

srvSql.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));

..................

//// restore database codes

..............................

Global.conForMasterDatabase.Close();

Global.con.Open(); // reopen connection for normal use

///Global is my class

}

Now please tell me how to restore and backup from remote location