Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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).

share|improve this answer
add comment

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.

share|improve this answer
    
I cannot add the view creation to the test scripts because the views are invalid. I suppose a possible approach is to identify all the broken views, add sufficient tables / views to allow them to be loaded, and then drop the additional tables and load the rest of the proper schema. That would take a few days though. –  Martin Dec 7 '10 at 7:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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