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
Furthermore some sprocs were originally created with dependencies on views (for example) but the views have been deleted.
We've tried the following approaches:
- Backup / restore entire database, delete unwanted data
- UAT server doesn't have enough room for some databases, enlarging is not an option
- Backup / restore small database, truncate all tables and insert into from other server
- Not suitable as schema varies from one server to the next
- 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?