R: Copy/Clone full DB from SQL Server to local

Question

In R, I have a SQL Server connection with this database:

From this answer I understand that these levels are catalogs (=databases), schemas, and tables. The following code:

library(odbc)
library(DBI)
library(RSQlite)
library(dbi)
confull <- odbc::dbConnect(odbc(), 
                           Driver = "SQL Server", 
                           Server = "XXX")
odbcListObjects(confull, schema="schema")

Yields:

         name    type
1     DBAInfo catalog
2 InBluePrism catalog
3      master catalog
4        msdb catalog
5      tempdb catalog

Questions:

1. How can I extract the full structure tree of this database, not just the catalogs?
2. How can I progammatically save (clone) this whole database (including all tables, schemas, and catalogs) into a local SQLite table?

For the first question I have tried the following:

> all_schemas <- DBI::dbGetQuery(confull, "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA")
> all_schemas
                SCHEMA_NAME
1                       dbo
2                     guest
3        INFORMATION_SCHEMA
4                       sys
5              CCAutomation
6      XXXXXX\\xxxAdmin
7      XXXXXX\\z_swmon
8      NT AUTHORITY\\SYSTEM
9      XXXXXX\\z_Backup
10                 db_owner
11           db_accessadmin
12         db_securityadmin
13              db_ddladmin
14        db_backupoperator
15            db_datareader
16            db_datawriter
17        db_denydatareader
18        db_denydatawriter

For the second question, I have tried:

1. to generate scripts in SQL Server, but I get an error and moreover I would like to keep this programmatic.
2. to just save all the tables given by dbListTables(confull) however, I then lose the information about the catalogs and schemas these tables belong to.

---

EDIT: the following link also contains useful information

Answer 1

I don't know which version of the SQL Server you have. I'm basing it on what I have which is SQL Server 2008 and 2016.

To have CLI tool you can download Export2SqlCE.zip

Description:

SQL Server 2005/2008 command line utility to generate a SQL Compact or SQLite compatible script with schema and data (or schema only)

After downloading you can run it to extract the information using:

Export2SQLCE.exe "Data Source=(local);Initial Catalog=<your_database>;Integrated Security=True" your_database.sql sqlite 

Answer 2

We can use Powerbuilder pipeline of version 9/10/10.5 depends on your SQL Server version. Database and data will be easily migrated all you must know is to create ODBC/Database connections which is the matter of few clicks.

Use a Pipeline object by defining a standard class user object inherited from the built-in Pipeline object in the User Object painter. We can then access the Pipeline events by writing scripts that contain code for the events.

This is how we can execute pipeline by writing the script.

The scenario of this case is we want to pipeline a table from one database to another database. So, first, we need at least 2 transaction objects, which mean we must declare first in the top of the script. Since we have a default database connection SQLCA, we only have declare another new transaction object called SQLCAtarget, which represent for the target database connection. Remember, in this case, SQLCA will be the source of database connection

transaction SQLCAtarget // declare this variable as INSTANT variable

SQLCA.DBMS = 'your source dbms name'
SQLCA.Database = 'your source database name'
SQLCA.LogId = 'your source database login id'
SQLCA.LogPass = 'your source database password'
SQLCA.ServerName = 'your source database server'
CONNECT USING SQLCA;

SQLCAtarget = CREATE transaction
SQLCAtarget.DBMS = 'your target dbms name'
SQLCAtarget.Database = 'your target database login id'
SQLCAtarget.LogPass = 'your target database password'
SQLCAtarget.ServerName = 'your target database server'
SQLCAtarget.LogId = 'your target database login id'
CONNECT USING SQLCAtarget;

Next step, we need to build a pipeline object by clicking the Pipeline painter in the main toolbar. Remember, use MAIN TOOLBAR, if we want to pipeline the data to ANOTHER DATABASE.

Setup the source database and the target database profile, choose the table(s), column(s) and criteria(s), then save as pl_mypipeline.

to begin with, Click on pipeline button from powerbuilder

Choose the source and target of Pipeline

set the table, column and criteria of your pipeline

save your pipeline

Create a window, then put one datawindow object and one button object. We don't need to put dataobject for the datawindow, just keep it blank. And put the script below at clicked event in button object.

integer iReturn
pipeline myPipeline
myPipeline = CREATE pipeline

myPipeline.DataObject = "pl_mypipeline"
iReturn = myPipeline.Start(SQLCA, SQLCAtarget, dw_1)

// always disconnect your database connection
DISCONNECT USING SQLCA;
DISCONNECT USING SQLCAtarget;

iReturn should have 0 (zero) value if the pipeline runs smoothly.