Link to home
Start Free TrialLog in
Avatar of lee88
lee88Flag for United States of America

asked on

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:
http://www.dbazine.com/sql/sql-articles/sharma3

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
Next

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

How can I combine these two code segments to create my local backup?
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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
Avatar of lee88

ASKER

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.
Avatar of lee88

ASKER

OK. Never mind on the VB approach.
Hi,

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

HTH
  David