Go to Main page..

Database recovery from transaction log (Microsoft SQL Server





Restore database from transaction log [new]
Good time, dear forum users.
By necessity, it became necessary to restore the database from the backup copy as of December 17. All is well, backups are being made, took up the restoration. Used MSSQL Studio. It seems like everything is simple. We create a new database, click the right button on the required database, Restore, specify the parent database as the source, and test it as the result. In the time interval - choose the desired date and forth.
And here at this stage miracles begin. The base source rises to "Restore from a copy". Nothing happens with the test base.
After some time, I discovered that backups from SQL were not performed due to transferring a virtual machine with 1C server and MSSQL to another RAID controller, and consequently I was scolding about changing the cluster size on the backup device. As a result, there are only backup copies of the database from 06/16/2018 and a backup of the transaction log file, 1.6 GB in size.
-
Attention question: Is there at least a theoretical possibility based on the available data - to restore the data lost in 3 months?
-
I am completely new to MSSQL issues, and I understand that I’ve got somewhere. The question is exactly where and how to avoid a repetition of such situations in the future? I redid the backup system, recreated the backup devices and performed the test task of backing up the databases. Now everything is working fine. I really need your help!
Re: Restore database from transaction log [new]
Siver

What is the result with backups? "All is well, are backups done" or have SQL backups not been performed?

Did you want to restore backup to a new database with a different name? There you need to specify the recovery with the movement, the new file names. I noticed a feature of SSMS: they began to restore to the test database, they chose backup from disk - the name of the rewritable database changed to the one that was in backup. It needs to be traced and corrected back, otherwise with the overwriting enabled you can lose the original one.

The initial base in what state, the case is not according to the scheme from my last item went?
Re: Restore database from transaction log [new]
Danion,

I was sure that backups are being made. since backups are carried out in 2 stages, the first is the database backup itself to the local disk, the second, this third-party software retrieves data from the local disk, stuffs it into an encrypted archive and puts it on the network ball. Here is the second stage - it worked without failures, creating archives with dates on the ball, everything is as it should be. The problem is that in all these archives information from 16-08-2017. That is, the first stage as it stopped on August 16, so this infa multiplied. And I didn't have enough brains to check the contents of the archive.
-
Re: Restore database from transaction log [new]
Execute
 headeronly from disk = резервная копии базы есть только от 16 - 06 - 2018 restore headeronly from disk = бэкап файла журнала транзакций restore headeronly from disk = backup database is only from 16 - 06 - 2018 restore headeronly from disk = backup of the transaction log file 

And show FirstLSN and LastLSN from the result sets.
Re: Restore database from transaction log [new]
Siver
As a result, there are only backup copies of the database from 06/16/2018 and a backup of the transaction log file, 1.6 GB in size.
That is, there is no journal bucks either? New is not done, or old erased?
Re: Restore database from transaction log [new]
alexeyvg,

forgive me my ignorance - but I do not know =) there is only what I have indicated above, namely, a backup copy of the database from 16-08 and a backup copy of the transaction log of the same database from 24-12 this year.
Re: Restore database from transaction log [new]
invm,

now at home and unable to connect to the server by remote. Tomorrow I'll do it right in the morning. I would be very grateful if you tell me right away exactly where to do this?
Re: Restore database from transaction log [new]
Siver
I would be very grateful if you tell me right away exactly where to do this?
In Management Studio.
Re: Restore database from transaction log [new]
invm,

I understood that Management Studio is being used. I have 16 bases there. Do I need to click on the desired database and select "create request"? or "Tasks" - "Create a backup copy" and there separately create another backup of the old database and reuse the same menu, but create a backup of the transaction file?
Re: Restore database from transaction log [new]
I have 2 halyards
base - backup_UZBI.bak (1 205 397 KB) dated 16 08 2018
Journal - UZBI_LogBackup_2018_12_24_21_10_23.bak (1 675 642 KB) 24 12 2018
-
in SQL itself, there is now a UZBI base which is deployed from the file base. When connecting to 1C in this database there is the last entry from 08.08 2018.
-
I really just ran into MSSQL in general and with similar problems in particular. I am willing to pay for the solution if it is and provide remote access. I'm just afraid that my competence is zero - not enough even to follow your advice. I'm not lazy, though, I just do not have time from the word at all. Three working days left ...
Re: Restore database from transaction log [new]
Siver

in the studio, click the New Query button and execute the commands that the invm asked for ("And show FirstLSN and LastLSN from the result sets.")

 headeronly from disk = '...\backup_UZBI.bak' restore headeronly from disk = '... \ backup_UZBI.bak'
 headeronly from disk = '...\UZBI_LogBackup_2018_12_24_21_10_23.bak' restore headeronly from disk = '... \ UZBI_LogBackup_2018_12_24_21_10_23.bak'


replace three points with the full path to the file
Re: Restore database from transaction log [new]
Komrad,

Thank you very much!!!
-
for the database - FirstLSN 224000002606800131, LastLSN 224000002613100001
for the log file FirstLSN 194000002252800289, LastLSN 247000008034300001
Re: Restore database from transaction log [new]
Siver
for the database - FirstLSN 224000002606800131, LastLSN 224000002613100001
for the log file FirstLSN 194000002252800289, LastLSN 247000008034300001
You can restore the database and roll backup log.
Only first on the cats practice.
Re: Restore database from transaction log [new]
invm,

I will be grateful to you, if you explain on my fingers how to do it! Because I am not able ... skills and knowledge is not enough
Re: Restore database from transaction log [new]
All that I could find on the Internet - I did not help to create an empty database and roll back the backup database first - then the remaining transaction file, and received a message about the inability to recover data because the path was not agreed or something like "missing files for rolling"
Re: Restore database from transaction log [new]
All that I could find on the Internet - I did not help to create an empty database and roll back the backup database first - then the remaining transaction file, and received a message about the inability to recover data because the path was not agreed or something like "missing files for rolling"
Re: Restore database from transaction log [new]
Restore to the current database
 master ; use master ;
 database MyDB set single_user with rollback immediate ; alter database MyDB set single_user with rollback immediate ;

 database MyDB from disk = 'полный бекап' with replace , norecovery ; restore database MyDB from disk = 'full backup' with replace , norecovery ;
 log MyDB from disk = 'бекап журнала' with recovery ; restore log MyDB from disk = 'backup log' with recovery ;


Restore to new database
 database MyNewDB from disk = 'полный бекап' with restore database MyNewDB from disk = 'full backup' with
  'Логическое имя1' to 'Новое расположение файла1' , move 'Logical name1' to 'New location of file1' ,
  ...
  'Логическое имяN' to 'Новое расположение файлаN' , move 'Logical nameN' to 'New location of fileN' ,
  ; norecovery ;
 log MyNewDB from disk = 'бекап журнала' with recovery ; restore log MyNewDB from disk = 'backup log' with recovery ;
The correspondence of logical names to files can be found in the result.
 filelistonly from disk = 'полный бекап' ; restore filelistonly from disk = 'full backup' ;


Before you do something, review the documentation - https://docs.microsoft.com/ru-ru/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view = sql-server-2017
Re: Restore database from transaction log [new]
invm,

Follow your instructions as follows:
1. created a base TEST
2. run the view script
+
DBCC CHECKDB
restore database TEST from disk = 'G: \ backup \ 1111 \ 1 \ backup_UZBI.bak' with
move 'F: \ 1c \ UZBI.mdf' to 'G: \ backup \ 1111 \ 1 \ UZBI.mdf',
move 'D: \ logs \ UZBI_log.ldf' to 'G: \ backup \ 1111 \ 1 \ UZBI_log.ldf',
norecovery;
restore log TEST from disk = 'G: \ backup \ 1111 \ 1 \ uzbi.bak' with recovery;

where is the path G: \ backup \ 1111 \ 1 \ - a temporary storage in which the full copy files and the log file are located
restore filelistonly from disk = 'G: \ backup \ 1111 \ 1 \ backup_UZBI.bak'; produced the following result
+
LogicalName PhysicalName
UZBI F: \ 1c \ UZBI.mdf
UZBI_log D: \ logs \ UZBI_log.ldf

as a result, the recovery to the new database took about 20 seconds and gave the result
+
DBCC results for "UZBI".
CHECKDB detected 0 allocation errors and 0 consistency errors in the "UZBI" database.
DBCC execution completed. If the DBCC gives you error messages, contact your system administrator.
The message 3154, level 16, state 4, line 2
The backup data set contains a copy of a database other than the existing "TEST" database.
The message 3013, level 16, state 1, line 2
RESTORE DATABASE aborted with error.
The message 3154, level 16, state 4, line 6
The backup data set contains a copy of a database other than the existing "TEST" database.
The message 3013, level 16, state 1, line 6
RESTORE LOG was interrupted with an error.

-
where to dig further? or what did I do wrong?
-
Many thanks for the link, be sure to read it!
Re: Restore database from transaction log [new]
invm,

attempt to execute a query view
+
use master;
alter database UZBI set single_user with rollback immediate;

restore database UZBI from disk = 'G: \ backup \ 1111 \ 1 \ backup_UZBI.bak' with replace, norecovery;
restore log UZBI from disk = 'G: \ backup \ 1111 \ 1 \ UZBI.bak' with recovery;

led to the result
+
Unspecified transactions are rolled back. Pre-rollback: 0%.
Unspecified transactions are rolled back. Pre-rollback: 100%.
151384 pages are processed for database "UZBI", file "UZBI" for file 1.
Processed 4 pages for database "UZBI", file "UZBI_log" for file 1.
RESTORE DATABASE successfully processed 151388 pages in 15.211 seconds (77.754 MB / s).
Message 4330, Level 16, State 1, Line 5
The backup dataset cannot be applied because it is on a recovery path that is not consistent with the database. The recovery path is a sequence of data and backup logs, passing through which the database reaches a specific recovery point. Find a compatible backup for recovery or restore the rest of the database to match the recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
The message 3013, level 16, state 1, line 5
RESTORE LOG was interrupted with an error.
Re: Restore database from transaction log [new]
Siver

Restor with move you curve.
In your example:
 database TEST from disk = 'G:\backup\1111\1\backup_UZBI.bak' with restore database TEST from disk = 'G: \ backup \ 1111 \ 1 \ backup_UZBI.bak' with
 'UZBI' to 'G:\backup\1111\1\UZBI.mdf' , move 'UZBI' to 'G: \ backup \ 1111 \ 1 \ UZBI.mdf' ,
 'UZBI_log' to 'G:\backup\1111\1\UZBI_log.ldf' , move 'UZBI_log' to 'G: \ backup \ 1111 \ 1 \ UZBI_log.ldf' ,
 ; norecovery ;
 log TEST from disk = 'G:\backup\1111\1\uzbi.bak' with recovery ; restore log TEST from disk = 'G: \ backup \ 1111 \ 1 \ uzbi.bak' with recovery ;


There are no UZBI.mdf and UZBI_log.ldf files in the path G: \ backup \ 1111 \ 1 \ right now?

After move, you need to specify the logical name of the file from backup, and not the physical mdf \ ldf.

Attached file Size - 42Kb
Re: Restore database from transaction log [new]
Danion,

Thank you for your help. As a result of the request you have written, the answer is:
+
The message 3154, level 16, state 4, line 2
The backup data set contains a copy of a database other than the existing database "UZBI".
The message 3013, level 16, state 1, line 2
RESTORE DATABASE aborted with error.
The message 3154, level 16, state 4, line 6
The backup data set contains a copy of a database other than the existing database "UZBI".
The message 3013, level 16, state 1, line 6
RESTORE LOG was interrupted with an error.
Re: Restore database from transaction log [new]
Siver

A similar error if trying to overwrite existing files.

Before that I asked:
"There are no UZBI.mdf and UZBI_log.ldf files on the path G: \ backup \ 1111 \ 1 \ right now?"
For overwriting, replace is used, but you can overwrite something you need ...

Try this option. And do not overwrite the current one and it is unlikely that there is a database \ TEST1234 files.
  [ master ] USE [ master ]
 database TEST1234 from disk = 'G:\backup\1111\1\backup_UZBI.bak' with restore database TEST1234 from disk = 'G: \ backup \ 1111 \ 1 \ backup_UZBI.bak' with
 'UZBI' to 'G:\backup\1111\1\TEST1234.mdf' , move 'UZBI' to 'G: \ backup \ 1111 \ 1 \ TEST1234.mdf' ,
 'UZBI_log' to 'G:\backup\1111\1\TEST1234_log.ldf' , move 'UZBI_log' to 'G: \ backup \ 1111 \ 1 \ TEST1234_log.ldf' ,
 ; norecovery ;
 log TEST from disk = 'G:\backup\1111\1\uzbi.bak' with recovery ; restore log TEST from disk = 'G: \ backup \ 1111 \ 1 \ uzbi.bak' with recovery ;
Re: Restore database from transaction log [new]
Danion,

+
151384 pages are processed for database "TEST1234", file "UZBI" for file 1.
Processed 4 pages for database "TEST1234", file "UZBI_log" for file 1.
RESTORE DATABASE successfully processed 151388 pages in 13.058 seconds (90.574 MB / s).
Message 4330, level 16, state 1, line 6
The backup dataset cannot be applied because it is on a recovery path that is not consistent with the database. The recovery path is a sequence of data and backup logs, passing through which the database reaches a specific recovery point. Find a compatible backup for recovery or restore the rest of the database to match the recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
The message 3013, level 16, state 1, line 6
RESTORE LOG was interrupted with an error.
Re: Restore database from transaction log [new]
Siver

Yes, before that there was an attempt to overwrite existing files.

invm wrote:
"You can restore the database and roll backup log.
Only first on the cats practice. "

I honestly do not remember how to determine by LSN whether files belong to the same chain of backups. But I had a question:
Backup log extension has .trn, which then is:
"Journal - UZBI_LogBackup_2018_12_24_21_10_23.bak (1 675 642 KB) from 24 12 2018" Is this not a diff event?
Re: Restore database from transaction log [new]
Danion,

I wrote above, this file was formed just at the time of the erroneous rewriting of the database, after, when I tried to restore everything with my own resources and with a little thought - when rewriting the test database - I had formed similar files and in the process of their creation something was written like ". So I decided that this is it.
Re: Restore database from transaction log [new]
Siver

And, then this is a tail-log, it may have the extension .bak, never had to use it :)

While I have the impression that you have a full and backup log from different backup chains.
Re: Restore database from transaction log [new]
And if through graphic equipment you do:
Right-click on the Databases + Restore database, device select both backups, ok + another ok. What remains in the backup sets to restore field? You can take a screenshot.

In my screenshot there was a diff, which by itself would not recover. And loaded the full and this diff, but different chains of backups.

Attached file Size - 26Kb
Re: Restore database from transaction log [new]
Danion,

I have the same picture, I have the equipment in Russian, and when choosing a recovery source, I have a similar picture when viewing the contents of a downloadable object
Re: Restore database from transaction log [new]
Danion,

Attached file Size - 74Kb
Re: Restore database from transaction log [new]
Danion,

Attached file Size - 121Kb
Re: Restore database from transaction log [new]
Siver

That is, both backup files were in the selection list, only the log remained?
If yes, then I got this with backups from different chains. Then the full + log is most likely not restored.
But maybe I am not that I understood and someone will advise something else.
Re: Restore database from transaction log [new]
Danion,

I only had one file selected, now I have selected both. in the selection line you can see why the database does not have a file name?

Attached file Size - 127Kb
Re: Restore database from transaction log [new]
Siver

I am at a loss with the answer why there is no backup name. The fact that tightened all the same - plus.

From this window, if in the field of assignment - Database names, you enter another name (for example, test1122), and then press Script, what code is obtained?
Re: Restore database from transaction log [new]
Danion,

USE [master]
BACKUP LOG [UZBI] TO DISK = N'G: \ backup \ auto \ UZBI_LogBackup_2018-12-26_19-35-11.bak 'WITH NOFORMAT, NOINIT, NAME = N'UZBI_LogBackup_2018-12-26_19-35-11', NOSKIP , NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5
RESTORE DATABASE [TEST] FROM DISK = N'G: \ UZBI \ backup_UZBI.bak 'WITH FILE = 1, MOVE N'UZBI' TO N'F: \ 1c \ TEST.mdf ', MOVE N'UZBI_log' TO N ' D: \ logs \ TEST_log.ldf ', NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TEST] FROM DISK = N'G: \ UZBI \ UZBI_LogBackup_2018-12-24_21-10-23.bak 'WITH FILE = 1, NOUNLOAD, STATS = 5

GO
Re: Restore database from transaction log [new]
Siver

Again, the existing database and attempt to overwrite without replace.

I don’t really understand what he didn’t like in the attempt to restore to the TEST1234 database, the error went exactly to the log file.
At the same time, the script of this part is similar and loads through the snap-in.
Re: Restore database from transaction log [new]
Thank you all so much for your help and assistance! base restored to the desired state!



Old articles you may read here
Go to Main page