Solution for restore database (move) not working with change in file structure

[el_grom]

Hello everybody,

Few months ago, I had a really bad issue with the restore of a SQL Server database.
I needed to restore a database but the original database was still in the server and cannot be replaced. Whatever, I created a "move template" and, as the datafile structure changed with time, I had to use the solution explained in this official "how-to" (solution 2 with alternate client restore). This worked fine

http://www.symantec.com/business/support/index?page=content&id=TECH19198

I have pretty much the same issue now but the solution previously mentioned is not working anymore.

The actual situation is the following: I need to restore a database (1 full and 3 transactions log backups to restore). There were multiple datafile manipulation between the full backup and the last transaction log backup (Actually: 2 datafiles were added and one of the existing's one has been renamed).

Since the original database is still there and cannot be replaced, I want to apply the solution provided in the "How-to" and use an alternate client like I did before but this does not work.
The script procuded by the "Create a move template" option shows me 2 datafiles and 1 logfile to un-comment (which I know is not correct)
If I start it after modifying the path, the process fails at the last step.
Obviously, even if the error message is really not clear, the wrong datafiles enumeration in the script is the cause. The database has, in fact, 4 datafiles and 1 log file)

My question is: How can I deal with this situation? How can I restore my database without replacing the original one?

Thanks for your help
PS: I used NetBackup 7.6.0.2 on a Windows Server 2008R2 with SQL Server 2012 SP1

[Marianne]

When you browse for restores on the destination client, what do you see?

If you do not see what you expect in the GUI, there is no point in continuing with 'create template'.

If you do not have a Full DB backup of ALL db's then transaction log backup will not backup the new db's.
If memory serves me right, the logs of TRX backup will containg a warning for new db's, saying that a full backup is needed before logs can be backed up.

Why not run another full backup of all DB's and then try the redirected restore again?

Ensure dbclient log folder exists on destination client before next restore attempt.

[el_grom]

Hi Marianne,

Thanks for your reply but I think there is a little misunderstanding here. In the GUI, I see all available backups as they should be there. This is OK.

In the other hand, there is a first full backup for all databases I have. I mentioned the transaction logs backup because I need to restore the database at a specific timestamp. I need to restore the database around 1pm last Tuesday. I have a full backup that run on Monday evening, and two transaction logs backup on Tuesday morning (7am and 10am) So, to restore the database on Tuesday 1pm, I need to begin restoring the Monday full and every transaction between the full backup and the specific timestamp.

My problem occurs because 2 datafiles were added Tuesday morning + another 1 has been renamed.

There were other datafile manipulation yesterday (Wednesday) so I can't write the script myself. And since I cannot replace the original database, the only remaining solution is to use the alternate client restore.

Problem is: The script is generated with the datafile situation of Monday evening (2 datafiles) which is different from situation of Tuesday 1pm (4 datafiles with 1 renamed).

Let me know if this is not clear enough

[Michael_G_Ander]

Think the problem is that you need to do a full backup after changes like adding database files

If there is a way to recover I think it will be

1. Restore the full backup and transaction logs before the datafile add/rename without recover

2. Add the database files and rename the datafile which changed name

3. Restore the transactions after the datafile add/rename with recover

[el_grom]

Hi Michael,

Thanks for your reply. I disagree with the fact that I need to take a full backup for some reasons:
1) It is not part of the SQL Server documentation (http://msdn.microsoft.com/en-us/library/ms189253.aspx)
2) NetBackup does not react with that. (It does react when a first full backup is missing for example)
3) If I restore the database in the same client with same name (option "restore selected object"), it DOES work.

Third reason clearly shows that the image I want to restore is valid. Problem is located in the script file generated when I request a "move template".

Your solution could work... if I have a way to know when the datafiles where added and what was their logical name. With all the information, I'm pretty sure this solution would work but this is a really dangerous way to do it...

I'm really astonished that my situation (which is not really tricky) seems impossible for this product

[Michael_G_Ander]

1) Might not be, but as it always been good practice to take a full backup when doing change major things like the number of database files on all the databases I have worked with

2) Actually it does, usually be giving a status 1 on the transaction log backup

3) Then choose restore selected object and save as script, then modiify the script to a move script

For the information talk to your SQL DBA

Don't think you would be better with any other backup product as they would use the same VDI api to communicate with the SQL server. 

[el_grom]

1) Sure it's would be better but not always possible. I'm sure I don't have to give you examples

2) Nope it doesn't. NetBackup took the transaction log backup fine and end it up with status 0. RESTORING this image results in a status 1. So either the image is corrupted which NetBackup does not react to when taking the backup, either NetBackup cannot restore its own image. In both case, there is an issue.

3) Like I said, I can't. The original database is still in place and cannot be replaced. Also, I'm sure I don't have to explain why.

I'm the DBA, just not the NetBackup expert. That's why I come with my issue here.

I actually had better behaviour with our previous backup solution which wasn't NetBackup. If you want to ask why we decided to change. It was simply not my choice. The API is fine. I never had to edit a single script with this other tool. It simply "knows" the data structure at the time of the backup image.

EDIT:

3) I can't replace the original database. Plus, if I follow what you said, the script produced is quite basic. When I start it, I get the error that the restored files cannot be restored. Because the original file is still present and used by the original database. The only way to deal with this error is to specify the MOVE command with new path and logical name of the files. This lead us to my very first issue: I don't know these logical files name and cannot restore it

[Michael_G_Ander]

Please post the progress log of the failing transaction log restore

You should change the script to a MOVE script before starting it. The logical names can be gotten from the running database or by creating move templates for each backup image.

[el_grom]

Hi Michael,

I'll post the log asap, (i'm out of the office for a couple of days so cannot have access to it :( ). But basically it says "File specified cannot be restored"

I agree I have to make a MOVE script and adapt it. The problem is "how can I adapt it". In my case, the logical names can NOT be found in the running database since they have changed.
To be sure I understand your last sentence: Do you want me to create the MOVE script after each backup?

This needs confirmation but I'm pretty sure this will leave some time laps with the exact same issue since the backup operation cannot be done at the EXACT same moment than the datafile change. However, if I understood correctly again, this is a custom job I need to create to "patch" NetBackup? For me, this kind of functionnality should be built-in in such a product (it is in others). I'm really surprised to see the difficulty of such a case.

If you want to reproduce the case, here are my steps:

1. Create a database XXX (logical names xx1 and xx_log)
2. Take a full backup
3. Add a datafile (logical name xx3)
4. Take a transaction log backup
5. Add a datafile (logical name xx4)
6. Take a transaction log backup
7. Change the logical names (from xx1 to yy1, xx2 to yy2 ...)

What I want to do is: Restore the transaction log backup image taken on step 6 knowing that the current database cannot be replaced. Move script file generated is not able to retrieve the "xx?" logical names. In fact, in certain cases, it will not take the 2 extra datafiles in the loop....

In this case, taking a full backup after each change is not really possible (really big database). And you can imagine that the "add and rename" datafile is part of a partitionning job which takes hours to complete.

EDIT: Your previous solution "restore the full, get the names then restore the transaction logs" will not work either since I don't have the specific timestamp of the "add datafile" action. And I don't have its name at this timestamp. In my example, I used simple datafile logical names which is not always predictible

[Marianne]

Check dbclient log for the log backups taken in step 4 and 6.

You will see that nothing is backed up for logical name xx3 and logical name xx4, even if you have $all in the backup script.

Log backups cannot be taken when there is no full backup.
I believe this is an SQL requirement, not NBU.
(For Filesystem backups, NBU will simply backup everything in Incr schedule when there is no prior Full backup)
Best to confirm with Microsoft.

[el_grom]

Hi Marianne,

What you're saying is absolutely not correct:

• There is nothing like this in the log
• If I specify the secondaries datafiles in the bch script file (tested on a test-database) with the correct logical names, the database is completely restored with all datafiles and their data!

If a tr.log backup was not possible after a change in the db structure, I would hope that NBU returned something else than a successful job...

Question is still the same: How can I restore my database if I don't know its structure at each specific backup image timestamp?

[Michael_G_Ander]

Not a "patch" but a way to work around the problem you are have described.

Which other products ? have never heard of any that would not have an issue in this scenarie, unless they can only perform full backups