SQL Server - backup / restore schema only

Question

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - maybe from where someone has backed up data into another column, updated the original column and not deleted the backup column, for example.

There are also varying column sizes (i.e. column a on table b on one server may be nvarchar(7500) but same column on same table on another server may be nvarchar(max)).

Furthermore some sprocs were originally created with dependencies on views (for example) but the views have been deleted.

We've tried the following approaches:

1. Backup / restore entire database, delete unwanted data
   • UAT server doesn't have enough room for some databases, enlarging is not an option
2. Backup / restore small database, truncate all tables and insert into from other server
   • Not suitable as schema varies from one server to the next
3. Loop through tables, select * into <tablename> from [ip].[db].[schema].[tablename] where ...
   • Creates table structures fine including data types, but primary keys, triggers, sprocs etc. not created - we've tried scripting them from various sys tables but due to some views being removed some sprocs fail (same with triggers if they use removed views)

We basically just need to copy across the data for one client only onto a UAT server, run some tests against that and then move onto the next client. Forget things outside the database, these are not part of my task; my task is simply to replicate the structure of a DB from one server to another including sprocs, triggers and primary keys. We will potentially have up to 36 DBs on each of 2 UAT servers, hence the space restriction (6 per developer allowing up to 6 clients on the server per developer). We may be able to increase the number of servers and make it, say, 12 DBs per server, but to encourage those who can get these servers we will have to have a working proof of concept first, if you get what I mean.

Method 3 above gets closest but the fact that sprocs may not even exist is not satisfactory - if any program detects the cause of the error, it may act differently depending on whether the sproc is missing or failed during execution. There might be some code which executes before failure, in which case this would be executed each time the sproc is run on live but not at all on UAT.

Lastly I'm running SSMS 2005 and I believe servers vary between SQL Server 2005 and 2008.

Does anyone have any ideas on how to achieve this? Is it even possible, bearing in mind we physically cannot do a full backup / restore and cannot script all sprocs due to non-existence of views?

Answer 1

I haven't used it before but there is an object in SSIS called Transfer SQL Server Objects. It has an option to copy data or not and you can select copy all objects or just specific types of objects, permissions etc.

You could also look into doing replication but blocking the data portion of it. In fact if you set up a snapshot replication that just pushed schema for each of your servers you could potentially have an automated process to do the push.

I think the SSIS option is probably your best bet though.

Answer 2

You can use Powershell to script out database objects as a .sql script that can be used both as a schema backup and for the transfer you're talking about. This is done by using the .Script() method in the SQL Server SMO. There's a full blog post on it by Edwin Sarmiento. For example, here's how you can script out your CREATE DATABASE with options(extracted from the linked blog post):

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1"  

$dbs=$s.Databases 

$dbs["Northwind"].Script() 

The blog post should provide you full details and code that you can use to support your schema copies.

Answer 3

As of SQL Server 2012 (patched), you can make a schema only clone of your database using DBCC CLONEDATABASE. Then simply backup the clone.

dbcc clonedatabase(Demo, Demo_Clone) with verify_clonedb;
alter database [Demo_Clone] set read_write;
backup database [Demo_Clone] to disk = N'C:\temp\Demo_SchemaOnly_20220821.bak';
drop database [Demo_Clone];

Read more here: Schema Only Database Backup

Answer 4

Have a look at Redgate SQL Compare. It will do this for you in seconds.

And the great thing is that it can report differences and deltas