• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

Backup SQL Server Database from Visual Basic

Hii All ,
I want to give a backup & restore utility for my appliaction .
Application should be able to backup the database
I use VB 6.0 , SQL Server 2000 . SQL Server Anuthentication mode.

My main concern is dump file should be password Protected . So that no one should not be able to restore that File to somewhere & can see my DB Structure

Thanks
rahulbagal


0
rahulbagal
Asked:
rahulbagal
  • 2
2 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
Depending on how your SQL Server is currently setup you will need to run one or two queries.

If you don't already have a "device" setup for backups then you will need to use the sp_adddumpdevice stored procedure to create one. The syntax for the query can be found here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_3iqt.asp

Secondly, or if you already have a dumpdevice set up, you will need to run a "BACKUP DATABASE" query. The syntax for the query can be found here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

Note that the "BACKUP DATABASE" query requires you to specify the name of a pre-exisiting dumpdevice, or the one created by sp_adddumpdevice. The "BACKUP DATABASE" query also allows you to specify a password for the backup so that nobody else can restore it.

Hope this helps.
0
 
aryaomniCommented:
I am giving you some code here that backups data base


Public Function G_F_BackupDatabase(ByVal P_File2Backup As String) As String
   On Error GoTo errorhandler
   Dim L_S_Sql As String
   If gobjCon.G_Connection.State = adStateOpen Then gobjCon.G_Connection.Close
   Set gobjCon = Nothing
   connectDB
   gobjCon.G_Connection.Execute ("USE MASTER")
   L_S_Sql = G_F_FindDesc("SELECT COUNT(*) FROM MASTER.DBO.SYSDEVICES WHERE NAME='MyDump'")
   If val(L_S_Sql) > 0 Then
         gobjCon.G_Connection.Execute ("EXEC SP_DROPDEVICE 'MyDump'")
   End If
   L_S_Sql = "EXEC SP_ADDUMPDEVICE 'DISK','MyDump','" & P_File2Backup & "'"
   gobjCon.G_Connection.Execute (L_S_Sql)
   L_S_Sql = "BACKUP DATABASE " & gstrDbName & " TO MyDump WITH INIT"
   gobjCon.G_Connection.CommandTimeout = 0
   gobjCon.G_Connection.Execute (L_S_Sql)
   gobjCon.G_Connection.Close
   Set gobjCon = Nothing
   connectDB
   G_F_BackupDatabase = retSuccess
   Exit Function
errorhandler:
   G_F_BackupDatabase = retFailed & " : " & Err.Description
End Function

change the query accoding to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp
As suggested by by carl_tawn

gobjCon is a ADO connection class & G_Connection is Public member of type ADODB.Connection



0
 
aryaomniCommented:
P.S. and connectDB method opens connection G_Connection
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now