Restore DB takes a coffee break to open file explorer

  • Edvard Korsbæk - Tuesday, February 21, 2017 5:14 AM

    Do you have an easy way to declare the @FileListOnlyTable (Something like script as...)

    I don't know of any, no.  I usually find myself copying and pasting from Books Online.  If you're going to so this regularly you could create a permanent table, which would make your code a lot more concise.

    John

  • Edvard Korsbæk - Tuesday, February 21, 2017 5:14 AM

    That was Easy!

    Do you have an easy way to declare the @FileListOnlyTable (Something like script as...)

    But anyway, Thanks!

    Edvard

    Another option is that you could use something with a table variable in your stored procedure,, using something along the lines of the following, adding variables as needed:

    DECLARE @FileList TABLE (
      [LogicalName]            NVARCHAR(128),
      [PhysicalName]            NVARCHAR(260),
      [Type]                    CHAR(1),
      [FileGroupName]            NVARCHAR(128),
      [Size]                    NUMERIC(20,0),
      [MaxSize]                NUMERIC(20,0),
      [FileID]                BIGINT,
      [CreateLSN]                NUMERIC(25,0),
      [DropLSN]                NUMERIC(25,0),
      [UniqueID]                UNIQUEIDENTIFIER,
      [ReadOnlyLSN]            NUMERIC(25,0),
      [ReadWriteLSN]            NUMERIC(25,0),
      [BackupSizeInBytes]        BIGINT,
      [SourceBlockSize]        INT,
      [FileGroupID]            INT,
      [LogGroupGUID]            UNIQUEIDENTIFIER,
      [DifferentialBaseLSN]    NUMERIC(25,0),
      [DifferentialBaseGUID]    UNIQUEIDENTIFIER,
      [IsReadOnly]            BIT,
      [IsPresent]                BIT,
      [TDEThumbprint]            VARBINARY(32), --SQL 2008 and higher
      [SnapshotURL]            NVARCHAR(360) --SQL 2016 and higher
    )
    INSERT INTO @FileList
        EXEC('RESTORE FILELISTONLY
        FROM DISK = ''D:\BBBACKUP_2017\RC\FEBRUAR\easyplan-drift-170216-after.bak''')
        
    SELECT * FROM @FileList

    But I'd probably do as John suggested if you do this frequently and create a table to use for your restore procedure. 

    Sue

  • A lot times the reason for slow file browses is that there is a mapped drive that isn't available.  The file browser will try go access that drive and will have to wait for a timeout before continuing on.

  • thanks!

    thats the explanation for now.
    but anyway, I have learned a lot in this processs, so thanks to everybody anyway.

    best wishes

    Edvard Korsbæk

  • royce.bacon - Wednesday, February 22, 2017 8:13 AM

    A lot times the reason for slow file browses is that there is a mapped drive that isn't available.  The file browser will try go access that drive and will have to wait for a timeout before continuing on.

    +100 for that!  It's a common problem.

    --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)

Viewing 5 posts - 16 through 19 (of 19 total)

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