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
41 Comments. Leave new
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.
Hi pinal ,
I have a database in sql server 2000.i want to create a backup file for it How i can.
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???
Worked like a charm. Good tech article and nice website!
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.
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
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
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
hi,
When i restoreing database there are 3 files creating.
1). ldf
2). Log
3) Company File
While i am restoring this file from c#
giving error.
Can u tell me the solution.
Thanks a bunch this worked great for me.
Abdul
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
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
Thanks Pinaldave
Totally what God has been speaking to me today. ,
You should always use these header files in pairs. ,
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
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
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
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
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