To attach a database with only a mdf file. (This will create new log file)
USE [master]
GO
CREATE DATABASE [Test] ON
( FILENAME = N'D:\Database\2008 Database\Test.mdf' )
FOR ATTACH
GO
To attach a database with mdf and ldf files.
USE [master]
GO
CREATE DATABASE [HandWashDW] ON
( FILENAME = N'D:\Database\2008 Database\HandWashDW.mdf' ),
( FILENAME = N'D:\Database\2008 Database\HandWashDW_log.ldf' )
FOR ATTACH
GO
To shrink the log file in SQL Server 2005
USE myDatabase
GO
DBCC SHRINKFILE(myDatabase_log, 1)
BACKUP LOG myDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDatabase_log, 1)
To shrink the logs in 2008, use the following
USE HandWashDW
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )
-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )
-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )
