Steps to script out the primary key constraints or clustered indexes in a database using DMO

Why do you need this? One of the drawbacks of the “Generate Scripts” option in SQL Server Management Studio is that SSMS scripts out the Primary Key constraints along with the CREATE TABLE script. So, if the table is already created, the primary key will not be created if you use the script that was generated by the SSMS Generate Scripts Wizard

The steps that you need to follow are for generating the primary key constraints or clustered indexes for user defined tables: 

1. You will have to create the Stored Procedure from my previous blog post on the source database. 

2. Then enable the OLE AUTOMATION and XP_CMDSHELL on the server. 

EXEC sp_configure 'Show Advanced',1

reconfigure with override

go

EXEC sp_configure 'Ole Automation',1

reconfigure with override

go

EXEC sp_configure 'xp_cmdshell',1

reconfigure with override

go

 

3. Use the following script to generate the list of Primary Keys for user defined tables in a database: 

Script for generating primary key creation script: 

set nocount on 

print 'DECLARE @return_value int ' 

select ' 

EXEC @return_value = [dbo].[proc_genscript] 

@ServerName = ''SQLSERVER NAME'', /*Replace this with the SQL server name*/ 

@DBName = ''Northwind'', /*Replace this with the database name*/ 

@ObjectName = '''+[name]+''', 

@ObjectType = ''KEY'', 

@TableName = ''' +object_name(parent_object_id)+''', 

@ScriptFile = ''C:\Database\'+[name]+'.sql'' /* Replace this with the directory path where you want the create scripts to be saved */ 

SELECT ''Return Value '' = @return_value'+char(13) 

from sys.objects 

where type = 'PK' 

and parent_object_id in (select id from sys.sysobjects where xtype = 'U')

Script for generating clustered indexes creation script: 

set nocount on 

print 'DECLARE @return_value int ' 

select ' 

EXEC @return_value = [dbo].[proc_genscript] 

@ServerName = ''SQLSERVER NAME'', /*Replace this with the SQL server name*/ 

@DBName = ''Northwind'', /*Replace this with the database name*/ 

@ObjectName = '''+[name]+''', 

@ObjectType = ''INDEX'', @TableName = ''' +object_name(id)+''', 

@ScriptFile = ''C:\Database\'+[name]+'.sql'' /* Replace this with the directory path where you want the create scripts to be saved */ 

SELECT ''Return Value '' = @return_value'+char(13) 

from sys.objects 

where type = 'PK' 

and parent_object_id in (select id from sys.sysobjects where xtype = 'U')

4. Then use the scripts obtained from the above output to generate the Primary Key creation scripts in the folder that you mentioned. In the above example, the *.sql scripts would get created in the C:\Database folder. 

5. Then use the following script to generate the SQLCMD commands for running those *.sql scripts against the source database. 

create table #filenames (file_name varchar(1000))

insert into #filenames

exec xp_cmdshell 'dir <folder path>\*.sql /b'

select 'sqlcmd -S <servername> -d <databasename> -E -i ' +file_name

from #filenames

where file_name like '%sql%'

drop table #filenames

where  

<server name> = SQL Server instance name 

<database name> = Database Name on which you want the objects to be created 

<folder path> = the folder where you want the .sql file to be created. This has to be the same folder path where you saved the .sql files in Step 3. 

6. Now put the commands obtained in Step 5 into a .bat file saved at the same location where the .sql files were created in Step 5. Run the .BAT file from a command prompt window and all the script files will get executed against the context of the database that you provided. 

The above set of steps loops through the sysindexes system catalog and picks up all non-fulltext index and generates the CREATE INDEX scripts for all the user database tables using DMO. 

**** Before running this on a production database, please test out the above solution on a test database

CHECKDB with Repair Allow Data Loss: Should I shoot myself?

Over the past years, I have seen multiple DBAs execute DBCC CHECKDB with REPAIR ALLOW DATA LOSS to repair the SQL Server database corruption in their database without understanding repercussions. Even when you are working with CSS on such issues, CSS engineers actually explain what the command does and also send across a disclaimer stating why this command should NOT be executed on a database and what the repercussions are. Alas due to a lack of a sound database backup strategy, a lot of DBAs are forced to resort to losing data. In a nutshell, Repair Allow Data Loss tries to first repair the corruption in the page but if it cannot, then it checks if it can drop the page and repair the error and hence the name Allow Data Loss! I had a question for all the DBAs who advocate the use of this command:

How would you like your Payroll DBA to run the command? (Good enough food for thought!!)

The only way to compare how much data you lost would be to perform a CHECKDB before running the CHECKDB with REPAIR ALLOW DATA LOSS and after the CHECKDB has completed. The row counts returned by the two CHECKDB outputs would have to be compared. If CHECKDB is failing due to system catalog corruption, then you are plain out-of-luck.

What most people fail to understand that CHECKDB can verify integrity of the database based on the SQL database rules imposed on it like referential integrity, data length, record structures, page structures, header correctness etc. But these are database integrity rules that we are aware of. What about the business logic rules that you imposed while designing the database.

Eg: Let’s say you had a database table called Employee which depended on a Department ID valued to be present in the Department table. No foreign key relationship exists between the two tables. You experienced corruption in the database and used Repair Allow Data Loss command along with CHECKDB which dropped a few pages from the Department table. However, CHECKDB cannot know that a Department ID is missing in the Department table because you didn’t create a Foreign Key between the two tables. So when your application now tries to retrieve the Department ID of the Employee, it will BARF on you! This is the kind of scenario we specifically want to avoid which is why we advise against the use of data loss commands.

SQL Server wouldn’t know what data is supposed to present in the table rows. The only thing that SQL can verify while you are retrieving data or checking integrity of the database is that the column data and column datatype match. I have heard questions like “Why can’t SQL Server figure out if the value is correct?”. Well, would you know why a person’s name is Uncle Sam and not Uncle Bill? 🙂 So, how would SQL Server know that the FirstName column (an example) was supposed to store Sam and not Bam. As per SQL Server, both strings match the column datatype, hence from a structural and logical integrity, the data is correct.

Database Integrity Checks

Database integrity checks are recommended to ensure that the database consistency is intact and if there is a problem with consistency, then it is reported to the appropriate team(s) so that necessary action can be taken to rectify it. This can be done with the help of Database Maintenance Plans. In the event that a CHECKDB on a database fails, then it needs to be reported by Email(using xp_sendmail or SQL Agent Mail or Database Mail for SQL Server 2005) or events fired in the Operating System Event Logs (if these are monitored regularly) with the help of Operators which can be configured for SQL Server Agent. The xp_sendmail feature is not available for SQL 2005 64-bit versions.
The frequency of these checks largely depends on the following factors:
1. Importance of the database
2. How often data changes in the database (If a database integrity check fails for a database where data is not modified, then it would be advisable to restore the last known good backup rather than trying to repair the consistency database)
3. The size of the database
4. In the event of consistency checks failing, it needs to be determined which is the most feasible option:
a. Restore the last known good backups in accordance with the recovery model for that database to allow for a minimum amount of data loss
b. Or try and repair the database and falling back on Option (a) only if this fails In case a repair option is suggested in the CHECKDB output, it is important to note that a REPAIR_ALLOW_DATA_LOSS be never performed on the database without understanding its full consequences and consulting Microsoft PSS. In the event, that this route needs to be taken, it is always recommended to fall back on the last known good backups if possible. The REPAIR_FAST and REPAIR_REBUILD repair options can be performed without having any data loss. Please note that these are time consuming operations and in the event of database inconsistency it is not possible for us to predict how long these tasks would run for. Also, the time taken for CHECKDB on a database cannot be predicted. An educated guess can be made to how long it would take by referring to the last durations of the CHECKDB operations on that particular database. For the above mentioned repair options, please refer the following article: http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx

REPAIR_ALLOW_DATA_LOSS
Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database. This option shouldn’t be used without the assistance of a SQL Support Engineer.
REPAIR_FAST
Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.
REPAIR_REBUILD
Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss. In case of SQL Server 2005, you have the option of checking the suspect_pages in the MSDB database to find out the affected pages.

Please refer the following articles for more detailed information:
Suspect_pages table (SQL 2005)
http://msdn2.microsoft.com/en-us/library/ms174425.aspx
Understanding and managing the suspect_pages table
http://msdn2.microsoft.com/en-us/library/ms191301.aspx
Designing a Backup and Restore Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_63eh.asp

In the event, that only a few pages have a problem, then for SQL Server 2005 a page level restore can be performed. Online page restore operation is a feature available in SQL Server 2005 Enterprise Edition but all other Editions of SQL Server 2005 support offline page level restores. The page level restores are done using the NORECOVERY option for that database. Then a backup of the current transaction log is taken and applied to the database with the RECOVERY option. This feature is applicable to databases in FULL or BULK-LOGGED recovery models.
For performing page level restores, please refer the following article: http://msdn2.microsoft.com/en-us/library/ms175168.aspx
It is highly important that a disaster recovery plan is in place to ensure the following:

  • · A plan to acquire hardware in the event of hardware failure
  • · A communication plan.
  • · A list of people to be contacted in the event of a disaster.
  • · Instructions for contacting the people involved in the response to the disaster.
  • · Information on who owns the administration of the plan.
  • · A checklist of required tasks for each recovery scenario. To help you review how disaster recovery progressed, initial each task as it is completed, and indicate the time of completion on the checklist.

In conclusion, if the database is of a considerably large size, then an integrity check needs to be scheduled during a window when the load on the server is at a minimum. The definition of minimum here refers to a load which is lesser than the normal workload on the server. If the database sizes are quite small, then daily integrity checks on the database would be the order of the day.
It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time.

Recommendations for Good DBCC Performance
· Run CHECKDB when the system usage is low.
· Be sure that you are not performing other disk I/O operations, such as disk backups.
· Place tempdb on a separate disk system or a fast disk subsystem.
· Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
· Avoid running CPU-intensive queries or batch jobs.
· Reduce active transactions while a DBCC command is running.
· Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.