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'