SQL Queries - Common SQL Queries - How to check if a database has been restored on Oracle or SQL Server after being initially created

Created by John McMahon on Feb 11, 2022
Purpose of Query

This page provides queries to check if a database has been restored on Oracle or SQL Server after being initially created. This cannot be done on CSS as objects' creation date is not stored in the system catalog.

Applicable CAST Version
Release
Yes/No
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
7.3.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(tick)
Microsoft SQL Server(tick)
CSS2(error)

 

 

Query for Oracle

Execute this query as the system user substituting the schema name that you want to check in the query below:

SELECT uo.created schema_creation_date,
       (SELECT MIN(revision_date)
       FROM    <schema_name>.sys_package_history
       )
       original_schema_date
FROM   ALL_OBJECTS uo
WHERE  uo.OBJECT_NAME = 'SYS_PACKAGE_HISTORY'
AND    OWNER          = '<schema_name>'
Query result example
 
SCHEMA_CREATION_DATE    ORIGINAL_SCHEMA_DATE
[DATETIME             ] [DATETIME             ]
----------------------- -----------------------
02/16/2012 14:59:41:000 11/04/2011 12:13:00:000
Query result interpretation

If the creation date is newer, then the schema has been restored.

In the example data above, the schema has been restored.

Query for SQL server

Connect to the schema and run the query below:

SELECT create_date AS schema_creation_date,
       (SELECT MIN(REVISION_DATE)
       FROM    SYS_PACKAGE_HISTORY
       )
       original_schema_date
FROM   sys.all_objects
WHERE  name = 'SYS_PACKAGE_HISTORY'
Query result example
 
schema_creation_date     original_schema_date
-----------------------  -----------------------
06/02/2012 15:24:53      06/02/2012 15:24:54
Query result interpretation

If the creation date is newer, then the schema has been restored.

In the example data above, the schema has not been restored.

Notes/comments
 

 

 

Related Pages