• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

TDP 7.1.1 SQL Rstore of AlwaysOn database

odhammar

ADSM.ORG Member
#1
Have tried to restore a SQL alwayson database into a new database and relocating the files with no luck. I have tried both with /relocatedir and /relocate /to but TSM (tdpsqlc) tries to restore the files to the original folder??

Commands tested:
tdpsqlc restore xxxx /fromsqlserver=yyyy /querynode=alwayson /relocate=xxxx2 /to=d:\restore\xxxx2.mdf /into=xxxx2

or
tdpsqlc restore xxxx /fromsqlserver=yyyy /querynode=alwayson /relocatedir=d:\restore /into=xxxx2

Any with positive experience with a restore of a alwayson database in to a new database? It works to do the restore to original folder.
 

odhammar

ADSM.ORG Member
#3
We have a long case open with IBM and they haven't so far got any closer a solution for us. But I have done a lot more research/testing on this and at least got me an answer when it doesn't work :)

When you have a SQL AlwaysOn cluster the secondary are normally not readable (= No need for licens) because it just used for replication and also normally that one you take the TDP backups on. It works to take backups on the secondary but it doesn't work to do a restore of the backup. If I change the secondary to readable everything works.
 

odhammar

ADSM.ORG Member
#4
Update: IBM finally understood the problem and manage to reproduce the problem in their lab.

They have now created an report (APAR) and working on it.

Here's the APAR (Draft):
------------
Abstract:
Relocate restore operations may fail with ACO5436E and ACO5436E for AlwaysOn databases from non-readable secondary replicas.



Problem Description:
Tivoli Storage Manager for Databases: Data Protection for Microsoft
SQL Server is not able to perform a restore to alternate location of
an AlwaysOn database which was backed up using a non-readable
secondary replica.

The restore fails showing following messages:
ACO5436E A failure occurred on stripe number (0), rc = 428
ACO5436E The SQL server aborted the operation.
Restore of AAGDB failed.
An exception occurred while executing a Transact-SQL statement or
batch.
The file 'C:\Program Files\Microsoft
SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AAGDB.mdf' cannot be
overwritten. It is being used by database 'AAGDB'.

This problem only occurs if the backup of this database was taken
from a non-readable secondary replica.

Customer/L2 Diagnostics:
The problem is Data Protection for SQL Server cannot read the
file information of AlwaysOn databases from a non-readable
secondary replica.

A tdpsqlc query sql command will also return no file sizes for
these databases:

5. tdpsqlc query sql * /sqlserver=Non-readable-AlwaysOnInstance

SQL Server Name ........................ Non-readable-AlwaysOnInstance

SQL Database Name ........................ AAGDB
SQL Database AAG Name ........... ........ AAG
SQL Database Replica Role .. ............. Secondary
SQL Database Sync State Description ...... Synchronized
SQL Database Data Space Allocated ........ 0
SQL Database Data Space Used ............. 0
SQL Database Log Space Allocated ......... 0
SQL Database Log Space Used .............. 0
SQL Database Options .....................


Tivoli Storage Manager Versions Affected:
Tivoli Storage Manager for Databases Version 6.4 and 7.1:
Data Protection for Microsoft SQL Server




Initial Impact:
Medium




Additional Keywords:
TSM High Availability Primary AAG groups replication relocate position path directory


Local Fix:
The database can be restored into original location without any
problem.
Enable secondary replicas for readable access to prevent from this situation.
 

kwh0507

Active Newcomer
#6
Has anyone heard any updates on this? I'm still having problems restoring this to a new database server, we validate our backups and send a report and I have two always on clusters and failing to restore this to our test server. I've tried running backups on the primary server and I still get failures. I checked the IBM site, login and it shows the infamous oops, this page doesn't exist clicked here: http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT08974 makes me login and then the oops page.
 

odhammar

ADSM.ORG Member
#7
I gets information mail from IBM about this once a month but still no solution, don't know what they are doing it's nearly 10 month now!!!
 

odhammar

ADSM.ORG Member
#8
Apply fixing level when available. This fix is currently *
* projected to be available in: *
* Tivoli Storage FlashCopy Manager for Microsoft SQL Server *
* 4.1.4 *
* Tivoli Storage Manager for Databases: Data Protection for *
* Microsoft SQL Server 7.1.4
 

smajl

ADSM.ORG Senior Member
#9
As per workaround description in http://www-01.ibm.com/support/docview.wss?uid=swg1IT08974 you can apply fixpack 7.1.3.1. Check your passport advantage if it is present there. If not ask IBM support to provide you custom fix - this should be on-demand created archive zith newer version of some files that you need to replace on server. Try to use some "clever" justification, i.e. need restore to pre-prod environment and at this point our whole deployment is freezed due to failing restore.
I had some troubles described by APAR and confirmed as program error, but fix was planned in 7.1.3 that was not out yet at that time and issue was not fixed by any patch level. IBM support provided me archive file with multiple files for my 7.1.2 installation, so I was get rid of the problem until 7.1.3 was out. After that I just updated TDP.
 

odhammar

ADSM.ORG Member
#11
I was a little bit to fast on the answer about it was solved, worked like I wrote on one server but not on next AlwaysOn cluster and the difference is that one doesn't work just have SQL 2014 CU3 and the one working SQL2014 CU7
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 16 19.0%
  • Keep using TSM for Spectrum Protect.

    Votes: 52 61.9%
  • Let's be formal and just say Spectrum Protect

    Votes: 9 10.7%
  • Other (please comement)

    Votes: 7 8.3%

Forum statistics

Threads
31,419
Messages
133,822
Members
21,533
Latest member
MACH
Top