How to attach a data file MDF without log file (LDF)

USE master
go
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\MSSQL\Data\AdventureWorks2008R2_Data.mdf') -- change the drive and file path
FOR ATTACH_REBUILD_LOG ;

Find Drive size and free space

-- This query will run SQL Server 2008 R2 only
SELECT distinct vs.volume_mount_point AS DriveName,
vs.total_bytes/1073741824 as TotalSize_GB,
vs.available_bytes/1073741824 AS FreeSpace_GB,
(vs.total_bytes/1073741824) -
(vs.available_bytes/1073741824) AS SpaceUsed_GB,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,3)) * 100 AS [SpaceUsed %]
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point

SQL SERVER 2012 available now

The new vesion of SQL Server 2012 is released today.
http://www.microsoft.com/sqlserver/en/us/default.aspx

You can download evaluation copy from
http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

How to insert rows into table with identity column

Imagine you have  two billion rows on employee table, you have to archive this table. After archiving you are going to insert back only 110 million rows to employee table from employee_temp, There is a column emp_id is identity, so how can you insert data back from archived tables.  Here you can find the tested solution , first you need to disable an indentity column to auto insert.

Step 1: Make sure to stop all process(application connection) against employee table
Step2 :Create a new table employee_copy from employee table
           Find max(emp_id) from employee eg: 2000000001and  RESEED this value to employee_copy
           DBCC CHECKIDENT (employee_copy , RESEED, 2000000001)
Step3:  Rename the orginal table using sp_rename 'employee' , 'employee_temp'
Step4: Rename the new table using sp_rename 'employee_copy' , 'employee'
Step5:  Insert the rows from employee_temp table to employee table

SET IDENTITY_INSERT employee ON
GO
INSERT INTO employee (emp_id, emp_name, country_code)
SELECT emp_id, emp_name, country_code from employee_temp(nolock) where emp_id > 110000000
GO
SET IDENTITY_INSERT employee OFF
GO