Saturday, 16 February 2013

Create db when only ldf are available

--Ever had a situation where you had to attach a database when all you had were the data files? 
--Perhaps when you downloaded the AdventureWorks sample databases from CodePlex? Here's a script to attach a database with just the data files using CREATE DATABASE....
--ATTACH_REBUILD_LOG statement

USE [master]
GO
 
CREATE DATABASE [AdventureWorks]
ON  PRIMARY ( NAME = N'AdventureWorks_Data',
FILENAME = N'E:\SQL Databases\AdventureWorks\AdventureWorks_Data.mdf',
SIZE = 3072KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
FOR ATTACH_REBUILD_LOG
GO



--Some finer points about ATTACH_REBUILD_LOG:

--When rebuilding the transaction log, a collation name cannot be specified to change the collation of the database
--This option is only available for read/write databases
--Because the transaction log file is being rebuilt, we do not have control over the location, initial size and file growth increment values. The new log file will be created at the default log path location and will have a size of 1MB
--Naturally, this operation breaks the log chain

No comments:

Post a Comment