lee88
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;Persi st 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.SqlSe rver")
Set oBackup = CreateObject("SQLDmo.Backu p")
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?
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;Persi
& "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.SqlSe
Set oBackup = CreateObject("SQLDmo.Backu
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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?
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.
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.
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
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
Yep that is the way to go. You can also zip it up, prior to moving to your laptop.