backup and restore sql database

Expand Post »

m using visual studio 2005 and sql server 2005.
i want to create a backup of the sql server which m doin using the following code..

C# Syntax (Toggle Plain Text)
if (saveBackupDialog.ShowDialog() == DialogResult.OK)
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = @"backup database dbname to disk =@loc with init,stats=10";
                cmd.Parameters.Add("@loc", SqlDbType.VarChar);
                cmd.Parameters["@loc"].Value = saveBackupDialog.FileName.ToString() + ".bak";
                cmd.Connection = Module1.sqlcon;
                Module1.sqlcon.Open();
                cmd.ExecuteNonQuery();
                Module1.sqlcon.Close();
            }
if (saveBackupDialog.ShowDialog() == DialogResult.OK)
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = @"backup database dbname to disk =@loc with init,stats=10";
                cmd.Parameters.Add("@loc", SqlDbType.VarChar);
                cmd.Parameters["@loc"].Value = saveBackupDialog.FileName.ToString() + ".bak";
                cmd.Connection = Module1.sqlcon;
                Module1.sqlcon.Open();
                cmd.ExecuteNonQuery();
                Module1.sqlcon.Close();
            }

however m unable to restore the backup.
ive used a couple of codes

C# Syntax (Toggle Plain Text)
if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
{
 
Restore rDatabase = new Restore();
 
// Set the restore type to a database restore
rDatabase.Action = RestoreActionType.Database;
 
// Assign a db to restore operation
rDatabase.Database = "coloursoft";
 
// Set the backup device to restore from file
BackupDeviceItem bkDevice = new BackupDeviceItem(openFileDialog1.FileName, DeviceType.File);
 
// Add the backup device to the restore type
rDatabase.Devices.Add(bkDevice);
 
// Replace the Db if already exists
rDatabase.ReplaceDatabase = true;
Server srv = new Server("server = ABC;uid=sa;pwd=sasa");
 
if (File.Exists(openFileDialog1.FileName))
// restore
rDatabase.SqlRestore(srv);
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}
if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                try
{

Restore rDatabase = new Restore();

// Set the restore type to a database restore
rDatabase.Action = RestoreActionType.Database;

// Assign a db to restore operation
rDatabase.Database = "coloursoft";

// Set the backup device to restore from file
BackupDeviceItem bkDevice = new BackupDeviceItem(openFileDialog1.FileName, DeviceType.File);

// Add the backup device to the restore type
rDatabase.Devices.Add(bkDevice);

// Replace the Db if already exists
rDatabase.ReplaceDatabase = true;
Server srv = new Server("server = ABC;uid=sa;pwd=sasa");

if (File.Exists(openFileDialog1.FileName))
// restore
rDatabase.SqlRestore(srv);
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

i get the following error

Restore failed for Server 'server = ABC;uid=sa;pwd=sasa'.

Similar Threads

SQL Server 7.0 BackUP and Restore Code in Visual Basic 4 / 5 / 6
VB backup and restore program in Visual Basic 4 / 5 / 6
back-up & restore mysql database in runtime(help pls) in Visual Basic 4 / 5 / 6
Remote MS SQL database update issue in MS SQL
SQL Server 2005 - Merge Replication in MS SQL

babbu

Reputation Points: -1

Solved Threads: 23

Posting Whiz in Training

Offline

207 posts
since Jun 2009

Permalink

Nov 5th, 2009

Re: backup and restore sql database

also tried the following code

C# Syntax (Toggle Plain Text)
SqlConnection connect;
                    string con = "Data Source = localhost; Initial Catalog=master ;Integrated Security = True;";
                    connect = new SqlConnection(con);
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connect;
                    cmd.CommandText = "use master";
                    connect.Open();
                    cmd.ExecuteNonQuery();
 
                    cmd.CommandText = @"restore database coloursoft from disk = @loc with replace, MOVE 'coloursoft' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\coloursoft.mdf',
                    MOVE 'coloursoft_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\coloursoft_Log.ldf'
                    ";
                    cmd.Parameters.Add("@loc", SqlDbType.VarChar);
                    cmd.Parameters["@loc"].Value = openFileDialog1.FileName.ToString();
 
                    Module1.sqlcon.Close();
                    cmd.ExecuteNonQuery();
                    connect.Close();
SqlConnection connect;
                    string con = "Data Source = localhost; Initial Catalog=master ;Integrated Security = True;";
                    connect = new SqlConnection(con);
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connect;
                    cmd.CommandText = "use master";
                    connect.Open();
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = @"restore database coloursoft from disk = @loc with replace, MOVE 'coloursoft' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\coloursoft.mdf',
                    MOVE 'coloursoft_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\coloursoft_Log.ldf'
                    ";
                    cmd.Parameters.Add("@loc", SqlDbType.VarChar);
                    cmd.Parameters["@loc"].Value = openFileDialog1.FileName.ToString();

                    Module1.sqlcon.Close();
                    cmd.ExecuteNonQuery();
                    connect.Close();

i get the following exception

Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.

babbu

Reputation Points: -1

Solved Threads: 23

Posting Whiz in Training

Offline

207 posts
since Jun 2009

Permalink

Nov 5th, 2009

Re: backup and restore sql database

problm solved dont no how...thnx ne ways...

babbu

Reputation Points: -1

Solved Threads: 23

Posting Whiz in Training

Offline

207 posts
since Jun 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: Add user control in application

Next Thread in C# Forum Timeline: Saving and Discarding mails

DaniWeb Message

Cancel Changes

User Name
Password