Recently, one of my friends (new to SQL Server) was attempting to the deploy the AdventureWorks2012 versions of the sample databases available for download from the CodePlex community on his laptop for study purposes, but was facing an error and hence called me up.
When I reached to his place, he told me that he had downloaded the AdventureWorks2012 database from the following website: http://msftdbprodsamples.codeplex.com/releases/view/55330#DownloadId=165399
When attempting to restore the database, he was facing the following error:
A quick glance at the screenshot shows us the error -
Unable to open the physical file "E:\SQL Databases\AdventureWorks\AdventureWorks_Log.ldf". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)
Clearly the error is because the sample databases available on the CodePlex website are just the data files. A transaction log file is not supplied by CodePlex. To restore a database that comes without a data file, one can use the option to rebuild the transaction log when running the CREATE DATABASE command. For example:
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
You can also find this script with a lot of other SQL Server, C# and XML scripts at the Scripts Module on BeyondRelational.com: http://beyondrelational.com/modules/30/scripts.aspx?s=stream&tab=scripts
Until we meet next time,
Be courteous. Drive responsibly.