copy index statistics from production to development.

I have a very large database that I need to be able to copy the statistics only from the production database server to my develoment environment. I need to be able to reproduce the execution plans on development without moving all the data. MS does this when they need to review the exeecution plan for large complex databases.

Need help.

See if this helps.

Transferring SQL Server Statistics From One Database to Another

http://www.sql-server-performance.com/jc_transferring_statistics.asp

AMB

|||

Thanks for your help.

|||

The technique described in the link is not a supported mechanism. It will not work in SQL Server 2005 also. It is risky to manipulate system metadata especially page pointers from one database to another. You will end up corrupting the database. The ability to create statistics and histograms for indexes is available in SQL Server 2005. You can use the SMO scripting capability to do this and create an empty database with all the stats from production. See the following topics for more details:

http://msdn2.microsoft.com/en-us/library/ms186472.aspx -- Use Script Statistics option

Copy database with full-text index

Hi,

Can anyone please explain the proper precedure for copying a SQL Express database between two instances?

I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application.

I run into two problems every time I copy:

1) Orphaned users. I have to drop the database users and the re-map the server users to database users.

2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog.

And I suspect there's an easier way..

Regards,
Jens Erik

Hi Jens,

The behavior with users is expected. Users and Logins are indelibly linked and only the Users are stored in the database. When you move a database to production, you have to create the appropriate Logins on the new server and then link them to the Users in your database.

Full-text indexes are another odd case. These indexes are stored outside of the database as they are created by an external process. Again, just copying the mdf file will not copy and move the Full-text index. Backup/Resore does handle the Full-text index, so you should consider that option. There is more information about this in BOL. Check out http://msdn2.microsoft.com/en-us/library/ms190436.aspx.

Mike

Copy database with full-text index

Hi,

Can anyone please explain the proper precedure for copying a SQL Express database between two instances?

I am accessing the database without problems from a local web application. And I want to copy the database to a SQL Express instance on another server, running the same web application.

I run into two problems every time I copy:

1) Orphaned users. I have to drop the database users and the re-map the server users to database users.

2) The full-text indexes are not available after copy, so I have to drop and re-create the indexes and the catalog.

And I suspect there's an easier way..

Regards,
Jens Erik

Hi Jens,

The behavior with users is expected. Users and Logins are indelibly linked and only the Users are stored in the database. When you move a database to production, you have to create the appropriate Logins on the new server and then link them to the Users in your database.

Full-text indexes are another odd case. These indexes are stored outside of the database as they are created by an external process. Again, just copying the mdf file will not copy and move the Full-text index. Backup/Resore does handle the Full-text index, so you should consider that option. There is more information about this in BOL. Check out http://msdn2.microsoft.com/en-us/library/ms190436.aspx.

Mike