Tuesday, August 25, 2009

Sql Server Script to Restore a Database

use master

alter database DBName
set single_user with rollback immediate

restore database DBName
from disk = 'C:\DBBackup.bak'

-- use the following to overwrite log in case the log file was not backed up
with replace

Tuesday, August 18, 2009

SQL Commands - Disable Constraints

The following commands are from this URL
http://gchandra.wordpress.com/2008/02/18/sql-server-clean-your-database-records-and-reset-identity-columns-the-shortest-path/

--Disable Constraints & Triggers
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

--Perform delete operation on all table for cleanup
exec sp_MSforeachtable 'DELETE ?'

--Enable Constraints & Triggers again
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

--Reset Identity on tables with identity column
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

I found the commands are very useful. For example, I once got the foreign key constraint error when trying to import data into my database. I executed the first two commands to disable constraints and triggers first, then imported the data, the errors were gone. After importing data, I then executed the last three commands to re-enable and reset stuff.