Create local backup of remote MS SQL database

Posted on 2006-06-05
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


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

    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

    Author Comment

    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

    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

    OK. Never mind on the VB approach.
    LVL 35

    Expert Comment

    by:David Todd

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now