Post reply

Restore 'WITH MOVE' without knowing filenames

  • August 11, 2016 at 3:55 pm

    #329186

    I want to perform a restore using WITH MOVE to move the files to a new location. However, I currently do not know the logical or physical filenames. Is there a way to do this? Or maybe a way to 'query' a backup to return that info so then I will have it?

    Thanks!

  • August 11, 2016 at 5:52 pm

    #1895134

    Clint-525719 (8/11/2016)

    I want to perform a restore using WITH MOVE to move the files to a new location. However, I currently do not know the logical or physical filenames. Is there a way to do this? Or maybe a way to 'query' a backup to return that info so then I will have it?

    Thanks!

    IIRC, the RESTORE FILELISTONLY command will return the logical and physical filenames. However, if you want to MOVE the files to a new location, you'll obviously need to know those new targeted physical filenames either by manual entry or by building them on the fly with dynamic SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 16, 2016 at 3:30 am

    #1895581

    I have an article on how to retrieve information [/url]from your backups that could help. You'll just have to convert that information into what you need for the restore as Jeff has outlined.

    "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

  • August 23, 2016 at 10:30 am

    #1896967

    Thanks to both of you. This helped.

  • August 23, 2016 at 8:03 pm

    #1897081

    Grant Fritchey (8/16/2016)

    I have an article on how to retrieve information [/url]from your backups that could help. You'll just have to convert that information into what you need for the restore as Jeff has outlined.

    It's a real shame that MS doesn't allow INTO sometemptable on these commands especially since they're not designed to identify the meta-data and so you can't even use something like a dynamic OPENROWSET to capture the output. Do you know of a non-xp_CmdShell method to get the data from such command into a table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 24, 2016 at 6:43 am

    #1897162

    Jeff Moden (8/23/2016)

    Grant Fritchey (8/16/2016)

    I have an article on how to retrieve information [/url]from your backups that could help. You'll just have to convert that information into what you need for the restore as Jeff has outlined.

    It's a real shame that MS doesn't allow INTO sometemptable on these commands especially since they're not designed to identify the meta-data and so you can't even use something like a dynamic OPENROWSET to capture the output. Do you know of a non-xp_CmdShell method to get the data from such command into a table?

    PowerShell?

    I'm not sure. It's not something I've tried before through T-SQL.

    "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

  • August 24, 2016 at 7:36 am

    #1897185

    I've done the "Create a table and use INSERT/EXEC" thing in the past. Works fine but it's an initial pain. There are a whole lot of things in SQL Server that I wish they'd add but aren't likely to because they're not flashy enough.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 24, 2016 at 7:57 am

    #1897200

    When I've automated restore processes, I've usually had access to msdb to retrieve the data directly. It sure makes things easier. Plus, we usually had standard drive configurations & folders, again, making stuff easier. If it's completely unknown and you have to go to this, you do have to hop through hoops inordinately.

    "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 8 posts - 1 through 7 (of 7 total)

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