#0176 – SQL Server – Creating a database without the log backup file – Error Msg. 5120


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:

Attach Database Wizard showing an error arising out of a missing log file

Error message #5120

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.

Advertisements

6 thoughts on “#0176 – SQL Server – Creating a database without the log backup file – Error Msg. 5120

  1. 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

  2. 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

  3. Pingback: #0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases | SQLTwins by Nakul Vachhrajani

  4. Kranthi

    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

    Like

    Reply
    1. nakulvachhrajani Post author

      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!

      Like

      Reply
  5. Kranthi

    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

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s