Friday, February 10, 2012

Backing up the sql database(*.bak) and restoring the Database in VB.net

Hi There,

I want to know how to backup and restore database from VB.net. ie I am trying to put two buttons on my form, Backup and Restore. When I click on Backup a Backup of the SQL Database with extension of *.bak is created. Similarly when I click on restore and select a database with *.bak extension it should restore that database. I saw a software where it created a backup of Sql database with extension of .zip containing the bak file which is password protected. when u restore the database it automatically gets unzipped and the database is restored. Can anyone help me.

Thanks in advance.

Regards,

Amit

You can create stored procedure to perform backup/restore, then call the stored procedure from your code. For example:

use master
go

CREATE PROC sp_backup @.path sysname,@.dbname sysname,
@.dataDevice varchar(100),@.logDevice varchar(100)
as
DECLARE @.fullFileName sysname
SET @.fullFileName=@.path+@.dataDevice
--Create the data backup device.
EXEC sp_addumpdevice 'disk', @.dataDevice,@.fullFileName

--Create the log backup device.
SET @.fullFileName=@.path+@.logDevice
EXEC sp_addumpdevice 'disk', @.logDevice,@.fullFileName

-- Back up the full MyNwind database.
EXEC('BACKUP DATABASE'+@.dbname+' TO'+@.dataDevice)

-- Update activity has occurred since the full database backup.

-- Back up the log of the MyNwind database.
EXEC('BACKUP LOG'+@.dbname+' TO'+@.logDevice)
go

sp_backup 'c:\','Northwind','test_Data','test_Log'

Then call the sp:

string connectionString = @."Data Source=Confute\SQL2000;Initial Catalog=master;Integrated Security=SSPI;";

using (SqlConnection connection =
new SqlConnection(connectionString))
{

SqlCommand command = new SqlCommand("EXEC sp_backup @.path ,@.dbname,
@.dataDevice,@.logDevice", connection);
command.Connection.Open();

command.Parameters.Add("@.path", textBox1.Text)

//add all parameters here
command.ExecuteNonQuery();

}

No comments:

Post a Comment