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 DatabasesAdventureWorksAdventureWorks_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 DatabasesAdventureWorksAdventureWorks_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.
SQLTwins by Nakul Vachhrajani 
Pingback: #0360 – SQL Server – sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified) | SQLTwins by Nakul Vachhrajani
Pingback: #0360 – SQL Server – sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified) - SQL Server - SQL Server - Toad World
Pingback: #0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases | SQLTwins by Nakul Vachhrajani
Hi Nakul,
Great Post!!
But I would like to correct the below mentioned point.
–> This option is only available for read/write databases
This is not true, we can also use this option for Read-only databases as well. I have tested this by making a database read-only and detached it. Then attached using create database For Attach_Rebuild_log option and it was successfully added and database made read/write post that.
Regards,
Kranthi
LikeLike
Thanks for the feedback, Kranthi!
I agree that this is a bug with the MSDN documentation.
Did you use more than one file group in your test? Please note that the reason one cannot use ATTACH_REBUILD_LOG with a read-only database is because the primary filegroup is read-only. And, it’s not that straight forward to have a fully read-only database. Please refer my post for more details: https://nakulvachhrajani.com/2016/01/18/0370-sql-server-myths-create-database-for-attach_rebuild_log-will-not-work-for-read-only-databases/
What appears to be happening is that when the log is rebuilt, the DB becomes read/write and ceases to be read-only. So, although the MSDN documentation is right technically, it is wrong for all practical purposes
Do keep reading and sharing your feedback!
LikeLike
Hi Nakul,
Many Thanks for the details and explanation.
I tested with only one file group.
If you don’t mind I would like to ask one more question based on your answer 🙂
You mentioned that,
Please note that the reason one cannot use ATTACH_REBUILD_LOG with a read-only database is because the primary filegroup is read-only,
You mean to say when database is changed to Read-Only, the Primary Filegroup will also change to Read-Only?
Sorry If I am bugging you with my questions.
Regards,
Kranthi
LikeLike