System Administration

How to Drop All Relations in A Database

Welcome Back! I hope you enjoy the content on this site. If you have not done so already, you may want to subscribe to my RSS feed or become a fan of this blog on Facebook. Thanks for visiting!

this is related to my earlier post on dropping all indexes from an sql database but this one is to delete all foreign key relations from a database.

set nocount on
 declare @statements cursor
 set @statements = cursor static for
 select  'alter table ' + quotename(ctu.table_schema) + '.' + quotename(ctu.table_name) +
         ' drop constraint ' + quotename(cc.constraint_name)
 from  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as cc
          join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu
           on cc.constraint_catalog = ctu.constraint_catalog
              and cc.constraint_schema = ctu.constraint_schema
              and cc.constraint_name = ctu.constraint_name

 open @statements
 declare @statement nvarchar(1000)
 While  (1=1)
  begin
         fetch from @statements into @statement
                if @@fetch_status <> 0
                 break
                exec (@statement)
         end

Hope this was Helpful!

Hatim

How To Drop All Indexes From A Database

I was doing some performance tweaking of a batch job that was hanging and i was suspecting the indexes in the database to have something to do with it shocking up so I used this script to clear all indexes.

Ironically it was a missing index that caused the bottleneck. But here it is for anyone that might need it.

DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)

DECLARE [indexes] CURSOR FOR

    SELECT        [sysindexes].[name] AS [Index],
            [sysobjects].[name] AS [Table]

    FROM        [sysindexes]

    INNER JOIN    [sysobjects]
    ON        [sysindexes].[id] = [sysobjects].[id]

    WHERE        [sysindexes].[name] IS NOT NULL
    AND        [sysobjects].[type] = 'U'

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'

    FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END

CLOSE        [indexes]
DEALLOCATE    [indexes]

Hope this was helpful!

Hatim

Issue with the NIC driver for Windows 7 in VMWare

It’s been a while that I wanted to try the windows 7 beta so yesterday I loaded the ISO in virtual machine using VMWare. The install went fine but when windows started it didn’t detect the NIC.

So after going through some forums here is the solution:

- Shut down the VM.

- Open the .vmx file associated with the machine in notepad

- Add this line at the bottom of the file

ethernet0.virtualDev = "e1000"

- Start the VM

That’s it!

How To Backup Microsoft Exchange Server 2007

Bellow is a detailed guide on how to backup your Microsoft exchange server 2007.

Since it’s always a good idea to have a backup laying around when production machines decide to act up.

Backing up Exchange Server 2007 also clears up the log files from the storage group folder usually in "C:\Program Files\Microsoft\Exchange Server\Mailbox\First Storage Group".

Exchange keeps a bunch of log files which can be used to restore it’s state in case  the storage group is corrupted kinda like SQL Server .ldf files. The log files in exchange are named in this fashion E0000001BDF.log, E0000001BD0.log, E0000001BD1.log ….

These log files can consume a lot of disk space specially on large organizations.

So let’s get to it!!

1 - Launch Windows Backup

2 - Backup Wizard Screen

3 - Choose backup up files and settings

4 - Select "Let me choose what to back up"

5 - Expand the "Microsoft Exchange Node" and select the stores you would like to backup

6 - Select the location where you would like to store you backup file and give a name to your backup file

I always include a version number in the name of the backup file, it’s easier to determine the date of the backup this way.

7 - review your settings and click finish

You can use the "Advanced" options to specify more settings or set a schedule for your backup.

You can also use the command line to set a scheduled backup. the following KB explains all the command line arguments How to use command line parameters with the Ntbackup command in Windows Server 2003

Hope this was helpful!

Hatim