How to Refresh and Update Metadata for a Database in SQL Server

The term “database refresh” typically means the process of resetting or renewing a database to a required state. Let’s review some cases where you might need to refresh a database:

• Data update: Sometimes, it’s necessary just to update data in a database to maintain consistent information.
• Data rollback: If there are some errors, there is a need to revert a database to a previous state. This helps in identifying bottlenecks faster and facilitates their resolution.
• Testing: In test and development environments, a database is usually reset to a particular state before running tests or developing new features. This approach guarantees that each test stage starts with a stable and well-defined database state.
• Disaster recovery: Critical situations may require an emergency response, including database restoration from backups.
• Performance optimization: Database refresh can be a part of the process to improve database performance.

Contents

• The issue with outdated metadata
• Refresh metadata for other objects
• Common methods for the database refresh
• Use manual restore for the database refresh
• Perform the database and metadata refresh
• Troubleshooting and verification
• Conclusion

The issue with outdated metadata

Metadata for non-schema-bound views can become outdated for several reasons. Generally, it happens due to changes in a database. Here is the explanation of possible scenarios:

1. If the columns of tables and other views referenced by a non-schema-bound view are renamed, dropped, added, and so on, the metadata of the view becomes irrelevant.
2. If you alter the tables or views referenced by the non-schema-bound view, the view’s metadata becomes outdated.
3. Even if the structure of the underlying objects remains the same, changes to the data within those objects can lead to outdated metadata.
4. If the non-schema-bound view depends on stored procedures, functions, or other database objects that are modified or removed, the view’s metadata becomes outdated.
5. Alterations to security settings or permissions on underlying objects can also impact non-schema-bound views. If the view relies on access to objects that it no longer has permission to access, it becomes outdated.
6. If objects referenced by the view are renamed, the view’s metadata may become outdated because it still references the old object names.

As you can see, there are many factors that can affect non-schema-bound views but sp_refreshview will help you forget about this issue. It updates the metadata of a view to reflect the changes in the underlying tables or columns referenced by the view. The query with sp_refreshview looks as follows:

EXEC sp_refreshview ;

For example, let’s refresh the metadata for the view named vIndividualCustomer in the Sales schema.

USE AdventureWorks2022; 
GO
EXECUTE sp_refreshview N'Sales.vIndividualCustomer';
GO 

To be brief, you can use this procedure to maintain data consistency, resolve errors, and improve performance in database systems.

Refresh metadata for other objects

If you apply changes to the database schema, dependencies, or the objects themselves, be ready to have obsolete metadata for various database objects, such as stored procedures, user-defined functions, views, DML triggers, and database-level DDL triggers. Let’s figure out why metadata for these objects can become outdated.

Schema changes 

If columns referenced by any of these objects are modified (e.g., data type changes, column renames, or dropping columns), it can lead to outdated metadata. The objects rely on the structure of the referenced columns, and any mismatches can cause errors or unexpected behavior.

Object renaming 

If any of the referenced objects are renamed, the metadata of dependent objects becomes outdated because they still reference the old object names.

Security changes 

If you alter something in the database security policy, then stored procedures, user-defined functions, views, DML triggers, and database-level DDL triggers do not function fully, as an object does not have access to the required resources.

Code modifications

If you change the code of these objects (stored procedures, user-defined functions, views, DML triggers, and database-level DDL triggers), the updated code and the stored metadata become different.

Dependency changes

Any modifications to dependent objects impact the metadata of the referencing objects, making it irrelevant.

So, how to update the metadata for other objects and avoid further challenges? You can use sp_refreshsqlmodule. It’s a system-stored procedure for refreshing the metadata of a specific module in a database. Here is the syntax of sp_refreshsqlmodule:

EXEC sp_refreshsqlmodule schema_name.object_name

Where:

• schema_name is the name of the schema where the required module is located
• object_name is the name of the module (e.g., stored procedure, function, or view) you want to refresh the metadata for

Let’s refresh the metadata for the ufnGetContactInformation function in the AdventureWorks2022 database with the procedure:

USE AdventureWorks2022; 
GO
 
EXEC sys.sp_refreshsqlmodule 'dbo.ufnGetContactInformation';
GO

Common methods for the database refresh

There are various approaches for performing the database refresh, but we’ll review the most common ones with their pros and cons.

1. Manual restore

It’s a basic method and implies creating a backup of a database in its desired state and then restoring the backup when you need to refresh the database.

Pros:

• Control: Manual backup and restore operations allow you to manage all the stages of the refresh process.
• Point-in-time recovery: You can restore a database to a specific point in time, which is useful for data recovery and rollbacks.

Cons:

• Time costs: Manual procedures can take much time, especially when you back up large databases.
• Complexity: You should know how to perform the backup and restore processes.
• Errors: Manual operations do not exclude the risk of human errors.

2. SQL scripts

This method uses SQL statements to update, alter, or recreate the database to bring it to a desired state.

Pros:

• Customization: You can adjust SQL scripts to your needs.
• Automation: To avoid manual routine, the scripts can be automated.
• Version control: Managing SQL scripts in version control systems like Git ensures that the changes are tracked and can be rolled back if required.

Cons:

• Development: To create and maintain your custom scripts, you need to spend enough time and effort.
• Maintenance: You may need to update the scripts according to changes in the database structure.
• Error handling: SQL scripts must contain the error handling behavior.

3. Containerization

Containerization platforms like Docker can be used to create database environments that you can easily refresh.

Pros:

• Scalability: You can scale up or down containers when you need.
• Automation: Manual operations can be replaced with DevOps pipelines.
• Consistency: Containers provide consistent and reliable environments.

Cons:

• Expertise: DevOps practices require specific knowledge and skills.
• Resource consumption: To maintain containers, you need to have enough resources.
• Not suitable for all databases: This approach may not be suitable for all types of databases or legacy systems.

4. Third-party tools

Third-party tools deliver a robust and effective way to refresh databases. These tools are designed to enhance and automate the process of copying, updating, and synchronizing databases.

Pros:

• Ease of use: In most cases, the tools have user-friendly interfaces.
• Automation: Such instruments offer automation capabilities.
• Support: Commercial tools come with support and documentation.

Cons:

• Cost: Many tools require licensing fees.
• Incompatibility: Some tools may be inappropriate for your environment.

Use manual restore for the database refresh

In this section, we’re going to perform the database refresh with the help of SSMS. This process includes two stages: create a backup of the desired database and restore it.

Take a backup

1. In Object Explorer, right-click the database you want to back up and navigate to Tasks > Back Up.

2. Under Destination, confirm the path to the backup file.

If you want to change the path, click Remove, and then Add.

To select the necessary path, click the button shown in the screenshot.

Set the path and click OK.

3. Confirm the backup destination by clicking OK.

4. To back up the database, click OK.

Restore the database

1. In Object Explorer, right-click Databases and click Restore Database.

2. Select Device and click three dots to place the backup file.

3. Click Add.

Then select the .bak file and click OK.

4. Confirm the backup destination by clicking OK.

5. Select the database where you want to restore the backup from Database.

6. Finally, click OK.

As you can see, this process has taken several minutes and we do not need to run any queries for it.

Perform the database and metadata refresh

Now, let’s shift our focus from theory to the practical aspect. We’ll provide examples of the scripts for refreshing the AdventureWorks2022 database and metadata in it and run both of them in dbForge Studio for SQL Server.

Here is an example of the script that you can use to refresh metadata. The script updates metadata information for all VIEWS, DML TRIGGERS, PROCEDURES, FUNCTIONS, and DDL TRIGGERS.

USE AdventureWorks2022;  
GO  
 
-- Refresh all VIEWS
  
PRINT ' -- Refreshing all VIEWS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshview '''
+QUOTENAME(ss.name)+'.'
+QUOTENAME(so.name)+'''' as stmt_refresh_views
FROM sys.objects AS so
    INNER JOIN sys.sql_expression_dependencies AS sed
        ON so.object_id = sed.referencing_id
    INNER JOIN sys.schemas AS ss
        ON so.schema_id = ss.schema_id
WHERE so.type = 'V' AND sed.is_schema_bound_reference = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
    BEGIN
        print @stmt_refresh_object
        exec sp_executesql @stmt_refresh_object
       FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
    END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
 
-- Refresh all DML TRIGGERS

PRINT ' -- Refreshing all DML TRIGGERS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(schemas.name)+'.'
+QUOTENAME(triggers.name)+''''
as stmt_refresh_dml_triggers
FROM sys.triggers AS triggers WITH(NOLOCK)
    INNER JOIN sys.objects AS objects WITH(NOLOCK)
        ON objects.object_id = triggers.parent_id
    INNER JOIN sys.schemas AS schemas WITH(NOLOCK)
        ON schemas.schema_id = objects.schema_id
    LEFT JOIN sys.sql_modules AS sql_modules WITH(NOLOCK)
        ON sql_modules.object_id = triggers.object_id
    LEFT JOIN sys.assembly_modules AS assembly_modules WITH(NOLOCK)
        ON assembly_modules.object_id = triggers.object_id
    LEFT JOIN sys.assemblies AS assemblies WITH(NOLOCK)
        ON assemblies.assembly_id = assembly_modules.assembly_id
    LEFT JOIN sys.database_principals AS principals WITH(NOLOCK)
        ON principals.principal_id = assembly_modules.execute_as_principal_id
            OR principals.principal_id = sql_modules.execute_as_principal_id
WHERE RTRIM(objects.type) IN ('U','V') and parent_class = 1
    AND sql_modules.is_schema_bound = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
    BEGIN
        print @stmt_refresh_object
        exec sp_executesql @stmt_refresh_object
       FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
    END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
 
-- Refresh all PROCEDURES

PRINT ' -- Refreshing all PROCEDURES in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(s.name)+'.'
+QUOTENAME(p.name)+''''
as stmt_refresh_procedures
FROM sys.procedures AS p WITH(NOLOCK)
    LEFT JOIN sys.schemas AS s WITH(NOLOCK)
        ON p.schema_id = s.schema_id
    LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK)
        ON p.object_id = sm.object_id
    LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK)
        ON p.object_id = am.object_id
    LEFT JOIN sys.assemblies AS a
        ON a.assembly_id = am.assembly_id
    LEFT JOIN sys.objects AS o WITH(NOLOCK)
        ON sm.object_id = o.object_id
    LEFT JOIN sys.database_principals AS dp WITH(NOLOCK)
        ON sm.execute_as_principal_id = dp.principal_id
         OR am.execute_as_principal_id = dp.principal_id
    LEFT JOIN sys.database_principals AS dp1 WITH(NOLOCK)
    ON o.principal_id = dp1.principal_id
WHERE (CAST(CASE WHEN p.is_ms_shipped = 1 then 1
    WHEN (SELECT major_id FROM sys.extended_properties WHERE
major_id = p.object_id AND minor_id = 0 AND class = 1 AND
name = 'microsoft_database_tools_support') IS NOT NULL THEN 1
ELSE 0 END AS bit)=0)
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
BEGIN
    print @stmt_refresh_object
    exec sp_executesql @stmt_refresh_object
    FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO

-- Refresh all FUNCTIONS

PRINT ' -- Refreshing all FUNCTIONS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'
+QUOTENAME(o.name)+''''
as stmt_refresh_functions
FROM sys.objects AS o WITH(NOLOCK)
    LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK)
        ON o.object_id = sm.object_id
    LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK)
        ON o.object_id = am.object_id
    LEFT JOIN sys.database_principals p1 WITH(NOLOCK)
        ON p1.principal_id = o.principal_id
    LEFT JOIN sys.database_principals p2 WITH(NOLOCK)
        ON p2.principal_id=am.execute_as_principal_id
    LEFT JOIN sys.database_principals p3 WITH(NOLOCK)
        ON p3.principal_id=sm.execute_as_principal_id
    LEFT JOIN sys.assemblies AS ass WITH(NOLOCK)
        ON ass.assembly_id = am.assembly_id
WHERE o.type IN ('FN','IF','TF','AF','FS','FT') and sm.is_schema_bound = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
    BEGIN
        print @stmt_refresh_object
        exec sp_executesql @stmt_refresh_object
       FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
    END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
 
-- Refresh all DDL TRIGGERS

PRINT ' -- Refreshing all DDL TRIGGERS on database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400)
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''
+QUOTENAME(t.name)+''','
+'''DATABASE_DDL_TRIGGER''' as stmt_refresh_ddl_triggers
FROM sys.triggers AS t WITH(NOLOCK)
    LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK)
        ON t.object_id = sm.object_id
    LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK)
        ON t.object_id = am.object_id
    LEFT JOIN sys.assemblies AS assemblies WITH(NOLOCK)
        ON assemblies.assembly_id = am.assembly_id
    LEFT JOIN sys.database_principals AS principals WITH(NOLOCK)
        ON principals.principal_id = sm.execute_as_principal_id
        OR principals.principal_id = am.execute_as_principal_id
WHERE parent_class = 0
OPEN c_refresh_object
FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
WHILE @@FETCH_STATUS = 0
    BEGIN
        print @stmt_refresh_object
        exec sp_executesql @stmt_refresh_object
       FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object
    END
CLOSE c_refresh_object
DEALLOCATE c_refresh_object
GO
PRINT 'Metadata update for non-schema-bound objects is done.'
GO
USE [master]
GO

For convenience, we’ve commented on what each script block does. 

In each block, the script retrieves a list of views/DML triggers/procedures/functions/DDL triggers in the AdventureWorks2022 database, generates, and runs SQL statements to refresh all these objects.

To refresh the database, the following sript can be executed, for example:

USE master
GO
BACKUP DATABASE AdventureWorks2022
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\AdventureWorks2022_2023_08_24_14_19.bak'
WITH NAME = N'AdventureWorks2022-Full Database backup', NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 1
GO
 
RESTORE DATABASE AdventureWorks2022
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\AdventureWorks2022_2023_08_24_14_19.bak'
WITH FILE = 1, STATS = 1;
GO

The script backs up the AdventureWorks2022 database and puts the .bak file to C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS02\MSSQL\Backup\AdventureWorks2022.bak, and then restores the database from the backup file.

Troubleshooting and verification

Of course, not all refresh processes may go smoothly and you can face some challenges. 

These are the common issues during the database refresh and possible ways for troubleshooting them: 

Data integrity problems

Issues with data corruption can be a result of incomplete data transfer. 

Troubleshooting: 

• Before refreshing, check the source data for any issues
• Implement data validation checks before and after the refresh process
• Use the database backup and recovery tools to restore a database to a known state

Poor performance 

Database productivity can decrease after the refresh because of indexing, statistics, or query plan issues. 

Troubleshooting:

• Check and optimize query execution plans 
• Rebuild indexes 
• Track the database performance

Issues with security and permissions 

It’s not a secret that incorrect permissions can also affect the refresh process and lead to failures. 

Troubleshooting: 

• Keep updated security settings for databases and servers
• Before refreshing a database, ensure that the account has all the required permissions for it

Resource limitations 

Insufficient server resources (CPU, memory, etc.) can slow down or totally terminate the refresh process. 

Troubleshooting: 

• Monitor system resource usage
• Upgrade hardware if required 

As for the metadata refresh,  the following issues may occur: 

Lack of dependencies 

If you change objects that are linked with metadata,  there can be some failures. 

Troubleshooting: 

• Keep an eye on metadata dependencies and update them 
• Implement automated testing of metadata to detect issues in the early stages

Outdated metadata

Irrelevant metadata can lead to inaccurate results. 

Troubleshooting: 

• Regularly check and update metadata definitions 
• Implement versioning to track changes 

Concurrency issues 

When several users try to edit the metadata at the same time, the result can be unexpected such as conflicts or errors. 

Troubleshooting: 

• Use version control and branching for modifying metadata 
• Implement the concurrency control 

Poor performance 

Slow metadata refresh processes can leave a negative experience for users. 

Troubleshooting: 

• Enhance metadata queries and scripts 
• Utilize the monitoring and logging tools
• Apply cache systems to improve the performance 

The following recommendations can help you ensure that the database refresh is successful. 

Check logs  

This is the first thing that you should inspect after the database refresh. In logs, you can find errors and warnings, and analyze the whole process. 

Validate data  

To verify that the data is intact, just compare it from the refreshed database with the source data. 

Test 

Launch testing of the applications that use data from the refreshed database. Also, run benchmark tests and monitor response times to check the performance of the database. 

Verify data consistency 

Execute SQL queries and scripts to confirm that data remains reliable. 

Test the backup and restore procedures 

Ensure that you can recover the previous state of the database. We have added this step to the verification practice but in fact, both procedures must be tested before performing the refresh process.

Conclusion

To maintain data integrity and accuracy, it’s required to regularly refresh databases. This process is essential for optimal performance, security, and compliance. We’ve shown how to perform it in two different ways and with the help of two powerful tools, SSMS and dbForge Studio for SQL Server.

Here, we would like to focus a bit on dbForge Studio. Thanks to an advanced code editor in dbForge Studio for SQL Server, it’s possible to write SQL code of any complexity. The editor offers various features such as syntax highlighting, code completion, and error checking. In addition, dbForge Studio includes a library of code snippets that can help speed up the coding process by providing pre-written and commonly used SQL code segments. Download the tool for a free 30-day trial and try out all the functionality!