Backing Up Database from Remote Server to Local in VB.NET

Question

Hi,

I am making a VB.NET application that can download/backup the database that is currently on a remote server.

I have Remote Server IP,Username,Password and Database name. I am also able to connect to it.

But i don't know what to do after connecting to it. I don't know what all files are need to be backed up. ( i think database and log file both must be backed up, i am not sure )

please let me know that basic commmands that i will need to backup the whole database.

Thanks in advance.

Answer 1

Assuming it's a Microsoft SQL Server Database, then you can backup the database to a single file using the BACKUP DATABASE command.

Backing Up: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Restoring: [same URL as above, not got enough rep] /ms186858.aspx

Backup Example:

BACKUP DATABASE AdventureWorks 
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH FORMAT;
GO

You could write this into a stored procedure and then call it in VB using a SQLCommand object. Here's a basic example:

Dim objCommand As SqlCommand = Nothing
Dim objConnection as SQLConnection 
Try
        objConnection = new SQLConnection(sConnectionString)
        objConnection.Open()
        objCommand = New SqlCommand("P_YOUR_INSERT_SPROC", mobjConnection)
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.Parameters.Add(New SqlParameter("@SomeParam", pParamValue))
        objCommand.ExecuteNonQuery()
        Return True


Catch ex As Exception
    Throw
    Return False
Finally
    objCommand = Nothing
    If objConnection.State = ConnectionState.Open Then
        objConnection.Close()
    End If
End Try

If you need to move the backup off the server and bring it back down locally then you can use something like FTP or something to bring the actual file down. Or.. if you just wanted to store it remotely and be able to restore it at will then you can name it to something which you can store, which gives you enough information to generate the RESTORE function.