Create local backup of remote MS SQL database

I am trying to make a local backup (on my laptop's hard drive) of my remote SQL database.

I have this VB6 code that allows me to connect to my remote SQL database. It works fine:

-- begin code -------------------------------
    dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=...;" _
            & "Initial Catalog=...;Data Source=...;" _
            & "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" _
            & "Workstation ID=...;Use Encryption for Data=False;" _
            & "Tag with column collation when possible=False;User Id=...;PASSWORD=...;"
    If cnn.State = 1 Then
-- end code --------------------------------

An Expert Exchange expert recommended this link to help me move a SQL database:

This link shows the following code to create a backup:

-- begin code --------------------------------
Dim oServer
Dim oDatabase
Dim oBackup
Dim sBAKFilePath

'change this to where ever you want to place your backup files, no trailing
'backslash, we add it below

sBAKFilePath = "C:\BackUp"

'we need a backup object in addition to the sqlserver one

Set oServer = CreateObject("SQLDmo.SqlServer")
Set oBackup = CreateObject("SQLDmo.Backup")
oServer.LoginSecure = True
oServer.Connect "(local)"

'this will do a full backup of every database except TempDB to a file

For Each oDatabase In oServer.Databases
    If UCase(oDatabase.Name) <> "TEMPDB" Then
        oBackup.Database = oDatabase.Name
        'remove any previous backup - same as using T-SQL with init
        oBackup.Initialize = True
        'dynamically create the name of the backup file
        oBackup.Files = sBAKFilePath & "\" & oDatabase.Name & ".bak"
        'set the action property as needed
        '0 = Full backup
        '1 = Differential
        '2 = Specified files only
        '3 = Log backup
        oBackup.Action = 0
        oBackup.SQLBackup oServer
    End If

'clean up
Set oBackup = Nothing
Set oServer = Nothing
MsgBox "Database BackUps Done"
-- end code -----------------------------------

How can I combine these two code segments to create my local backup?
Who is Participating?
David ToddSenior DBACommented:

The key problem is that SQL backup will only backup to drives the server know about.

That is, from the server, you would have to map a drive to your laptop.

I'm thinking that it would be easier to backup the database, then copy/move the backup to your laptop.

I'm guessing that if the backup can fit on your laptop, then there should be short-term space on the server for the backup.

Anthony PerkinsCommented:
>>I'm thinking that it would be easier to backup the database, then copy/move the backup to your laptop.<<
Yep that is the way to go.  You can also zip it up, prior to moving to your laptop.
I would create an agent job that ... 1 backedup the database 2.. zipped the file 3.. copied the file to the destination 4.. removed the backup file form the local location , you could then run on demand or schedule to run daily etc
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

lee88Author Commented:
I am just a poor old VB6 programmer ;) and do not know how to create an agent job. And, I don't know if I have permissions to create and write to an external file on the remote server.

My objective is to move the SQL database (without the help of my hosting company, who has been unresponsive on some other hosting issues) from the remote server to my local hard drive.

The SQL database is not particularly large, but there are about 25 tables. I think I can copy all of the Tables to an Access database (are there database field type and size issues?) on my local hard drive using VB6. But, is it possible to, somehow, copy all of the Views and Stored Procedures to my local hard drive using VB6?

There are many "sort of" issues with downsizing.
You might consider downloading the free MSDB or sql express with the limited management studio and then restoring your backup to that.

Access is a good, small database but it is a different sql dialect (ANSI 89) to SQL Server (ANSI 92 or 99). There will be limitations to the size of your tables and files (less than 255 columns, less than 1 gig of data). Your views will sort of translate to queries. There is nothing like stored procedures in Access. You will be doing a lot of manual checking afterward to see if everything made it.
lee88Author Commented:
OK. Never mind on the VB approach.
David ToddSenior DBACommented:

Look at SQL Express or MSDB.

Of course for ease of use you will want to get the same version as your hosting company.

ONce you have set up your system.

Given that you probably cant create a backup you can access on the hosting company's system, or map a drive, then the next best thing would be imho to script out the database - query analyser or enterprise manager can do that for you.

Apply scripts to your new system and create the database with all the objects.

Next create a DTS package - use the import/export wizard and import the data from your hosting company to your database.

Of couse if you have a licence for the SQL 2000 tools - Query Analyser and Enterprise Manager et al - then you have a licence to run the personal edition of SQL, and you should be able to find a CD with personal edition with your SQL CD ...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.