SQL Query: Backup Database by Code

Backup is something very important because we can prevent disaster of losing data. In SQL Server, we can use backup wizards to backup our database, and even more we can schedule the SQL Server to backup automatic.

But, using wizards need IT or database administrator to running this daily routine. Now, how we can backup database without using wizards of SQL Server? It’s simple, because SQL Server provides function to their user to backup database with code.

There are some steps to backup database by code:
1. We must drops backup device or logical device,
2. Then create the new backup device,
3. Backup using code.

Here the codes show us how to backup by code:

Private Sub Backup_Click()
Dim con As New ADODB.Connection

If con.State = 1 Then con.Close
Set con = Nothing
con.Open "Driver={SQL SERVER};Server=fad-notebook;database=master;"

con.Execute "USE master"
con.Execute "EXEC sp_dropdevice master_1"
con.Execute "EXEC sp_addumpdevice 'disk', 'master_1'," & _
"'C:\Documents and Settings\layong.lim\My Documents\backup.dat'"

con.Execute "BACKUP DATABASE master TO master_1"

MsgBox "Success to backup"

End Sub

Explanations:

-- Create a connection to SQL Server --

Dim con As New ADODB.Connection

If con.State = 1 Then con.Close
Set con = Nothing
con.Open "Driver={SQL SERVER};Server=fad-notebook;database=master;"

-- Choose database to be backup --

con.Execute "USE master"

-- Deleting backup device or logical device --

con.Execute "EXEC sp_dropdevice master_1"

-- Creating new backup device or logical device --

con.Execute "EXEC sp_addumpdevice 'disk', 'master_1'," & _
"'C:\Documents and Settings\layong.lim\My Documents\backup.dat'"

-- Backup database into backup device --

con.Execute "BACKUP DATABASE master TO master_1"


Oks. That’s all folks. Enjoy it...