Restoring from a high availability backup into a test database

  • I have a live database that is participating in an AlwaysOn High Availability group using MSSQL Server 2012. This is set to backup nightly using the secondary node. This creates a .bak file as you'd expect.

    When I then attempt to use this .bak file to restore into a test database, it errors stating that the live database is in use. Yes, I have tried the 'with move' statement.

    Here's my script:

    RESTORE DATABASE TEST

    FROM DISK = 'i:\backup\LIVE.bak'

    WITH REPLACE, NORECOVERY;

    MOVE 'Test' TO

    'G:\Data\Test.mdf',

    MOVE 'Test_Log'

    TO 'H:\Logs\Test_log.ldf';

    Do I have to break the high availability group for the live database before restoring into TEST?

    Thanks!

  • john.ward 24608 (2/27/2015)


    I have a live database that is participating in an AlwaysOn High Availability group using MSSQL Server 2012. This is set to backup nightly using the secondary node. This creates a .bak file as you'd expect.

    When I then attempt to use this .bak file to restore into a test database, it errors stating that the live database is in use. Yes, I have tried the 'with move' statement.

    Here's my script:

    RESTORE DATABASE TEST

    FROM DISK = 'i:\backup\LIVE.bak'

    WITH REPLACE, NORECOVERY;

    MOVE 'Test' TO

    'G:\Data\Test.mdf',

    MOVE 'Test_Log'

    TO 'H:\Logs\Test_log.ldf';

    Do I have to break the high availability group for the live database before restoring into TEST?

    Thanks!

    Are you backing up from the secondary using COPY_ONLY?

    Are you attempting to restore over the database that's part of the Availability Group? If so, you'll have to break it out of that first. Availability Groups don't like you messing with the databases within them.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply