Check, Disable, Enable Foreign Key(s) – SQL Server

— Query to see all available foreign keys in the DB
SELECT
f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,
f.is_disabled
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
where
f.name = ‘<FK Name>’

— Disable foreign key
ALTER TABLE <Table Name> NOCHECK CONSTRAINT <FK Name>
— Enable foreign key
ALTER TABLE <Table Name> CHECK CONSTRAINT <FK Name>

More references …

http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/

http://www.mssqltips.com/sqlservertip/1376/disable-enable-drop-and-recreate-sql-server-foreign-keys/

Rebuild all indexes on a Table(s) – SQL Server

Rebuild all indexes on one table …

ALTER INDEX ALL ON <Table Name> REBUILD

Following link has code to rebuild all indexes on all tables of a DB …

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

run command to Login to remote m/c as admin using Remote Desktop Connection

mstsc /admin /V: IP

Restore SQL Server DB using RESTORE FILELISTONLY sql command

— See the logical db files info for the backup file
RESTORE FILELISTONLY FROM DISK = N’F:\DBBackup\DB_local.bak’ With file = 1

— Restore DB to different hard drive location (existing logical data/log name need to be mapped to new mdf/ldf file names in the new location)
RESTORE DATABASE MYDB1 FROM DISK = N’F:\DBBackup\DB_local.bak’
WITH MOVE N’MYDB_Data’ TO N’E:\SQL\Data\DB.mdf’,
MOVE N’MYDB_Log’ TO N’E:\SQL\Log\DB_log.ldf’,
MOVE N’MYDB_Log1′ TO N’E:\SQL\Log\DB_Log1.ldf’
GO

 

Check the DB Restore Status Using SQL

Following sql is helpful to see the DB restore status from time to time. Especially DB’s that take longer time to restore and when sql server management studio is not showing proper status then this query is very handy to check the status.

SELECT
percent_complete AS [PercentComplete]
,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes]
,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes]
,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes]
,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion]
,st.TEXT AS [CommandSQL]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
WHERE command LIKE ‘%RESTORE DATABASE%’

Auto login in PuTTY with a password – Link

http://superuser.com/questions/44106/is-there-a-way-to-auto-login-in-putty-with-a-password

C:\Windows\System32\putty.exe uid@IP -pw pwd

Shelving in Perforce – Link

http://filehost.perforce.com/downloads/media/shelving/shelving.html

Greenshot – Helps to Capture Images

http://getgreenshot.org/downloads/

PrtSc =>  captures section of the screen

Ctrl + Shift  + C => copies the screen to clipboard

Ctrl + V => regular command to paste the screen shot where ever you want

(Dev Tool, Thx Ramesh for letting me know this nice s/w).

Shrink the SQL Server DB Log File

— Check to see log size and log space used
DBCC sqlperf(logspace)

— Check to see if there are any open transactions
DBCC OPENTRAN

— Shrink the log

–The following example shrinks the DB log file to 1 MB.
–To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

— Ex: AdventureWorks DB
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO

— Following command will help to see the progress of the shrink operation
SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

Reference:

http://stackoverflow.com/questions/646845/sql-server-2008-log-will-not-truncate-driving-me-crazy

http://technet.microsoft.com/en-us/library/ms190757.aspx

http://www.calazan.com/how-to-check-the-progress-of-the-shrink-database-task-in-sql-server-2005/

http://blog.sqlauthority.com/2008/07/25/sql-server-dbcc-shrinkfile-takes-long-time-to-run/