[T-SQL] What to do , if mdf file get corrupted

Today one of my old friend asked help that he has got mdf file corrupted so what should he do.

So normally there are different excersies which one can do if he knows about the structure of sql server but i am suggesting two tool which you can download and get the task done.:

http://www.mdfrepair.com/
http://www.nucleustechnologies.com/sql-recovery.html

Download it and there are simple steps what you can do to get your db repaired.

Different versions of SQL Server 2008

Choosing the right SQL Server 2008 Edition is something that requires a bit of a research. The first step is of course to become familiar with the available options. We have seen this question pop up a lot of times at client sites as well as in newsgroups and the e-mails that we get from the readers. Microsoft already has very good, concise and to the point information on the different editions, their capabilities as well as a comparison matrix between Enterprise & Standard Edition as well as a comparison matrix between Compact & Express Edition. There are a lot of choices available for you and depending upon your needs, you can choose from (all the details on these can be obtained from the link above):

•Enterprise Edition
•Standard Edition
•Workgroup Edition
•Web Edition
•Compact Edition
•Express Edition
◦SQL Server 2008 Express Edition.
◦SQL Server 2008 Express with Tools.
◦SQL Server 2008 Express with Advanced Tools.

Full comparison sheet link is given below:

http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf

Comparison between prices of SQL Server and Oracle

SQL Server is a feature-rich and economical choice compared to Oracle. The base product of Oracle is expensive and to add all the features that are offered by the SQL Server, it requires many more different add-ons. These extra add-ons further increase the price to make SQL Server much more affordable than Oracle, which is ridiculously expensive.

I suggest that instead of me taking the side of SQL Server, you should compare the prices of the products yourself. The prices that are listed here are as per the process.

SQL Server Standard: $7,171
SQL Server Enterprise: $27,495

Oracle Standard One: $5,800
Oracle Standard: $17,500
Oracle Enterprise: $47,500

Additionally, in case of Oracle, you will have to purchase many additional features which are already available at NO COST in SQL Server Enterprise Edition. For example, in the SQL Server Enterprise edition, partitioning is a part of the package, whereas in the case of Oracle, you will have to pay additional 11,500 per processor along with the enterprise edition purchase. If you have a four-socket CPU, you end up paying almost the same price as Oracle Enterprise. Again this is just one feature we talked about. There are many additional add-ons available for Oracle, which are free in the SQL Server Enterprise or Standard edition (e.g. BI Tools).

*Prices listed are taken from the official pdf that were available on both companies website on july-2010.

[T-SQL] How to create new error log file

There are total two methods to do this:

1. Restart the server
2. EXEC sp_cycle_errorlog

First option is not a suitable one as we restart the server every time.

Hope this post is helpful to many of the dba's.

[T-SQL] Drop Database - Common Error

Normally when you try to drop the database , you get common error that its current in use. So here is proper script for how you should drop the database. But please make sure what you know what you are doing , as dropping database on Production can be a big issue.

USE MASTER
GO
ALTER DATABASE Mabbas_DB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Mabbas_DB
GO

[T-SQL] Last Backup time for all DBs

One more script to share

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

[T-SQL] DB Backup History

Today i was visiting one blog(Pinal Dave) and found a very interesting query for getting database backup history.

SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

[T-SQL] Delete duplicate without ID

Here is a query by which you can delete duplicate rows without using identity column

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

[T-SQL] Union vs. Union All

Now a days , i am taking interview for the post of DWH Engineer and a common question that i am asking is the above one , but i dont know why people dont know this.

So here is the basic definition:

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

If we talk about the performance , then it should be known that Union All is much faster then Union as Union involves distinct clause which itself is a heavy operation.

[T-SQL] Find Busiest Database(s)

DMV sys.dm_exec_query_stats contained columns total_logical_reads, total_logical_writes, sql_handle. Column sql_handle can help to to determine the original query by CROSS JOINing DMF sys.dm_exec_sql_text. From DMF sys.dm_exec_sql_text Database ID and from Database ID can be figured out very quickly.

SELECT SUM(deqs.total_logical_reads) TotalPageReads,
SUM(deqs.total_logical_writes) TotalPageWrites,
CASE
WHEN DB_NAME(dest.dbid) IS NULL THEN 'AdhocSQL'
ELSE DB_NAME(dest.dbid) END Databasename
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
GROUP BY DB_NAME(dest.dbid)

[T-SQL] Taking backup at two places same time

If you want to take backup of any DB at two places same time then following tsql need to be executed.

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
MIRROR TO DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO

Dont use the below tsql as it would break your db backup into two file and it would not serve the purpose. I mean you would be needing both of these files when you are restoring the database.

BACKUP DATABASE [AdventureWorks] TO
DISK = N'D:\AdventureWorks.bak'
, DISK = N'E:\AdventureWorks.bak'
WITH FORMAT, INIT,
NAME = N'AdventureWorks-Full Database Backup'
GO