sql server: how to load broken views

Question

I have a legacy database server with a lot of views installed without schema-binding. Over time, these views have fallen into disrepair and some refer to objects that no longer exist, or to columns whose names have changed.

In order to test an upgrade script, I would like to install the schema objects onto an empty test database. However, many of the views cannot be loaded because they are in a state of disrepair.

Is it possible to get SQL Server to load views that refer to non-existent objects in order that I can test my repair scripts ?

I can do this using backup and restore, but I do not want to load the data-set which is large and takes hours to load.

Answer 1

You could backup and resotre the database to a new name once, delete the data in all the tables, force a re-index and full database shrink, then backup the result for restoration at the start of each of your test runs.

This would give you a small data-free database with the same structure including the broken views and procedures. You still have to do the backup and restore of the whole lot, but only once as subsequent restores will be from the wiped and shrunk copy. The delete operation will take a while too but again will only need to be done once (the reindex and shrink should be very quick).

Answer 2

I don't think you'll be able to that. Why not add the view creation to the test scripts? If the entire script runs successfully, then your schema change resolves the problem.

I think earlier versions of SQL Server allowed direct modification of the syscomments table that possibly would've worked, but not any more.