Fix Database View Definition Lost After Restore (3 Steps)

Why Your View Definitions Disappear After a Restore

I've seen this a hundred times. You restore a database backup – maybe to a test server, maybe after a crash – and suddenly all your views are busted. They show up in the object explorer but the definition is empty, or SQL Server throws an error when you try to select from them. The most common trigger? You restored a backup from a newer version of SQL Server (like 2019) onto an older one (like 2016), or the restore missed some dependent objects. Don't panic. The fix is usually simple.

Fix 1 (30 seconds): Refresh the View Definitions

This sounds dumb, but it works more often than you'd think. SQL Server caches view definitions. After a restore, that cache can get stale. Here's what to do:

In SSMS, right-click the database with the broken views.
Select Tasks > Generate Scripts.
Choose Select specific database objects and pick the views.
Set the output to New query window.
Before running the script, run this command:
```
EXEC sp_refreshview @viewname = N'YourViewName';
```
Replace YourViewName with the actual view name.
Run the sp_refreshview for each broken view. If it succeeds, your view is back.

When this works: If the underlying tables exist and are unchanged, a refresh is all you need. I had a client who restored a copy of their production database to a dev server and every view was blank. Five seconds of refreshing fixed the entire thing.

Fix 2 (5 minutes): Check and Recreate Dependencies

If the refresh fails with an error like "Invalid object name" or "View or function is not updatable," the problem is deeper. The view might depend on another view, a table, or a function that was lost during restore. Common culprits:

Tables renamed or dropped before the backup was taken.
User-defined functions that are in a different database.
Cross-database references – these break if the other database isn't restored too.

Here's how to find the broken dependency:

Run this query against your database:

SELECT DISTINCT 
    OBJECT_NAME(d.referencing_id) AS ViewName,
    d.referenced_entity_name AS MissingObject
FROM sys.sql_expression_dependencies d
WHERE d.referencing_id IN (SELECT object_id FROM sys.views)
  AND d.is_ambiguous = 0
  AND d.referenced_id IS NULL;

This lists views that reference objects that don't exist.

For each missing object, recreate it. If it's a table, restore it from a backup or create a placeholder. If it's a function, script it out from the source database.
After recreating all missing objects, run sp_refreshview again for each view.

Real-world example: I worked on a SQL Server 2022 restore where a view used a scalar function from a utility database. The utility database wasn't restored. After recreating the function, the view worked fine.

Fix 3 (15+ minutes): Rebuild the View from Script

If the first two fixes don't work, you're dealing with a corrupted or incomplete view definition. This happens when the backup was taken while a view was being altered, or the restore process had a hiccup. Here's the nuclear option:

Open a new query window in SSMS connected to the restored database.
Run this to get the view's creation script:
```
SELECT definition 
FROM sys.sql_modules 
WHERE object_id = OBJECT_ID(N'YourViewName');
```
If this returns NULL, the definition is truly lost. If it returns something, copy it.
If the definition is empty, you need the original script. Check:
- Your source control system (Git, TFS, etc.).
- The source database (if still accessible) – right-click view > Script View as > CREATE To.
- Your backup logs – sometimes the definition is in the error log if the restore failed partially.
Once you have the script, drop the broken view:
```
DROP VIEW YourViewName;
```
Run the CREATE VIEW script.
Test the view with SELECT * FROM YourViewName.

Pro tip: If you have multiple broken views, script them all out before dropping any. You can use this query to get all view definitions at once:

SELECT 
    OBJECT_NAME(object_id) AS ViewName,
    definition
FROM sys.sql_modules
WHERE object_id IN (SELECT object_id FROM sys.views);

Save the results to a text file. Then you can drop and recreate each view safely.

When to Give Up and Restore Again

If you've tried all three fixes and still have broken views, the backup itself might be corrupt. Try restoring from a different backup file or a different point in time. Also check if the backup was taken with CHECKSUM enabled – if not, corruption is more likely. In SQL Server 2019 and later, you can use RESTORE VERIFYONLY FROM DISK = 'YourBackupFile.bak' to check the backup integrity.

Prevent This Next Time

Once your views are fixed, take these steps to avoid repeating the headache:

Store all view definitions in source control (Git works great).

Back up databases with CHECKSUM enabled:

BACKUP DATABASE YourDB TO DISK = 'YourDB.bak' WITH CHECKSUM;

Before restoring to a different server version, use DBCC CHECKDB to verify the source database health.
Test restores regularly – I do it monthly on a dev server.

That's it. You've got your views back. If something's still broken, drop me a comment below – I check them every morning over coffee.