SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

While moving some of the script from SQL SERVER 2000 to SQL SERVER 2005 our migration team faced following error.

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database “AdventureWorks” has not been backed up.
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose.
Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Following is the similar script using AdventureWorks samples database as example. This scripts works perfectly fine with SQL SERVER 2000. It gives the error in SQL SERVER 2005.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data',
MOVE 'AdventureWorks_Log' TO 'C:\Data'

The reason of error is already explained in the error detail. Requirement of backing up tail of the log for the database can be overridden by using RESTORE command. Most of the WITH clause statements can be used in combination with the others. Change the syntax of above script with addition of REPLACE in WITH clause.

ALTER DATABASE
AdventureWorks
SET SINGLE_USER WITH
ROLLBACK
IMMEDIATE

RESTORE DATABASE AdventureWorks
FROM DISK = 'C\:BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
REPLACE

Reference : Pinal Dave (https://blog.sqlauthority.com) ,BOL

, , ,
Previous Post
SQL SERVER – 2005 Security DataSheet
Next Post
SQL SERVER – Collate – Case Sensitive SQL Query Search

Related Posts

40 Comments. Leave new

  • Hi Pinal,

    When I executed maintenance plan which takes the backup of databases I am encountered with following error.

    Msg 3007, Level 16, State 1, Line 1
    The backup of the file or filegroup “sysft_TeamFoundationServer10FullTextCatalog” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

    Can you please help me out.

    Thanks in advance!!

    Santosh

    Reply
  • Hi Pinal,
    Thanks a lot. Worked like a charm

    Regards,
    Prashanth

    Reply
  • Thanks Mr. Pinal. Your solution is very helpful. My problem has been solved.

    Reply
  • hello Pinal,

    I had created my db using SQL Server 2005 express, now i need to work on SQL Server 2008, but I am unable to restore my bd as it is showing Error 3154: The backup set holds a backup of a database other than the existing database. I have unstalled SQL Server 2005 express from my system.

    Can you please help me out.

    Thanks in advance…

    Regards,
    karuna
    Karuna

    Reply
  • facebook comments
    May 18, 2011 9:22 am

    I am also want to restore the database,but cant be able to do so.
    Even I could not know in which SQl server the database is created.
    I am working on the maintenance project.

    Please help pinal.

    Thanks,
    Nimish Patel

    Reply
  • mordhwaj chauhan
    June 1, 2011 1:20 pm

    Hi Pinal,

    I got this error when i used this query “.bak’ is incorrectly formed. sql server cannot process this media family”.

    plz help me out of this

    Thanks & Regards
    Mordhwaj Chauhan

    Reply
  • Dear Sir

    I took backup my database from sql server 2008 r2 but forgot to give extension as .bak into wizard. Backup of database is successfully but when i tried to restore by wizard or by command (with replace) it gives me error. Can you suggest me how to restore database if i forgot .bak extension

    Reply
  • Great..thanks for solution bro, its works

    Reply
  • Good one, saved time. Thank you Pinal !

    Reply
  • hello pinal,
    when I create database from mdf file i got following error
    CREATE DATABASE Database1 ON
    (FILENAME = N’C:\AdventureWorksDW2008R2_Data.mdf’)
    FOR ATTACH_REBUILD_LOG

    Error:CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\AdventureWorksDW2008R2_Data_log.LDF’.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘Database1’. CREATE DATABASE is aborted.

    plz help me.
    Thanks & Regards
    sandip

    Reply
  • sir it gives error i use this
    RESTORE DATABASE dbProductDevelopment
    FROM DISK = ‘D:\dbProductDevelopment_log.ldf’
    WITH REPLACE

    error:
    Msg 3102, Level 16, State 1, Line 2
    RESTORE cannot process database ‘dbProductDevelopment’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    What to do?

    Reply
  • this works for me.There is a small typo in FROM DISK = ‘C\:BackupAdventureworks.bak'[‘C:\BackupAdventureworks.bak’ is required],held me for some time :-)

    Reply
  • THANKS DEAR.

    Reply
  • İsmail Manavoğlu
    September 18, 2014 8:38 pm

    Try this;
    Restore > Database option, not File and FileGroups

    Reply
  • Bilal-İstanbul (@ulubil)
    November 24, 2014 3:20 pm

    Database>Restore Database>to Database (Database Name)>from Device (XXrestore_DB.bak)
    -don’t forget WITH REPLACE- and OK …. restore success

    Good Works…

    Reply
  • Thank you – that worked for me.

    Reply
  • I get the following error when I tried to restore a database following your instruction, any suggestion to fix the issue?
    Msg 3634, Level 16, State 1, Line 56
    The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER_2014\MSSQL\DolphinPlatform_log.ldf’.
    Msg 3156, Level 16, State 5, Line 56
    File ‘Ortho_Log’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER_2014\MSSQL\DolphinPlatform_log.ldf’. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 56
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 56
    RESTORE DATABASE is terminating abnormally.

    Reply

Leave a Reply

Menu