How To: Create Schema Only Database Copies

Have you ever been asked to make a schema only copy of a database? What is your preferred method to complete this request? Below we will discuss how to create a schema only database copy.

Here are three approaches that are easy to understand and perform. One approach will use PowerShell and the dbatools module. The second method will use built-in SQL Server functionality, depending on your SQL Server version. The third way will use a third-party (paid) tool from Red-Gate software.

PowerShell and dbatools

Starting off this comparison will be PowerShell using dbatools. This in my opinion is a very robust and scalable option. There are multiple configuration settings you can choose from depending on how customized your need.

$options = New-DbaScriptingOption
$options.driAllConstraints = $true
$transfer = New-DbaDbTransfer -SqlInstance "localhostsql2017" -DestinationSqlInstance "localhostsql2017" `
    -Database DBA -DestinationDatabase DBA_SchemaOnly `
    -SchemaOnly -CopyAll Tables, Schemas, StoredProcedures -ScriptingOption $options
$transfer.DropDestinationObjectsFirst = $true
$transfer | Invoke-DbaDbTransfer -Verbose

In this example, the New-DbaScriptingOption, New-DbaDbTransfer, and Invoke-DbaDbTransfer commands were used. Below are details about the parameters being utilized.

Script Explanation

• New-DbaScriptingOption
  • driAllConstraints = Enables the copy of all database-level constraints
• New-DbaDbTransfer
  • SqlInstance – Source server name for source database
  • DestinationSqlInstance – Destination server name for the location of the copied database schema
  • Database = Source database name
  • DestinationDatabase = Destination database name (does not have to match)(database must exist)
  • SchemaOnly = Parameter that instructs the command to only copy schema objects
  • CopyAll = Parameter that instructs the command to copy all Tables, Stored Procedures, Schemas (see the documentation for a full list of options)
  • ScriptingOption = This instructs the command to use the custom scripting option set earlier in the script
• DropDestinationObjectsFirst = This signifies that I want to drop the objects on the destination database before deploying the new copy
• Invoke-DbaDbTransfer = This command does that actual schema copy based on the parameters input from the first three commands
  • Verbose – Displays the full output of steps the command is processing to the screen

From the execution of the script, you can see that the DBA database was copied to DBA_SchemaOnly in just 3.28 seconds.

Now DBA_SchemaOnly has an exact copy of all objects from DBA where it can be used as necessary.

DBCC CloneDatabase

It is time to take a look at the built-in SQL Server command DBCC CLONEDATABASE. This feature was released in 2016 for certain SQL Versions back to 2012. It will depend on the patch level of the server if this feature is available to you.

The functionality of this command allows you to get an exact schema only copy of the source database for troubleshooting performance issues or simply doing development on a schema copy instead of a full database restored copy.

DBCC CLONEDATABASE ('DBA', 'DBA_SchemaOnly_DBCC')

Script Explanation

• CLONEDATABASE
  • DBA = Source database name
  • DBA_SchemaOnly_DBCC = Destination database name

Notice that after creating the clone you are given a warning about not using this for any production environment.

Here we see our cloned database in a Read-Only mode. This is by design so that you can perform read activity against the database. However, you can run an

ALTER command to bring the database into Read/Write mode if you need to for testing.

Red-Gate SQL Compare

There is a very popular toolset in the SQL industry that was created by Red-Gate. One of the tools you see mentioned most often is SQL Compare. This utility will compare schema objects from a source location like a backup file, online database, script folder, snapshot, or source control. These make the tool very robust and versatile. We will cover how to compare two different databases.

1. 1. Input your source server name
   2. Click the drop-down and select your source database name
   3. Input your destination server name
   4. Click Create to be prompted for a new database name for the destination
   5. Enter the destination database name
   6. Click Create Now to have the database created on the destination server
   7. Choose Compare Now to start the compare process

The output will show you if there are objects in the source and not the destination, destination and not source or, the identical objects. We are concerned with objects that exist in the source and not in the destination.

1. 1. Click the Object name Checkbox to select all the Tables and Stored Procedures listed
   2. Choose Deploy
   3. Follow the instructions to either build a deployment script or allow SQL Compare to perform the deployment

When the deployment process is complete, SQL Compare will do another comparison and you should see all objects are identical on the source and destination.

Conclusion

While there are many other ways to create schema only database copies, this list is one that has a few most popular. Your mileage may vary depending on the tools and other environmental variables for your situation.

If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list.  I hope to start delivering content via the mailing list soon.  

The post How To: Create Schema Only Database Copies appeared first on GarryBargsley.com.