Sql-server – SQL Server – backup / restore schema only

schemascriptingsql serversql-server-2005

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:

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?

Best Answer

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.

Related Solutions

Sql-server – Restore all applicable files in a backup device in T-SQL

This procedure should do what you want:


create procedure dbo.dbrestore
@dbname nvarchar(50)
as
declare @fileno integer
declare @dumpdevice nvarchar(50)
declare @mediasetid integer
declare @position integer

select top(1) @dumpdevice=a.logical_device_name, @mediasetid=b.media_set_id, @position=b.position from backupmediafamily a inner join backupset b on a.media_set_id=b.media_set_id
where b.database_name='backuptest' and b.type='D' order by a.media_set_id desc, b. position desc

declare restore_cursor cursor for
select position from msdb.dbo.backupset where database_name=@dbname and type='D' and media_set_id=@mediasetid and position=@position
union
select  max(position) as position from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position
union
select position from msdb.dbo.backupset where database_name=@dbname and type='L' and media_set_id=@mediasetid and position>@position
and last_lsn>(select max(last_lsn)from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position)
order by position asc;

open restore_cursor
fetch next from restore_cursor into @fileno
while @@FETCH_STATUS = 0
begin
--comment the print statement and uncomment the exec statement to run
--exec('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+@fileno+';')
  print ('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+convert(varchar(4),@fileno)+';')
fetch next from restore_cursor into @fileno
end
close restore_cursor
deallocate restore_cursor
--exec('restore database ['+@dbname+'] with recovery;')
go

execution goes:

get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device

Then build up a union query consisting of:

The last full database backup (type=D) in the device

the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.

the transaction logs (type=L) taken after the above diff.

returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)

Loop through the result restoring them individually, and then a final restore with recovery to finish it off.

Call it as:


exec dbrestore 'db2restore'

I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.

Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.

Sql-server – Restore databases from prod to test server with data over X days

As already suggested, you cannot achieve this using backup/restore. You can also not achieve it using log shipping.

I would look at using transactional replication perhaps from views, which would define the 60 to 90 day window.

Be aware (or make your stakeholders aware) that replication is not free and comes with its own (usually small) performance overhead.

Best Answer

Related Solutions

Sql-server – Restore all applicable files in a backup device in T-SQL

Sql-server – Restore databases from prod to test server with data over X days

Related Question