Go to Main page..

Script database recovery , Microsoft SQL Server





Database Recovery Script [new]
Good day.

From the last admin there was a script that updated the test bases 1C. I checked that it works and did not particularly peer at it. But it was necessary to add the second log file to the erp database and the script stopped working. I decided to fix this part in it so that it would work again, but after studying the script I did not understand why it worked at all ...

Request text:
+
 @sql nvarchar ( 4000 ) DECLARE @sql nvarchar ( 4000 )
 @db nvarchar ( 100 ) DECLARE @db nvarchar ( 100 )
 @ full nvarchar ( 250 ) DECLARE @ full nvarchar ( 250 )
 @diff nvarchar ( 250 ) DECLARE @diff nvarchar ( 250 )
 @erphrm nvarchar ( 10 ) DECLARE @erphrm nvarchar ( 10 )

 @db = 'ERP_rasrab' SET @db = 'ERP_rasrab'
 @ full = 'erp_20122018.bak' SET @ full = 'erp_20122018.bak'
 @diff = '' SET @diff = ''

 @erphrm = SUBSTRING ( @ full , 1 , 3 ) SELECT @erphrm = SUBSTRING ( @ full , 1 , 3 )
 [ master ] USE [ master ]
 @sql = 'USE [master]; SET @sql = 'USE [master];  + @db + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; '
 sp_executesql @sql EXEC sp_executesql @sql
 Полный --- Full
 @sql = 'RESTORE DATABASE ' + @db + ' FROM DISK = N''E:\SQL1\' + @ full + '.bak'' WITH FILE = 1, NOUNLOAD, NORECOVERY, REPLACE, STATS = 5' SET @sql = 'RESTORE DATABASE' + @db + 'FROM DISK = N''E: \ SQL1 \' + @ full + '.bak' 'WITH FILE = 1, NOUNLOAD, NORECOVERY, REPLACE, STATS = 5'
 sp_executesql @sql EXEC sp_executesql @sql
 --Diff
 @diff<> '' IF @diff <> ''
	 BEGIN
		 @sql = 'RESTORE DATABASE ' + @db + ' FROM DISK = N''E:\SQL1\' + @diff + '.bak'' WITH FILE = 1, NOUNLOAD, NORECOVERY, STATS = 5' SET @sql = 'RESTORE DATABASE' + @db + 'FROM DISK = N''E: \ SQL1 \' + @diff + '.bak' 'WITH FILE = 1, NOUNLOAD, NORECOVERY, STATS = 5'
		 sp_executesql @sql ; EXEC sp_executesql @sql ;
	 END

 @sql = 'RESTORE DATABASE ' + @db + ' WITH RECOVERY' SET @sql = 'RESTORE DATABASE' + @db + 'WITH RECOVERY'
 sp_executesql @sql EXEC sp_executesql @sql

 @sql = 'ALTER DATABASE ' + @db + ' SET MULTI_USER' SET @sql = 'ALTER DATABASE' + @db + 'SET MULTI_USER'
 sp_executesql @sql EXEC sp_executesql @sql


You need to specify the database where you restore, full backup and spruce up are - diff.
Ways to sell and test different, file names, too. But the script was restored with moving and renaming to db.mdf and dd.ldf

Now I rewrote it with an explicit move in the text for this base and it works fine, but I’m interested in what the past admin option worked on.

While testing I found out: it does not work if there is no base.
It does not work with any name, but mostly with erp_family razrab, and erp_ accidental surname did not work.
There are no triggers on the server, there is nothing in the master too.
When restoring the database with additional logs, the problem arose only with it, on mdf and the first log move seemed to work.
In general, it looks as if there is an object in which there is a database name and file paths for moving and when restoring through a script with an automatic muvit.
Who has any ideas about a possible implementation, I wonder how this is implemented.
Re: Database Recovery Script [new]
and what problems did you find in the script?
Here it is:

 [ master ]; USE [ master ];  DATABASE ERP_rasrab SET SINGLE_USER WITH ROLLBACK IMMEDIATE ; ALTER DATABASE ERP_rasrab SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
 DATABASE ERP_rasrab FROM DISK = N 'E:\SQL1\erp_20122018.bak.bak' WITH FILE = 1 , NOUNLOAD , NORECOVERY , REPLACE , STATS = 5 RESTORE DATABASE ERP_rasrab FROM DISK = N 'E: \ SQL1 \ erp_20122018.bak.bak' WITH FILE = 1 , NOUNLOAD , NORECOVERY , REPLACE , STATS = 5
 DATABASE ERP_rasrab WITH RECOVERY RESTORE DATABASE ERP_rasrab WITH RECOVERY
 DATABASE ERP_rasrab SET MULTI_USER ALTER DATABASE ERP_rasrab SET MULTI_USER


those. WITH REPLACE is done to an existing database,
All paths to your database are taken from the database metadata, not backup.

I'm on my laptop (with a single C drive), so I drag the bases,
although on the server from where I take it, the files are spread across the O and P disks
Re: Database Recovery Script [new]
although yes
if for example we have 2 servers on one computer,
on both the base exists,
the paths to the bases are different,
then an attempt to recover from one to another,
specifying only with replace,
it will end in failure, for it will try to restore over the source database files.

those. there is some inconsistency:
first, he tries to restore the paths taken from the backup,
and only if there are none, restores along the paths of the current database
Re: Database Recovery Script [new]
Yasha123,

Through the print checked that yes - the recovery script is similar to your version.

Unless when restoring by default, the data of the existing base is taken, and not the data that the base had, whose backup?
I used to always indicate exactly when recovering. Well, if only I do not raise the same base from backup.
Maybe I'm stupid with this option.

Now I tried again, created a test123 database and tried to restore a full backup of the hrm database (mdf + log) there.
Errors:

Msg 5133, Level 16, State 1, Line 1
The directory lookup for the file "D: \ SQL \ hrm.mdf" failed with the system.
Msg 3156, Level 16, State 3, Line 1
File 'hrm' cannot be restored to 'D: \ SQL \ hrm.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
The directory lookup for the file "D: \ SQL \ hrm_log.ldf" failed.
Msg 3156, Level 16, State 3, Line 1
File 'hrm_log' cannot be restored to 'D: \ SQL \ hrm_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Path D: \ SQL on the server from where backup. It looks like by default it tries to recover to the base directory where the backup is from. On the test, the default server paths are E: \ DATA \ and E: \ LOG \

The point that can not create is removed, it falls at the stage of USE [master]; ALTER DATABASE '+ @db +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Re: Database Recovery Script [new]
Just because the developer database can not be overwritten and will have to wait for treatment from them.
It will be possible to check whether the script is still working on the hrm database (erp 2 has a log and does not work), if so (and in theory nothing has changed), then pull out the query text during recovery. Will there be a construction with a move or just a RESTORE DATABASE which I get through the print without starting.
Re: Database Recovery Script [new]
here is my server, with a single C drive,
and here is their backup, with their paths that I don’t have.
I recover in the existing base.
in the picture successful recovery and disks,
who sees my server (only 1 disk and sees)

Attached file Size - 48Kb
Re: Database Recovery Script [new]
in your experiment, another base name,
where are you recovering.
Re: Database Recovery Script [new]
Yasha123,

It is interesting, but where is the way restored without indication, if the default paths and the paths of the base being restored are different?

Personally, after several cases of attempting to rewrite the wrong base, I prefer to always specify with move.

It turns out that the restore database has its own algorithm and sometimes restores where the base was (or the paths in the server settings), and sometimes it starts on the way from backup.

Then it is possible, in my case, that after restoring to the database with 1 log file, it began to restore backup paths and some kind of scheme with hidden triggers, as I thought at first, no. There will be a task to update the database, I will check for sure.
In the meantime, you can re-read what Microsoft is writing about this.
Re: Database Recovery Script [new]
Yasha123
in your experiment, another base name,
where are you recovering.


Well, there always was another name. Productive base in DB_familyRazraba.
Re: Database Recovery Script [new]
By the way, it makes no difference.
and with a different name will overwrite.
I just compare with my situation
(I have the same database name)

all this is documented:
RESTORE Statements (Transact-SQL)

Attached file Size - 60Kb
Re: Database Recovery Script [new]
Yasha123,

Aired and ideas appeared, but I will check it tomorrow.
It is clear that the replay allows you to overwrite the database leaving only the name of it.

In my attempts and errors there was a pattern: I tried to restore files with logical names other than backup along the way from backup. In an attempt where it showed an error only for log 2, mdf and the first log had logical names from the productive, and when I created it with my hands, it was erp_expand.

The case does not compare the logical file name of the rewritable database with the backup, if it matches, then the path is rewritable, if not, then the path from the backup.
Re: Database Recovery Script [new]
The option of checking logical names seems to be passing by too. Made logical names, as on the same product errors.
And on the test database, the developer again swears only at the second log file (adding to the test before restoring with the log name sold did not help). At the same time, when you open the recovery window in SSMS, select backup files, restore the database and go to the tab with paths, then everything is ok. Settings for moving files by default server paths, where you restore.
Re: Database Recovery Script [new]
The initial assumption that the script worked with a hidden trigger or something else was not confirmed.

These are exactly the properties of RESTORE DATABASE. I prefer to continue to use explicit MOVE when restoring to another server.

After successful recovery via SSMS, it began to work normally and simply through RESTORE DATABASE. At the same time, it restores along the paths of the rewritable database, even if it does not coincide with the default server paths.
Re: Database Recovery Script [new]
Danion
I prefer to continue to use explicit MOVE when restoring to another server.
It goes without saying. But, of course, it’s interesting what fancy algorithms laid out in RESTORE DATABASE, an interesting study :-)

Danion
After successful recovery via SSMS, it began to work normally and simply through RESTORE DATABASE. At the same time, it restores along the paths of the rewritable database, even if it does not coincide with the default server paths.
That is, it turns out, first the path is taken from the base, then from the buck ...
By the way, there is such an option that mapping RESTORE files does not by physical or logical names, but by file_id, some of my experiments point to this.
Re: Database Recovery Script [new]
alexeyvg
That is, it turns out, first the path is taken from the base, then from the buck ...

No, it does not work.
try to repeat my experiment (3rd post from the beginning),
when both servers are on the same physical and on both there is an identical base.
if you take a backup of the database from the first server and try to restore
to the existing database without specifying with move,
server climbs the paths prescribed in the backup
those. will try to overwrite the database files of the first server
Re: Database Recovery Script [new]
we have 2 servers, 2016 and 2012,
both have a db2 base.
I take a backup from the 2012th and try to restore to 2016 on top of the existing db2.
The server is trying to overwrite the source database files.
where he fails, because 2012 is also running,
so we get
error
"Attempting the RestoreContainer :: ValidateTargetForCreation 'on' C: \ Program Files \ Microsoft SQL Server \ MSSQL11.SQL_2012 \ MSSQL \ DATA \ db2.mdf '.


Attached file Size - 55Kb
Re: Database Recovery Script [new]
Yasha123
alexeyvg
That is, it turns out, first the path is taken from the base, then from the buck ...

No, it does not work.
try to repeat my experiment (3rd post from the beginning),
when both servers are on the same physical and on both there is an identical base.
if you take a backup of the database from the first server and try to restore
to the existing database without specifying with move,
server climbs the paths prescribed in the backup
those. will try to overwrite the database files of the first server
And the "identical base" means that they are both recovered from the same backup? Or does this mean that they just have the same name?
Re: Database Recovery Script [new]
alexeyvg
Yasha123
when both servers are on the same physical and on both there is an identical base.
if you take a backup of the database from the first server and try to restore
to the existing database without specifying with move,
And the "identical base" means that they are both recovered from the same backup? Or does this mean that they just have the same name?

Here, if the base is really identical, that is, with the same GUID:
 master use master
 go
 exists ( select * from sys . databases where name = 'db1' ) if exists ( select * from sys . databases where name = 'db1' )
	 database db1 drop database db1
 exists ( select * from sys . databases where name = 'db2' ) if exists ( select * from sys . databases where name = 'db2' )
	 database db2 drop database db2
 go
 database db1 create database db1
 ON   
 NAME = db1_data , ( NAME = db1_data ,  
     = 'C:\tmp\db1\db.mdf' ) FILENAME = 'C: \ tmp \ db1 \ db.mdf' ) 
 ON LOG ON  
 NAME = db1_log , ( NAME = db1_log ,  
     = 'C:\tmp\db1\db.ldf' ); FILENAME = 'C: \ tmp \ db1 \ db.ldf' );  
 GO
 database db2 create database db2
 ON   
 NAME = db1_data , ( NAME = db1_data ,  
     = 'C:\tmp\db2\db.mdf' ) FILENAME = 'C: \ tmp \ db2 \ db.mdf' ) 
 ON LOG ON  
 NAME = db1_log , ( NAME = db1_log ,  
     = 'C:\tmp\db2\db.ldf' ); FILENAME = 'C: \ tmp \ db2 \ db.ldf' );  
 GO

 database db1 to disk = 'C:\tmp\db1.bak' backup database db1 to disk = 'C: \ tmp \ db1.bak'
 database db2 to disk = 'C:\tmp\db2.bak' backup database db2 to disk = 'C: \ tmp \ db2.bak'
 GO


 - There will be a lot of mistakes !!!
 database db2 from disk = 'C:\tmp\db1.bak' restore database db2 from disk = 'C: \ tmp \ db1.bak' 
 REPLACE ; WITH REPLACE ;
 go

 - Then restore db2 from db1, that is, make them "identical"
 database db2 from disk = 'C:\tmp\db1.bak' restore database db2 from disk = 'C: \ tmp \ db1.bak' 
 MOVE 'db1_data' TO 'C:\tmp\db2\db.mdf' , WITH MOVE 'db1_data' TO 'C: \ tmp \ db2 \ db.mdf' ,
 'db1_log' TO 'C:\tmp\db2\db.ldf' , MOVE 'db1_log' TO 'C: \ tmp \ db2 \ db.ldf' ,
 ; REPLACE ;
 go


 - And here everything is fine!
 - because after the first recovery she changed the GUID,
 - and during restoration, its paths are used, which, we note
 - different from those in bakapu
 database db2 from disk = 'C:\tmp\db1.bak' restore database db2 from disk = 'C: \ tmp \ db1.bak' 
 REPLACE ; WITH REPLACE ;
Re: Database Recovery Script [new]
alexeyvg
 - And here everything is fine!
 - because after the first recovery she changed the GUID,
 - and during restoration, its paths are used, which, we note
 - different from those in bakapu
 database db2 from disk = 'C:\tmp\db1.bak' restore database db2 from disk = 'C: \ tmp \ db1.bak' 
 REPLACE ; WITH REPLACE ;
Although here if once again to make db1.bak, then it will not be restored.
Riddles continuous :-)
Re: Database Recovery Script [new]
Yasha123,

And in the last example, the path at the base db2 2012 server was the same? C: \ ... \ MSSQL11.SQL_2012 \ ... \ db2.mdf?
Then for 2016 I tried to recover on the way from backup. And try this backup for 2016 to restore with your hands with move, and then again just restore database. Most likely it will work.

What exactly is changing and when it begins to take the paths of rewritable files is not yet clear. It may even add a mark to some system table when first restored. Checked msdb.dbo.restorefile, there is information on the paths during recovery, but with this table would rather restore to the same place where it was last time, but this is not so.

Interestingly, the information from Microsoft did not find explanations on the logic of Restore; I would not be surprised if it also changed from the server version.
Re: Database Recovery Script [new]
Danion
And try this backup for 2016 to restore with your hands with move, and then again just restore database. Most likely it will work.
Yes, I brought the script above, for the first time it works, with the next one - no.
Re: Database Recovery Script [new]
Danion
Interestingly, the information from Microsoft did not find explanations on the logic of Restore; I would not be surprised if it also changed from the server version.
Yeah, there option REPLACE described very poorly.
Re: Database Recovery Script [new]
alexeyvg,

I slowly typed my message at the same time :)

Not really understood what stopped working:

Restaurant with move - everything is ok.
The restaurant is simple - everything is ok.
The same backup.

Then a new backup was taken from db1 and
on db2 not recovered?
Re: Database Recovery Script [new]
I tried two full backups of the product with a weekly difference. He wants to move for the first time, but there were no further problems. It was enough to specify the path for the first backup only.
Re: Database Recovery Script [new]
I report.
when restoring to the "identical base",
which is not a restorer at all,
base paths are taken (not backups)

when restoring to base
which is obtained by restoring a similar one from another server,
the paths of the base itself are already taken,
those. as suggested by Danion:
Danion
And in the last example, the path at the base db2 2012 server was the same? C: \ ... \ MSSQL11.SQL_2012 \ ... \ db2.mdf?
Then for 2016 I tried to recover on the way from backup.
And try to restore this backup for 2016 with your hands with move,
and then again just restore the database. Most likely it will work .


of course, the database has changed, but not at all on the database from the first server.
but now that these bases are the same, this is family_guid ,
mentioned, by the way, in the RESTORE documentation.

and this family_guid refers to backup:
BOL
Identifier of the backup family for detecting matching restore states.


total we have:
when restoring, the family_guid of both bases is compared,
if they match, then the base has already been restored from the backup of this family,
so, let's not get into the paths recorded in the backup, let's go directly overwrite the existing files.
but if the families are different, we are NOT a copy of the database obtained by the previous restaurant,
so if the paths are not specified in the team, then let's go and read them from the backup and use these paths.
and woe to him who already has something along this path, they will overwrite it,
without even looking at what we are overwriting.

Attached file Size - 22Kb
Re: Database Recovery Script [new]
Danion
Restaurant with move - everything is ok.
The restaurant is simple - everything is ok.
The same backup.

Then a new backup was taken from db1 and
on db2 not recovered?
And, apparently, I somewhere was mistaken, it is restored.

So the point is in the GUID, more precisely, as noted by Yasha123, in FAMILY_GUID

Here is the corrected script
 -- сделать папки C:\tmp\db1 и C:\tmp\db2 use master go if exists ( select * from sys . databases where name = 'db1' ) drop database db1 if exists ( select * from sys . databases where name = 'db2' ) drop database db2 go create database db1 ON ( NAME = db1_data , FILENAME = 'C:\tmp\db1\db.mdf' ) LOG ON ( NAME = db1_log , FILENAME = 'C:\tmp\db1\db.ldf' ); - To run the script, you need to make the folders C: \ tmp \ db1 and C: \ tmp \ db2 use master go if exists ( select * from sys . Databases where name = 'db1' ) drop database db1 if exists ( select * from sys . databases where name = 'db2' ) drop database db2 go create database db1 ON ( NAME = db1_data , FILENAME = 'C: \ tmp \ db1 \ db.mdf' ) LOG ON ( NAME = db1_log , FILENAME = 'C : \ tmp \ db1 \ db.ldf ' );  database db2 ON ( NAME = db1_data , FILENAME = 'C:\tmp\db2\db.mdf' ) LOG ON ( NAME = db1_log , FILENAME = 'C:\tmp\db2\db.ldf' ); GO create database db2 ON ( NAME = db1_data , FILENAME = 'C: \ tmp \ db2 \ db.mdf' ) LOG ON ( NAME = db1_log , FILENAME = 'C: \ tmp \ db2 \ db.ldf' );  database db1 to disk = 'C:\tmp\db1.bak' with INIT backup database db2 to disk = 'C:\tmp\db2.bak' with INIT print '' GO -- Тут будет куча ошибок !!! GO backup database db1 to disk = 'C: \ tmp \ db1.bak' with INIT backup database db2 to disk = 'C: \ tmp \ db2.bak' with INIT print '' GO - There will be a bunch of errors !!!  -- и пути берутся из бакапа print 'restore database db2 from db1.bak' restore database db2 from disk = 'C:\tmp\db1.bak' WITH REPLACE ; - because FAMILY_GUID does not match db2, - and the paths are taken from the print backup print db2 from db1.bak restore database db2 from disk = 'C: \ tmp \ db1.bak' WITH REPLACE ;  '' go -- Тогда восстанавливаем db2 из db1, то есть делаем их "идентичными" print 'restore database db2 from db1.bak with move' restore database db2 from disk = 'C:\tmp\db1.bak' WITH MOVE 'db1_data' TO 'C:\tmp\db2\db.mdf' , MOVE 'db1_log' TO 'C:\tmp\db2\db.ldf' , REPLACE ; print '' go - Then restore db2 from db1, that is, we make them "identical" print 'restore database db2 from db1.bak with move' restore db2 from disk = 'C: \ tmp \ db1.bak' WITH MOVE ' db1_data ' TO ' C: \ tmp \ db2 \ db.mdf ' , MOVE ' db1_log ' TO ' C: \ tmp \ db2 \ db.ldf ' , REPLACE ;  '' go -- А вот тут всё нормально ! print '' go - And here everything is fine!  -- теперь он такой же, как у db1 -- и при восстановлении используются её пути, которые, заметим, -- отличаются от тех, которые в бакапе print 'restore database db2 from db1.bak' restore database db2 from disk = 'C:\tmp\db1.bak' WITH REPLACE ; - because after the first recovery, FAMILY_GUID has changed, - now it is the same as in db1 - and during restoration, its paths are used, which, we note, are different from those in the print 'restore database db2 from db1.bak ' restore database db2 from disk = ' C: \ tmp \ db1.bak ' WITH REPLACE ;  '' -- Но мы ещё сделаем бакап db1 backup database db1 to disk = 'C:\tmp\db1.bak' with INIT ; print '' - But we still make a db1 backup database db1 to disk = 'C: \ tmp \ db1.bak' with INIT ;  '' go -- И опять попытаемся восстановить его в db2 -- всё нормально! print '' go - And again we will try to restore it to db2 - everything is fine!  'restore database db2 from db1.bak' restore database db2 from disk = 'C:\tmp\db1.bak' WITH REPLACE ; print 'restore database db2 from db1.bak' restore database db2 from disk = 'C: \ tmp \ db1.bak' WITH REPLACE ;  '' print '' 
Re: Database Recovery Script [new]
The mystery is revealed :) Thank you all for participating!



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ