ORACLE IS_MEMBER function like in SQL SERVER

CREATE OR REPLACE FUNCTION IS_MEMBER(  USER_ID Varchar  ) 

RETURN   NUMBER  IS    

dResult NUMBER; 

sLoginUser Varchar(200); 

BEGIN  

   dResult :=0;    

   SELECT  user into sLoginUser  from  dual; 

    IF upper(sLoginUser)= upper(USER_ID) THEN    

       dResult :=1; 

    END IF;  

RETURN  dResult;  

END;

/

Essential SQL Server Date, Time and DateTime Functions

Essential SQL Server Date, Time and DateTime Functions

Part I: Standard Date and Time Functions

I've posted some variations of these before, but here they all are in 1 easy package:  The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

They are simple, easy, and brief and you should use them any time you need to incorporate any date literals or date math in your T-SQL code.  I have always wondered why T-SQL omits these basic functions, but the beauty of user defined functions is that we can create them ourselves.

create  function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end
go

create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
-- Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
    begin
    return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
    end
go

create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments). 
returns datetime
as
    begin
    return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
    end
go

create function TimeOnly(@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
-- Thanks, Peso! 
returns datetime
as
    begin
    return dateadd(day, -datediff(day, 0, @datetime), @datetime)
    end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
    begin
    return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
    end
go

Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Usage Examples:

•  where TransactionDate >= dbo.Date(2005,1,2)  -- no formatting or implicit string conversions needed for date literals
• select dbo.Date(year(getdate()), 1,1) -- returns the first day of the year for the current year.
• select dbo.DateOnly(getdate()) -- returns only the date portion of the current day.
• if dbo.TimeOnly(SomeDate) = dbo.Time(5,30,0)  -- check to see if the time for a given date is at 5:30 AM
• select dbo.Date(year(getdate()), month(getdate()),1) -- returns the first day of the current month.
• select dbo.Date(year(getdate()), month(getdate())+1,0) -- returns the last day of the current month.
• where SomeDate >= dbo.DateOnly(getdate()) and SomeDate < dbo.DateOnly(getDate())+1 -- a simple way to get all transactions that occurred on the current date
• select dbo.DateOnly(getdate()) + 1 + dbo.Time(14,30,0) -- returns tomorrow at 2:30 PM.

and so on ....

ENCRYPTING BUISNESS LOGIC IN THE BACK-END

Hi All,

Use the WITH ENCRYPTION Option in SQL Server when ever you want to hide the business- logic

in Views / Procedures / Functions. (Specially in Client sites when ever we use procedures).  

This is part of the application code, therefore please pay more attention to that.

Note : that you will not be able to regenerate the scripts from the server, if you use this option.

This is a sample

CREATE VIEW TEST

WITH ENCRYPTION

AS SELECT 'TEST' AS TI

SQL SERVER 2005 builtin Encryption

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecretTable_SecretData_Key'

GO

CREATE CERTIFICATE SecretTable_SecretData_Key

      WITH SUBJECT = 'IWMI',

      START_DATE = '01/04/2008',

      EXPIRY_DATE = '10/31/2050'

CREATE SYMMETRIC KEY SecretTable_SecretData_Key  

    WITH ALGORITHM = TRIPLE_DES  

    ENCRYPTION BY CERTIFICATE SecretTable_SecretData_Key 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'SecretTable_SecretData_Key';

 GO

  

ALTER TABLE <TABLE NAME>

ALTER COLUMN <COLUMN NAME> [varchar](400)

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SecretTable_SecretData_Key'

  

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

  

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

OPEN SYMMETRIC KEY  SecretTable_SecretData_Key  DECRYPTION BY CERTIFICATE SecretTable_SecretData_Key

declare @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')

declare @vartest as nvarchar(400) 

SELECT convert(varchar(max), decryptbykeyautocert(cert_id('SecretTable_SecretData_Key'), NULL, EBSAL_BASIC_SALARY))   FROM HS_HR_EMP_BASICSALARY

CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]

Advanced SQL Injection In SQL Server Applications

Advanced SQL Injection In SQL Server Applications

Table of Contents

 

[Abstract]...........................................................................................................................3

[Introduction].....................................................................................................................3

[Obtaining Information Using Error Messages].................................................................7

[Leveraging Further Access].............................................................................................12

[xp_cmdshell]...............................................................................................................12

[xp_regread]..................................................................................................................13

[Other Extended Stored Procedures]............................................................................13

[Linked Servers]............................................................................................................14

[Custom extended stored procedures]...........................................................................14

[Importing text files into tables]...................................................................................15

[Creating Text Files using BCP]...................................................................................15

[ActiveX automation scripts in SQL Server]................................................................15

[Stored Procedures]...........................................................................................................17

[Advanced SQL Injection]................................................................................................18

[Strings without quotes]................................................................................................18

[Second-Order SQL Injection]......................................................................................18

[Length Limits].............................................................................................................20

[Audit Evasion].............................................................................................................21

[Defences]........................................................................................................................21

[Input Validation]..........................................................................................................21

[SQL Server Lockdown]...............................................................................................23

[References].....................................................................................................................24

Appendix A - 'SQLCrack'.................................................................................................25

(sqlcrack.sql).........................

 

http://www.ngssoftware.com/papers/advanced_sql_injection.pdf

 

 

Manipulating Microsoft SQL Server Using SQL Injection

 

select * from OPENROWSET( 'SQLoledb','server=pacthr\pact;uid=hsenid;pwd=hsenid','select * from hs_hr_employee' )

 

 

Manipulating Microsoft SQL Server Using SQL Injection

http://www.appsecinc.com/presentations/Manipulating_SQL_Server_Using_SQL_Injection.pdf

 

 

Auditing Through Triggers (SQL Server)

Reprinted with Permission by Quest Software June 2004

---

Auditing Through Triggers
Robert Marda, bigdough.com

Introduction

In this article I will give code examples for a few triggers. For an excellent explanation about triggers in general and how they work in SQL Server 7.0 and SQL Server 2000 please see the following articles written by Brian Kelley: Triggers in SQL Server 7.0 and 2000 - The Common Ground and Triggers in SQL Server 7.0 and 2000 - What's New. The first article will explain the special tables called inserted and deleted.

I believe these examples will work on SQL Server 2000, however they were only tested on SQL Server 7.0. First we need to have a table to work with. Execute this code in Query Analyzer:

CREATE TABLE [Components] (         [Iden] [int] IDENTITY (1, 1) NOT NULL ,         [ComponentName] [varchar] (25) NULL ,         [SerialNumber] [varchar] (25) NULL ,         [Comments] [varchar] (75) NULL ,         [UserName] [varchar] (50) NULL ,         [UpdateDate] [datetime] NULL ,         [UpdatedBy] [varchar] (35) NULL ) ON [PRIMARY] GO  CREATE TABLE [ComponentsDeleted] (         [Iden] [int] NOT NULL ,         [ComponentName] [varchar] (25) NULL ,         [SerialNumber] [varchar] (25) NULL ,         [Comments] [varchar] (75) NULL ,         [UserName] [varchar] (50) NULL ,         [DeletedDate] [datetime] NULL ,         [DeletedBy] [varchar] (35) NULL ,  ) ON [PRIMARY] GO

Insert/Update Triggers

One of the simplest ways to design a trigger is one that fires whenever any column in a table is updated or whenever a row is inserted. The following code can be executed in Query Analyzer to create a trigger that captures the date a row was inserted or updated and the login for the person that made the last change:

CREATE TRIGGER updatedby ON dbo.Components FOR INSERT, UPDATE AS UPDATE c SET UpdateDate = getdate(), UpdatedBy = SYSTEM_USER FROM inserted i INNER JOIN dbo.Components c ON i.Iden = c.Iden

To test the trigger insert some rows into the table Components and then update one or two rows. Now suppose you only need to know when a component is swapped for a similar component. A change in the serial number would indicate such a swap. You could modify the above trigger (using an ALTER TRIGGER command) to only fire when the SerialNumber column is changed. Execute this code:

ALTER TRIGGER updatedby ON dbo.Components FOR INSERT, UPDATE AS  IF UPDATE(SerialNumber) BEGIN       UPDATE c SET UpdateDate = getdate(), UpdatedBy = SYSTEM_USER      FROM inserted i      INNER JOIN dbo.Components c ON i.Iden = c.Iden  END

Test this trigger by updating a value in the column Comments, then in the column SerialNumber.

Delete Trigger

Now you want to capture the components that are deleted from the components table. These could be components being returned. The below trigger will insert a row into ComponentsDeleted whenever a row is deleted from Components:

CREATE TRIGGER deletedby ON dbo.Components FOR DELETE AS INSERT INTO ComponentsDeleted (Iden, ComponentName, SerialNumber, Comments, UserName, DeletedDate, DeletedBy) SELECT Iden, ComponentName, SerialNumber, Comments, UserName, getdate(), SYSTEM_USER FROM deleted

Delete a row or two from table Components. Now take a look at your ComponentsDeleted table. You will find your deleted rows there with the date and time they were deleted.

Conclusions

Setting up a simple auditing system using triggers similar to those described in this article will take very little time and could be useful if you must know when certain actions are performed in your database and by whom.

 

 

From: Gayanga [mailto:Gayanga@hsenid.lk]
Sent: Wednesday, September 21, 2005 1:52 PM
To: developers@hsenid.lk
Subject: Auditing Through Triggers (SQL Server)

 

 

Nice way to use functions _ORACLE

Hi !
Check this

CREATE OR REPLACE PROCEDURE SP_HS_HR_MEDI_MEDICAL_LETTER (MEDI_CURSOR in OUT
MEDICAL_LETTER_PACKAGE.MEDICAL_LETTER_CURSOR,EMP_NO IN VARCHAR2,SCHEME_CODE
IN VARCHAR2)
AS
BEGIN
OPEN MEDI_CURSOR FOR

SELECT E.EMP_NUMBER, E.EMP_TITLE,
E.EMP_MIDDLE_INI, E.EMP_SURNAME,
S.MSC_CODE,
S.MSC_NAME, S.MSC_AMOUNT-SUM (R.MRG_CLAIM_AMOUNT) AS BALANCE
FROM HS_HR_MEDI_SCHEME S, HS_HR_MEDI_REGISTER R, HS_HR_EMPLOYEE E
WHERE ((S.MSC_CODE = R.MSC_CODE)
AND (E.EMP_NUMBER = R.EMP_NUMBER)
AND (E.EMP_NUMBER = EMP_NO)
AND (S.MSC_CODE = SCHEME_CODE))
GROUP BY E.EMP_NUMBER,
E.EMP_TITLE,
E.EMP_MIDDLE_INI,
E.EMP_SURNAME,
S.MSC_CODE,
S.MSC_NAME,
S.MSC_AMOUNT
UNION ALL

SELECT E.EMP_NUMBER, E.EMP_TITLE,
E.EMP_MIDDLE_INI, E.EMP_SURNAME,
S.MSC_CODE,
S.MSC_NAME, S.MSC_AMOUNT

FROM HS_HR_MEDI_SCHEME S,HS_HR_EMPLOYEE E

WHERE S.MSC_CODE = SCHEME_CODE
AND E.EMP_NUMBER = EMP_NO
AND E.EMP_NUMBER NOT IN
(SELECT E.EMP_NUMBER
FROM HS_HR_MEDI_SCHEME S, HS_HR_MEDI_REGISTER R, HS_HR_EMPLOYEE E
WHERE (S.MSC_CODE = R.MSC_CODE)
AND (E.EMP_NUMBER = R.EMP_NUMBER)
AND (E.EMP_NUMBER = EMP_NO)
AND (S.MSC_CODE = SCHEME_CODE));

END;
/

Regards
Bandula Liyanage, Software Engineer
HTA
hSenid Software International (PVT) Ltd
Telephone: +94-112-699754 Fax: +94-112-673845
E-mail: bandula@hsenid.lk <mailto:bandula@hsenid.lk>
Web: www.hsenid.com

Its all about people
**** DISCLAIMER *****
This e-mail and any attachments thereto are intended for the sole use of the
recipient(s) named above and may contain information that is confidential
and/or proprietary to the hSenid Software International (Pvt.) Ltd. Any use
of the information contained herein (including, but not limited to, total or
partial reproduction, communication, or dissemination in any form) by
persons other than the intended recipient(s) is prohibited. If you have
received this e-mail in error, please notify the sender immediately and
delete it.

How to get OLD / NEW Values in SQL Server Triggers

CREATE TRIGGER trig_updateEmployeeName

 

ON HS_PR_EMPLOYEE

 

FOR UPDATE

 

AS

 

DECLARE @oldName VARCHAR(100)

 

DECLARE @newName VARCHAR(100)

 

IF NOT UPDATE(EMP_FIRST_NAME)

 

BEGIN

 

RETURN

 

END

 

SELECT @oldName = (SELECT EMP_FIRST_NAME FROM Deleted)

 

SELECT @newName = (SELECT EMP_FIRST_NAME FROM Inserted)

 

PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'

 

When, How, Where and Why to use Dispose

When, How, Where and Why to use Dispose

 

Dispose, Part 1
What does that pesky Dispose(bool disposing) method do for me?

On a blank form, you’ll see several lines generated:

private System.ComponentModel.Container components = null;
protected override void Dispose( bool disposing ) {
    if( disposing ) {
         if (components != null) {
            components.Dispose();
         }
     }
     base.Dispose( disposing );
}

Essentially this is placeholder code for when you add a component that isn’t a control to the form. When the form disposes, this code is there to dispose it’s associated components. The best example of this is the System.Windows.Forms.Timer and System.Windows.Forms.ToolTip.

If I drop on a timer from the toolbox onto the form and look in InitializeComponent, there are two new lines generated:

this.components = new System.ComponentModel.Container();
this.timer1 = new System.Windows.Forms.Timer(this.components);

Essentially, the constructor of Timer has just added itself to the components collection. When the form is in the process of disposing it will dispose the timer.

 

Dispose - Part 2
What is the purpose of the two dispose methods on Control & Component, etc?

Several pieces of background information:

 

Component implements IDisposable

Control inherits Component, thus inheriting IDisposable.

IDisposable has one method called Dispose()

Garbage collection (Finalization) happens on a different thread

Component (base class of Control) has the following pattern:

~Component() {
  // I am in the finalizer
  Dispose(/*currently disposing=*/false);
}             

Dispose() {
  // I’ve been told to clean up outside the finalizer
  Dispose(/*currently disposing=*/true);
  
 }

protected virtual void Dispose(bool disposing) {
    if (disposing) {
        // do stuff that can only be done from the main thread.
    }
    else {
       // this is the finalizer thread.  Be wary of touching other
       // objects as they may have already finalized. 
    }
   // do cleanup actions that can be done either from the finalizer 
   // thread or the main thread.
}

In other words Dispose(bool disposing) is the unified place for cleanup code. It is called from both the Finalizer and when someone explicitly calls the IDisposable.Dispose method.

If you want to prevent the finalizer from getting called, you can call GC.SupressFinalize from this method if disposing is true.

Here's a more in depth article on how to implement Dispose and Close methods. One little known fact: If you implement Dispose, you should allow it to be called multiple times.

Next time: Can I just let the GC get everything? When do I have to worry about Dispose?

 

Dispose, Part 3
Can I just let the GC get everything? When do I have to worry about Dispose?

 

If the object implements IDisposable then you should think about how the object is getting cleaned up. Objects that implement IDisposable usually do so because they are holding on to real resources that should be freed deterministically.

Rule of thumb: If the object implements IDisposable then you should think about how the object is getting cleaned up.

When a control is disposed, it disposes all of its children. The only time you get automagic disposal of controls is when you do Application.Run(new Form()). (We like to call this showing the form modelessly). When the form gets WM_CLOSE message, it starts cleaning itself up while it can.

However if you do a Form.ShowDialog(), the form is NOT disposed. (We like to call this showing the form modally). This is because you may want to go in after the form has closed and look at the state of the child controls to determine the next action of your program.

If you add and remove controls from your form dynamically, you should call dispose on them – otherwise you’ll accumulate extra unwanted window handles in your process. Remember you only have to dispose the topmost control – so if you’re swapping in and out a panel – disposing the panel disposes it’s child controls.

Dispose is not just for controls though – Brushes, Pens, and Fonts implement IDisposable as well. These hold onto GDI objects, not window handles. If you leak these objects, it can cause quite a perf problem in your application as System.Drawing may start to GC extra times to get the number of handles back in check. SystemPens and SystemBrushes do not need to be disposed – these are cached objects. SystemFonts do as they are a live fetch from the OS of the current MenuFont etc.

However, if you are creating a new control that holds onto its own IDisposable object (e.g. it’s own tooltip), that control should override Dispose(bool disposing) and add calls to Dispose() for these objects.

If you are concerned about a leak in your code – bring up task manager, switch to processes. View->Select Columns and tick off USER objects and GDI objects. This should show you the live count of how many handles you have out.

In summary

Modeless Form (Application.Run(new Form)) - NO
SystemPens, SystemBrushes - NO

Modal Form (Form.ShowDialog) - YES
Dynamic panel that’s swapped in and out - YES
Pens, Brushes, Fonts, Graphics objects - YES
Regions, other advanced System.Drawing objects - YES
SystemFonts (these are not cached) - YES
Timers, Tooltips, other components - YES

Next time: interesting uses of dispose

 

Dispose - Part 4
Cool things you can do with dispose

Now that we're all convinced that using Dispose is the greatest thing next to sliced bread, lets look at some syntatic sugar in C# that makes life just that much better.

If you are thinking of creating an IDisposable object, then disposing it within the same function, the conscientious thing to do is something like this:

Graphics g = Graphics(this.Handle);
try {
   g.DrawRectangle(..);
}finally {
   if (g != null) {
      g.Dispose();
   }
}

It turns out that C# already has a keyword for this - the using statement. The same thing can be expressed as such:

using (Graphics g = Graphics.FromHwnd(this.Handle)) {
   g.DrawRectangle(..);
}

It turns out this syntax is also great for defining things like transactions...

using (new MyTransaction()) { ... }

where the MyTransaction class or struct implements IDisposable. If this is the common usage of MyTransaction, consider making it a struct instead of a class so your object will be allocated on the stack, not the heap.

 

Links:

IDisposable.Dispose remarks section

Performance Considerations for Run-Time Technologies in the .NET Framework

Writing High-Performance Managed Applications : A Primer

Implementing Finalize and Dispose To Clean Up Unmanaged Resources

C# Language Specification: The using statement

 

Please note: The following article was extract from http://www.windowsforms.net

SQL Server Tips and Tricks

http://gatekiller.co.uk/Post/SQL_Server_Tips_and_Tricks

SQL Hacks for Microsoft SQL Server

http://www.sqlhacks.com/

.

Microsoft SQL Server 2005 Database Encryption Step-by-Step

Microsoft SQL Server 2005 Database Encryption Step-by-Step

This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption methodologies.
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)

Creating Master Key

Before using encryption algoritms in SQL Server 2005 and SQL Server 2008, a master key should be created in the database where encryption is going to be used.
Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.

SELECT * FROM sys.symmetric_keys

If there has been created a master key, you will see a result that is similar to below if you are running the t-sql select from sys.symmetric_keys view in MS SQL Server 2005,

Note that in SQL2008 (CTP6 or February CTP) sys.symmetric_keys view has additional columns:

name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
key_thumbprint
provider_type
cryptographic_provider_guid
cryptographic_provider_algid

It is important that for a database in MS SQL Server, there can be only one master key in other words a single master key can be created on a database. A second master key can not be created in a sql server database.

You can use the below t-sql script code in order to create a master key in the sql database.

/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
GO

It is important that you keep the encryption password in a safe place or keep backups of your sql server database master key.
I'm going to deal with master key backup later in this tutorial.

You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.

If you run the DROP MASTER KEY sql server statement after a certificate is defined which we will see in the next step, the following error message is going to be informing the sql server programmer about the dependent certificate.

Msg 15580, Level 16, State 1, Line 2
Cannot drop master key because certificate 'PasswordFieldCertificate' is encrypted by it.

SQL Server server Script which will shrink all databases

DECLARE @cmd1 varchar(2000),

@cmd2 varchar(2000)

SET @cmd1 = 'BACKUP LOG [?] WITH NO_Log'

SET @cmd2 = 'DBCC SHRINKDATABASE ([?],TRUNCATEONLY)'

EXEC sp_msforeachdb @command1 = @cmd1,

@command2 = @cmd2

SQL SERVER TIPS

------------------------------------------------------------------------------------
DELETE failed because the following SET options have
incorrect settings: 'ARITHABORT'.
ALTER DATABASE WEBPLIVE1 SET ARITHABORT ON
------------------------------------------------------------------------------------
shrink db SQL SERVER 2005
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
------------------------------------------------------------------------------------

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.
----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.mdf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups

WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO

'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',

MOVE 'AdventureWorks_Log'

TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf'

RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH RECOVERY

DBCC CHECKTABLE ("DILMAH.HS_PR_DAILYPROCTXNS");

--DELETE FROM HS_PR_TXN_ELIGIBILITY WHERE TRN_DTL_CODE = 'DEATJOIN'
DBCC CHECKDB (DILMAH, NOINDEX);
DBCC CHECKDB (DILMAH, REPAIR_ALLOW_DATA_LOSS);

DBCC CHECKDB (DILMAH, REPAIR_ALLOW_DATA_LOSS);

alter database DILMAH set SINGLE_USER

EXEC sp_dboption 'Works', 'single user', 'false'

BACKUP DATABASE ire TO DISK = 'C:\test\test.BAK'

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

To get actual server property

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

To recreate all db index in SQL SERVER

SELECT 'DBCC DBREINDEX ("'+ name+'", " ", 70); ' FROM dbo.sysobjects WHERE xtype='u'