Update Statistics
This step is not necessary, but if we are going to all the effort to transfer statistics, we may as well transfer accurate information. The simplest method of updating statistics is executing the following statement.
exec sp_updatestats
The above statement runs UPDATE STATISTICS against all user-defined tables in the current database. The new statistics will inherit the sampling ratio from the old statistics.
exec sp_updatestats
It may be desirable to change the sampling ration to full scan, in which case, run the following statement to generate a script for updating statistics on each table with a full scan. Note that on SQL Server Enterprise Edition, a view may have indexes as well. It is also assumed that one does not want to transfer statistics for tables created by SQL Server during installation.
SELECT ‘UPDATE STATISTICS ‘ + o.name + ‘ WITH FULLSCAN’
FROM sysobjects o
WHERE ( OBJECTPROPERTY(o.id, N’IsUserTable’) = 1
OR OBJECTPROPERTY(o.id, N’IsUserView’) = 1 )
AND OBJECTPROPERTY(o.id, N’IsMSShipped’) = 0
ORDER BY o.name
Also run DBCC UPDATEUSAGE to correct the rows, used, reserved and dpages columns of the sysindexes table.
Create New Database and Disable Automatic Statistics
The script below is an example for creating a new database.
CREATE DATABASE [sut] ON PRIMARY (NAME = N’sut_data’,
FILENAME = N’C:MSSQLDatasut_data.mdf’ , SIZE = 32)
LOG ON (NAME = N’sut_log’,
FILENAME = N’C:MSSQLDatasut_log.ldf’ , SIZE = 16)
COLLATE SQL_Latin1_General_CP1_CI_AS
After creating the new database, disable automatic statistics management as follows.
ALTER DATABASE [sut] SET AUTO_CREATE_STATISTICS OFF
GO
ALTER DATABASE [sut] SET AUTO_UPDATE_STATISTICS OFF
GO
Create Users, Data Types, Tables, Except for Nonclustered Indexes
Create required users (that own objects in the original database), data types, tables, clustered indexes, constraints and other objects except nonclustered indexes. It may be easier to run generate scripts from Enterprise Manager twice, once with indexes but not keys, defaults and constraints, and a second time without indexes but with keys, defaults and constraints. This may make it easier to separate the nonclustered indexes from any defaults. The reason for not creating the nonclustered indexes at this stage is that indexes and statistics not associated with indexes may have interspersed indid values. If the nonclustered indexes were created now, it may be necessary to remap the indid values for statistics only rows from the original database.
Create and Populate Mapping Table
When objects like tables are created in the new database, the new object is likely to have a different object id from the original object id. Since the sysindexes uses the object id to identify tables, it is necessary to create a mapping table to hold the original and new object ids for each table. The table below also holds the userid. This may not be necessary, but this set of scripts has not been tested on databases where tables are owned by users other than dbo. It is also not necessary to have the name column, but it is included for convenience.
CREATE TABLE [tobjects] ( name sysname , oid int , ouid int , id int , uid int
Switch back to the original database, and use the following script to populate the object id mapping table.
INSERT [sut]..[tobjects] (name, oid, ouid, id, uid)
SELECT o.name, o.id, o.uid, n.id, n.uid
FROM sysobjects o
INNER JOIN [sut]..sysobjects n ON n.name = o.name
WHERE ( OBJECTPROPERTY(o.id, N’IsUserTable’) = 1
OR OBJECTPROPERTY(o.id, N’IsUserView’) = 1 )
AND OBJECTPROPERTY(o.id, N’IsMSShipped’) = 0
ORDER BY o.name