Introduction

This utility will copy an MS SQL Server database. It could fairly easily be modified to work with other DBs, though I have not done so. While this runs from a Windows View, it should work from the web as well - though that has not been tested.

The code

This utility is made up of a BP and a supporting View. The BP does the work, while the View is supplied to easily set the needed properties in the BP and call the BP. The BP can be used by itself if you wish to call it from your own program - just set the properties as the view does and "Send DoCopy of oCopyDB" or modify the function to accept parameters & set the properties. If errors are encountered, it stops the process and returns an error string to the calling View. Easiest way to run it is to download the files, create a new Windows Project, add the View, set up a Connection, and compile & run.

If you look at the code you will see it performs a number of steps:

• Clone the original DB to the new DB (with verification if desired & using 2017 SP3 or later)
• Sets the DB to Read/Write
• Stores & Drops any Foreign Key constraints in the new DB
• Gets the Table names & then builds & runs an SQL string for each Table to insert the data into the new DB - Tables can be skipped - code change needed to do so
• Sets the Recovery Mode for the new DB to either Full or Simple (based on a property)
• Renames the SQL logical filenames to match the new DB Name
• Restores the FK constraints to the new DB
• Truncates the log for the new DB

No changes are made to the original DB!

Note that there is code in the BP that, if the To DB exists, asks you if you want to drop the DB first. This is because the clone process cannot overwrite an existing one. If you will be running the BP unattended, you will want to remove this & do the check in the calling View. There's a comment explaining this in the code and no other user interaction happens in the BP.

The process requires DF 19.0 or later, DAW driver, a ConnectionID, and MS SQL Server 2014 SP3 or later. MS SQL Server 2017 SP3 or later will allow SQL Verification (which is recommended by MS).

It's been tested in 19.0, 19.1, and 20.1. Testing has found that running less than 4GB of RAM may prevent copying all tables - particularly ones with a large number of rows. 8GB is better, as it will run faster. Running locally on a machine with 16GB, a 4GB DB took about 3 minutes to copy.

Chuck Atkinson wrote the Foreign Key code, the code to skip the change tracking table (MSchange_tracking_history), and the Table Drop Code. Bob Worsley & Wil van Antwerpen both made suggestions and helped in testing.

Download

• DBCopyFiles-20230925.zip
• CopyDB-20230329.zip