November 14, 2011 at 2:40 am
Hello,
I have to move some databases to SQL2005 / SQL2008. I would like to know if I already performed a DBCC CHECKDB with DATA_PURITY on my all sql 2005 databases (means dbi_dbccFlags = 2), I need after restoring backup on my SQL SERVER 2008 R2 to perform again DBCC CHECKDB with DATA_PURITY.
Thanks for your helps,
Eric
November 14, 2011 at 2:56 am
This was removed by the editor as SPAM
November 14, 2011 at 9:08 am
I checked but I saw nothing about that !!
Thx
November 14, 2011 at 9:10 am
Rebuild index, update stats.
Then monitor the server for newly poor performing code.
Transfer logins obviously.
November 14, 2011 at 9:15 am
This was removed by the editor as SPAM
November 14, 2011 at 9:24 am
When transferring a database to a newer version of SQL by using backup/restore, I perform the following steps:
1 - Transfer logins
2 - Reconcile SQL logins
3 - Perform a complete reindex (this will update the statistics, too)
4 - Perform a DBCC CHECKDB
5 - Backup the database in the new environment
I have seen disaster strike when the database was not completely reindexed. Never saw any issues with the DBCC CHECKDB and I've often thought about skipping that step, but I know the one time I don't run it there will be a problem.
...
November 14, 2011 at 9:36 am
I know but the only thing I need is if it's necessary to perform again a CHECKBD WITH PURITY after moving databases.
November 14, 2011 at 9:38 am
ERIC CRUDELI (11/14/2011)
I know but the only thing I need is if it's necessary to perform again a CHECKBD WITH PURITY after moving databases.
Only if you're coming from sql 2000.
Once you've done checkdb once with data_purity. You don't have to ask for it again, it'll be done by default forever.
From http://msdn.microsoft.com/en-us/library/ms176064.aspx
DATA_PURITY
Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.
For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.
If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
Validation errors reported by this option cannot be fixed by using DBCC repair options. For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005.
November 14, 2011 at 10:21 am
run dbcc updateusage
set compatibility level
set default schema for each user to DBO (if all objects were previously owned by dbo which is the usual case
drop the schemas created by the upgrade you don't need
set page verify to checksum
back it up!
---------------------------------------------------------------------
November 14, 2011 at 10:22 am
Ok so basically I have to check for all databases if dbi_dbccFlags = 2 before moving them to SQL 2008 R2.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply