DB BAckup

OuTCasT

Hey.
Can anyone tell me how to back up a database from within my visual studio application ? I have taken a look at SQLDMO but im sure there is a easier way to do it ?

ck9663

There's a BACKUP t-sql command.

The backup file will be on the server, not on your client.

-- CK

OuTCasT

Quote:

Originally Posted by ck9663

There's a BACKUP t-sql command.

The backup file will be on the server, not on your client.

-- CK

See the solution will be installed on the Client PC with the database located in the application.startupPath. Now i jst need to be able to backup the database and then be able to restore it.

OuTCasT

Quote:

Originally Posted by ck9663

There's a BACKUP t-sql command.

The backup file will be on the server, not on your client.

-- CK

Expand|Select|Wrap|Line Numbers

 USE master

EXEC sp_addumpdevice 'disk', 'example', 'c:\backup\'
 
BACKUP DATABASE dbName to example

is this what i mst do

ck9663

Quote:

Originally Posted by OuTCasT

Expand|Select|Wrap|Line Numbers

 USE master

EXEC sp_addumpdevice 'disk', 'example', 'c:\backup\'
 
BACKUP DATABASE dbName to example

is this what i mst do

This 'c:\backup\' is the server's C drive, not the clients.

-- CK

OuTCasT

Quote:

Originally Posted by ck9663

This 'c:\backup\' is the server's C drive, not the clients.

-- CK

when i declare a dump device i know i have to give it a location....

Expand|Select|Wrap|Line Numbers

 use master exec sp_addumpdevice 'disk','" & strCompanyName & "','C:\Backup\Test'
 

that is all fine and it back up to that location.
now with the restore database

you have to restore the database from that some location, say for instance the client moves the backup to another location and then tries to restore the database from there....then it gives me an error

is there no other way that i can do it ?

ck9663

Make your t-sql dynamic (use EXEC). You have to create a little wizard front-end app to do this.

-- CK

GGSoft

This Example in detail explains how to do it?

If Not System.IO.Directory.Exists("C:\backup") Then System.IO.Directory.CreateDirectory("c:\backup")
Dim backupFileName As String = "c:\Backup\BackupOfSameurneo_" + Today.ToShortDateString.Replace("/", ".") + ".bak"
Dim Texts(2) As String
Texts(0) = "use master; if exists (select 1 from sysdevices where name = 'BackupOfSameurneo') exec sp_dropdevice 'BackupOfSameurneo';"
Texts(1) = "exec sp_addumpdevice 'disk', 'BackupOfSameurneo', '" + backupFileName + "';"
Texts(2) = "Backup database sameurneo to backupofsameurneo;"

Dim command As New SqlClient.SqlCommand
Using conn As New SqlConnection(ConnectionStringGlobal)
command.Connection = conn
conn.Open()
Dim i As Int16
For i = 0 To 2
command.CommandText = Texts(i)
command.ExecuteNonQuery()
Next
End Using

OuTCasT

Quote:

Originally Posted by GGSoft

This Example in detail explains how to do it?

If Not System.IO.Directory.Exists("C:\backup") Then System.IO.Directory.CreateDirectory("c:\backup")
Dim backupFileName As String = "c:\Backup\BackupOfSameurneo_" + Today.ToShortDateString.Replace("/", ".") + ".bak"
Dim Texts(2) As String
Texts(0) = "use master; if exists (select 1 from sysdevices where name = 'BackupOfSameurneo') exec sp_dropdevice 'BackupOfSameurneo';"
Texts(1) = "exec sp_addumpdevice 'disk', 'BackupOfSameurneo', '" + backupFileName + "';"
Texts(2) = "Backup database sameurneo to backupofsameurneo;"

Dim command As New SqlClient.SqlCommand
Using conn As New SqlConnection(ConnectionStringGlobal)
command.Connection = conn
conn.Open()
Dim i As Int16
For i = 0 To 2
command.CommandText = Texts(i)
command.ExecuteNonQuery()
Next
End Using

What i did was....

[CODEvb]Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
'Dim sqlComAddumpDevice As SqlCommand = New SqlCommand("use master exec sp_addumpdevice 'disk','" & strCompanyName & "','" & TextBox2.Text & "'", sqlcon)
Dim sqlComBackupDatabase As SqlCommand = New SqlCommand("Backup database " & strCompanyName & " to disk = '" & TextBox2.Text & "'", sqlcon)
Try
sqlcon.Open()
'sqlComAddumpDevice.ExecuteNonQuery()
'MsgBox("Company Backup Device Created", MsgBoxStyle.Information)
sqlComBackupDatabase.ExecuteNonQuery()
MsgBox("Company Backup Complete", MsgBoxStyle.Information)
sqlcon.Close()
Me.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub[/code]

GGSoft

Quote:

Originally Posted by OuTCasT

What i did was....

[CODEvb]Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
'Dim sqlComAddumpDevice As SqlCommand = New SqlCommand("use master exec sp_addumpdevice 'disk','" & strCompanyName & "','" & TextBox2.Text & "'", sqlcon)
Dim sqlComBackupDatabase As SqlCommand = New SqlCommand("Backup database " & strCompanyName & " to disk = '" & TextBox2.Text & "'", sqlcon)
Try
sqlcon.Open()
'sqlComAddumpDevice.ExecuteNonQuery()
'MsgBox("Company Backup Device Created", MsgBoxStyle.Information)
sqlComBackupDatabase.ExecuteNonQuery()
MsgBox("Company Backup Complete", MsgBoxStyle.Information)
sqlcon.Close()
Me.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub[/code]

You must first remove device if its olready exists using system stored procedure exec sp_dropdevice 'YourDeviceName'

OuTCasT

Quote:

Originally Posted by GGSoft

You must first remove device if its olready exists using system stored procedure exec sp_dropdevice 'YourDeviceName'

Ja i noticed that .....i just didnt know how to drop the existing logical device.
you gave me the answer thanks dude.

but i saw there is easier way to do it without creating a logical device

Expand|Select|Wrap|Line Numbers

Backup Database 'DATABASE' to disk = 'LocationOnDrive'

i used a fileOpenDialog to let the user choose where he wants to save the backup, that is saved in a string strSaveLocation and that is used for the LocationOnDrive part. the database is the name of the company he is using on the application at the moment....its a payrolll solution.

with the restore i used

Expand|Select|Wrap|Line Numbers

Restore Database 'DATABASE' from disk = 'locationOnDrive'

used a openfiledialog to get the location of the backup.

DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

re: DB BAckup

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about