Showing posts with label vbnet. Show all posts
Showing posts with label vbnet. Show all posts

Friday, February 24, 2012

Backup and recovery of SQL Server using VB.net

Hi,

I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application.Hope i delivered my question correctly. Any little help is beneficial to me.

-regards

GRK

Try this one out...

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer

Private Sub Form_Load()
On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there

With oDevice
.Type = SQLDMODevice_DiskDump
.Name = "NorthwindBakUp"
.PhysicalLocation = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
End With

SERVER.Connect "Sanjib", "sa"
SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices ="NorthwindBakUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"

BACKUP.SQLBackup SERVER

End Sub

|||

Hi,

Here's my backup class of one of my project that uses Microsoft.SqlServer.Management:

public class BackupManager

{

Server srvSql;

SaveFileDialog saveBackupDialog = new SaveFileDialog();

OpenFileDialog openBackupDialog = new OpenFileDialog();

DatabaseCache dbCache = DatabaseCache.Instance;

private string GetAppPath()

{

return System.IO.Path.GetDirectoryName

(System.Windows.Forms.Application.ExecutablePath);

}

private void Connect()

{

ServerConnection srvConn = new

ServerConnection(dbCache.SqlConnection);

srvSql = new Server(srvConn);

}

private string GetDatabase(SqlConnection conn)

{

string[] connArray = conn.ConnectionString.Split(';');

string toMatch = "AttachDbFilename=";

string match = null;

foreach (string item in connArray) {

if (item.StartsWith(toMatch))

{

match = item.Substring(toMatch.Length);

break;

}

}

if (!String.IsNullOrEmpty(match) ) {

match = match.Replace("|DataDirectory|", GetAppPath());

} else {

throw new Exception(

"Could not extract database path from connection string");

}

return match;

}

public bool MakeBackup()

{

// If there was a SQL connection created

if (srvSql == null) {

Connect();

}

if (srvSql != null)

{

string path = GetAppPath();

path = path += @."\Backup";

if (!Directory.Exists(path))

{

Directory.CreateDirectory(path);

}

saveBackupDialog.InitialDirectory = path;

saveBackupDialog.DefaultExt = "bak";

// If the user has chosen a path

// where to save the backup file

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

{

// Create a new backup operation

Backup bkpDatabase = new Backup();

// Set the backup type to a database backup

bkpDatabase.Action = BackupActionType.Database;

// Set the database that we want to perform a backup on

bkpDatabase.Database = GetDatabase(dbCache.SqlConnection);

// Set the backup device to a file

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

// Add the backup device to the backup

bkpDatabase.Devices.Add(bkpDevice);

// Perform the backup

bkpDatabase.SqlBackup(srvSql);

} else {

return false;

}

} else {

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

}

return true;

}

public void RestoreBackup()

{

//database must first be unloaded before calling this.

// 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 = GetDatabase(dbCache.SqlConnection);

// 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;

// Perform the restore

try

{

rstDatabase.SqlRestore(srvSql);

}

catch (FailedOperationException ex)

{

Log.WriteLine( "" );

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

Log.Write( ex );

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

}

}

}

else

{

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

}

}

}

It's not generic so you'll have to change some of the code (and translate to vb...) You can see the basic idee from the code above.

Good luck,
Charles

|||

Hi ,

forgive me for the late reply.I tried the above code it is running after small changes to the code.thanks a lot

-regards

GRK

|||

Hi,

i hav the same problem...

" I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application"

i used tht abov code.. bt it dnt wok..

so.. plz snd me the code... plz its urgent.....

thnks in advance

siva

|||brallient|||

where i can find DatabaseCache class i downloaded and install SQLServer2005_XMO.msi

but still i cant see that class, other code works fine.

Thanks

|||

Hi,

It's normal, DatabaseCache is my own class that contains all the data of my application :)

Basically you need to remove that and replace dbCache.SqlConnection in the Connect function with the connection you use for your database.

Charles

|||

my connectionString is

connectionString = "Data Source=localhost,1433;Network Library=DBMSSOCN;Initial Catalog=myDb; User ID=sa;Password=saabc;";

when i replaced dbCache.SqlConnection with my own opened connection as "openedConnection", it compiled and executed, but when i click on backup button, i caught by following exception

"Could not extract database path from connection string"

Could you please change the code according to my connection.

Thanks

|||

Ok,

My code assumes that a database file is used. So in your case the GetDatabase method fails to find the db file from the connection string.

Try replacing bkpDatabase.Database = GetDatabase(dbCache.SqlConnection); by

bkpDatabase.Database = "myDb";

or better yet to modify the GetDatabase method to take into account the Initial Catalog key.

Charles

|||Thanks, let me check it

Backup and recovery of SQL Server using VB.net

Hi,

I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application.Hope i delivered my question correctly. Any little help is beneficial to me.

-regards

GRK

Try this one out...

Dim oDevice As New SQLDMO.BackupDevice
Dim BACKUP As New SQLDMO.BACKUP
Dim SERVER As New SQLServer

Private Sub Form_Load()
On Error Resume Next 'If the device already exists an error will result if you try to add it again so just resume next cos its already there

With oDevice
.Type = SQLDMODevice_DiskDump
.Name = "NorthwindBakUp"
.PhysicalLocation = "C:\Documents and Settings\Administrator\Desktop\BACKUP.bak"
End With

SERVER.Connect "Sanjib", "sa"
SERVER.BackupDevices.Add oDevice
BACKUP.Action = SQLDMOBackup_Database
BACKUP.Database = "Northwind"
BACKUP.Devices ="NorthwindBakUp"
BACKUP.BackupSetDescription = "Full BackUp"
BACKUP.BackupSetName = "By Sanjib"

BACKUP.SQLBackup SERVER

End Sub

|||

Hi,

Here's my backup class of one of my project that uses Microsoft.SqlServer.Management:

public class BackupManager

{

Server srvSql;

SaveFileDialog saveBackupDialog = new SaveFileDialog();

OpenFileDialog openBackupDialog = new OpenFileDialog();

DatabaseCache dbCache = DatabaseCache.Instance;

private string GetAppPath()

{

return System.IO.Path.GetDirectoryName

(System.Windows.Forms.Application.ExecutablePath);

}

private void Connect()

{

ServerConnection srvConn = new

ServerConnection(dbCache.SqlConnection);

srvSql = new Server(srvConn);

}

private string GetDatabase(SqlConnection conn)

{

string[] connArray = conn.ConnectionString.Split(';');

string toMatch = "AttachDbFilename=";

string match = null;

foreach (string item in connArray) {

if (item.StartsWith(toMatch))

{

match = item.Substring(toMatch.Length);

break;

}

}

if (!String.IsNullOrEmpty(match) ) {

match = match.Replace("|DataDirectory|", GetAppPath());

} else {

throw new Exception(

"Could not extract database path from connection string");

}

return match;

}

public bool MakeBackup()

{

// If there was a SQL connection created

if (srvSql == null) {

Connect();

}

if (srvSql != null)

{

string path = GetAppPath();

path = path += @."\Backup";

if (!Directory.Exists(path))

{

Directory.CreateDirectory(path);

}

saveBackupDialog.InitialDirectory = path;

saveBackupDialog.DefaultExt = "bak";

// If the user has chosen a path

// where to save the backup file

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

{

// Create a new backup operation

Backup bkpDatabase = new Backup();

// Set the backup type to a database backup

bkpDatabase.Action = BackupActionType.Database;

// Set the database that we want to perform a backup on

bkpDatabase.Database = GetDatabase(dbCache.SqlConnection);

// Set the backup device to a file

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

// Add the backup device to the backup

bkpDatabase.Devices.Add(bkpDevice);

// Perform the backup

bkpDatabase.SqlBackup(srvSql);

} else {

return false;

}

} else {

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

}

return true;

}

public void RestoreBackup()

{

//database must first be unloaded before calling this.

// 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 = GetDatabase(dbCache.SqlConnection);

// 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;

// Perform the restore

try

{

rstDatabase.SqlRestore(srvSql);

}

catch (FailedOperationException ex)

{

Log.WriteLine( "" );

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

Log.Write( ex );

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

}

}

}

else

{

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

}

}

}

It's not generic so you'll have to change some of the code (and translate to vb...) You can see the basic idee from the code above.

Good luck,
Charles

|||

Hi ,

forgive me for the late reply.I tried the above code it is running after small changes to the code.thanks a lot

-regards

GRK

|||

Hi,

i hav the same problem...

" I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application"

i used tht abov code.. bt it dnt wok..

so.. plz snd me the code... plz its urgent.....

thnks in advance

siva

|||brallient|||

where i can find DatabaseCache class i downloaded and install SQLServer2005_XMO.msi

but still i cant see that class, other code works fine.

Thanks

|||

Hi,

It's normal, DatabaseCache is my own class that contains all the data of my application :)

Basically you need to remove that and replace dbCache.SqlConnection in the Connect function with the connection you use for your database.

Charles

|||

my connectionString is

connectionString = "Data Source=localhost,1433;Network Library=DBMSSOCN;Initial Catalog=myDb; User ID=sa;Password=saabc;";

when i replaced dbCache.SqlConnection with my own opened connection as "openedConnection", it compiled and executed, but when i click on backup button, i caught by following exception

"Could not extract database path from connection string"

Could you please change the code according to my connection.

Thanks

|||

Ok,

My code assumes that a database file is used. So in your case the GetDatabase method fails to find the db file from the connection string.

Try replacing bkpDatabase.Database = GetDatabase(dbCache.SqlConnection); by

bkpDatabase.Database = "myDb";

or better yet to modify the GetDatabase method to take into account the Initial Catalog key.

Charles

|||Thanks, let me check it

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();

}