Create local backup of remote MS SQL database

Posted on 2006-06-05
Medium Priority
Last Modified: 2008-02-26
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?
Question by:lee88
LVL 35

Accepted Solution

David Todd earned 2000 total points
ID: 16837901

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.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 16837926
>>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.
LVL 10

Expert Comment

ID: 16840984
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.


Author Comment

ID: 16841366
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?

LVL 27

Expert Comment

ID: 16842088
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.

Author Comment

ID: 16842111
OK. Never mind on the VB approach.
LVL 35

Expert Comment

by:David Todd
ID: 16846422

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 ...


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question