2

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?

4
  • First thought is that I personally would want to use a backup/restore strategy rather than recreate schema - there can be many differences in the configuration that could affect the tests (e.g. users, security, recovery mode, engine version) Aside from that concern; something is going to have to give - your last sentence is basically saying you cant restore and you cant recreate - so you're kinda in trouble unless you wanted to just set up a linked server & alias to the source server and run your UAT through a layer of indirection...
    – chrisb
    Feb 21, 2013 at 22:01
  • Ok, can you explain your last sentence please? I know about linked servers, we're using them a lot for a project which kinda evolved into this whole import requirement, I just don't know what you mean about running UAT through a layer of indirection.
    – ClarkeyBoy
    Feb 21, 2013 at 22:03
  • Use Red-Gate SQL Compare for the schema, Red-Gate SQL Data Compare for the data. Both can be scripted. This is a case where it will be cheaper (time is money) to throw money at the problem and make it go away. Feb 21, 2013 at 22:05
  • @StrayCatDBA, thanks for the answer, we'll certainly consider this if it looks suitable. I will be sure to bring this up when we next work on this or have a meeting about it.
    – ClarkeyBoy
    Feb 21, 2013 at 22:42

4 Answers 4

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.

3
  • This sounds like it could be the way forward so long as it doesn't look at dependencies at all. My main questions about this (which I will research at a later date but figured I'd put them out there in case you know the answers) are whether we need SSMS installed first (we have it on our machines but I'm not sure about the servers themselves), whether we can call the transfer programmatically (when a user clicks a link on a web page) and whether it would need to be on the servers which do the push or whether it could just be on the UAT servers and they pull what they need from the live server?
    – ClarkeyBoy
    Feb 22, 2013 at 7:15
  • If you are using an SSIS package then you will have to have Integration Services installed on whichever server you want to run the packages on (but it could easily be just one central server). At that point you can write a simple "dos" command that you can run from any command shell. Or you can create a SQL job to run the SSIS package and execute it using a simple stored procedure, sp_startjob I believe. Feb 22, 2013 at 12:26
  • DTExecUI is a user interface that will help you generate your DTEXEC command to run the SSIS package. As far as replication goes I believe you can kick off replication pretty easily too using some system stored procedures but I don't know a whole lot about it. Feb 22, 2013 at 12:28
1

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.

3
  • Would this not evaluate dependencies still? What I'm looking for is a way to copy the schema without any data and without evaluating dependencies.
    – ClarkeyBoy
    Feb 23, 2013 at 11:01
  • By dependencies, I believe you mean constraints and indexes? Simply scripting out the table will only script out the CREATE statement with no constraints or indexes. You will need to script those out separately.
    – Mike Fal
    Feb 25, 2013 at 17:12
  • By dependencies I mean if a sproc requires a view then it depends on the existance of this view. When you create this sproc, it falls over if the view does not exist. I need it to create the sproc without looking at whether the view exists (not ideal, but it's the best way to exactly replicate a clients setup). In fact, more ideally, it should throw an error (so we can catch and log issues like this for fixing in the future) and still go ahead and create the sproc.
    – ClarkeyBoy
    Feb 26, 2013 at 11:01
0

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.