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 (http://blog.SQLAuthority.com) ,BOL

About these ads

39 thoughts on “SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

  1. Additional Note:
    Once you have used REPLACE Syntax you will not able to add/restore any other transaction or differential blog after that. However another full back will work just normal.

    • Hi Pinal
      I am receiving below error while restoring the database from backup file. I am using SQL 2008 R2 SP2 for this restoration-

      Location: bckioreq.cpp:1072
      Expression: 0
      SPID: 55
      Process ID: 1976
      Msg 3013, Level 16, State 1, Line 1
      RESTORE DATABASE is terminating abnormally.
      Msg 3624, Level 20, State 1, Line 1
      A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

      Any idea how to fix this.

  2. Pingback: SQL SERVER - FIX : Error 3154: The backup set holds a backup of a database other than the existing database Journey to SQL Authority with Pinal Dave

  3. Hi Pinal, I have a LOG that is too big, I made a back up only to the database for restoring only this, bus now I have the error “the backup set holds a backup of a database other than the existing “inventario”…..

    what can I do???

  4. Hi Pinal,

    I am trying to restore full backup writing following commands:

    RESTORE DATABASE [Backup] FROM
    DISK = N’ XXX_backup_200803311932.bak’
    WITH FILE = 1,
    MOVE N’XXX_Test’
    TO N’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Backup.mdf’,
    MOVE N’XXX_Test_log’
    TO N’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Backup_log.ldf’,
    NORECOVERY, NOUNLOAD, STATS = 10
    GO

    But, i am getting the error:

    The tail of the log for the database “Backup” 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.

    The same code is working fine if i am using RECOVERY mode. But in order to restore differential backup i am trying out the full backup restore with NORECOVERY.

    Please help me with this.

  5. Very useful article and great blog … saved me a lot of time and far more easier that trawling through MS documentation for my point problem.

    Thought I might add that if you don’t know the names to add to the MOVE command, RESTORE FILELISTONLY can give you that information, Eg:

    RESTORE FILELISTONLY FROM DISK=’C:\BackupAdventureworks.bak’

    regards,
    Kevin

  6. Hi, if anyone can help me I’d appreciate it. I’m trying to restore a database from SQL 2005 (probably Enterprise, but I don’t know) to SQL Express. One of the files is giving me trouble. The error message is

    The file “sysft_mydb001″ failed to initialize correctly

    in my data files I don’t even have sysft files. Is this an incompatability of SQL Express?

    Thanks for your blog!
    Daniel

  7. Dear Sir,

    You have answered so many quirky questions for me. Thank you very much. When researching this last question, when I saw your picture start to load I felt relieved because I knew there was an excellent chance that the answer would be forthcomming from this page.

    Thanks again!
    Don Roquerre

  8. Hi Pinal,

    I had a server with sql express 2005 which crashed recently
    i have a backup with file without any extension

    now i want to use it and restore a database using the backup file in a new server with sql 2008 in it.

    How can i do it..?

    can you give me some solution?

    Thanks.
    Praveen

  9. Hi Pinal,

    This is an excellent site, and your answers to diff queries are
    perfect.

    I easily find a solution everytime I log on.

    Thank you!

    Ujwala

  10. I am getting an SQL error code 3007 when trying to back up a database “13Software” on my failover SQL Server. The running DB was populated by restoring the production DB “13Software, from a server with a different name. does the error message “The backup of the file or filegroup “sysft_Name” is not permitted because it is not online.” mean anything relating to the differently named servers, or the catalog?

    Tim

  11. Hello Pinal

    I’m trying to establish Mirroringand due to the reason I wanted to take the full,differential and t-log backup of the source DB.I used the following queries to take the backups:

    backup database sourav to disk = ‘c:\backup\sourav_full.bak’

    BACKUP DATABASE sourav
    TO DISK =’c:\backup\sourav_diff.bak’
    –MIRROR TO DISK =’D:\DBBackup\Products_Mirror_Set_2.bak’
    –MIRROR TO DISK =’E:\DBBackup\Products_Mirror_Set_3.bak’
    WITH DIFFERENTIAL

    backup log sourav to disk = ‘c:\backup\sourav_log.bak’

    Now, while restoring the database I am getting the message.
    what should be the correct syntax? I want to restore the backuped up files to the mirror server location C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data) using ‘no-recovery’ option which is crutial for the database mirroring.

    Pls assist.

    Regards,
    Sourav

  12. Hi pinal

    I restored database but after two days database be in recovery (can not open database and delete)

    I want to convert database to norecovery

    please , help me

  13. Hello Aboazam,

    Was the restore process completed successfully and verify that you havn’t used the NORECOVERY option? If not, and its taking more than expected time than verify that the restore process is not listed in SP_WHO or sys.dm_exec_requests. Verify the backup file using RESTORE VERIFYONLY command and restore the database again.

    Regards,
    Pinal Dave

  14. Hi pinal,

    RESTORE FILELISTONLY
    FROM DISK = ‘\\xyz\ E:\BACKUP\Steps_Final.BAK’

    these command print the information of .bak file but i wants to receive logical name of .bak file in variables.

    please give me any solution

    Thanks
    rohit

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

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

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

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

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

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

  21. 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?

  22. 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 :-)

Leave a Reply

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