Restore

  • DECLARE @FilesCmdshell TABLE (
    outputCmd NVARCHAR (255)
    )
    DECLARE @FilesCmdshellCursor CURSOR
    DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
    INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B E:\BackupsTest\*.bak'
    SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell
    OPEN @FilesCmdshellCursor
    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @cmd NVARCHAR(MAX) = 'RESTORE DATABASE [' + SUBSTRING(@FilesCmdshellOutputCmd, 0,
    CHARINDEX('.', @FilesCmdshellOutputCmd)) + '] FROM DISK = N''E:\BackupsTest\' + SUBSTRING(@FilesCmdshellOutputCmd, 0,
    CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.bak'' WITH FILE = 1, NOUNLOAD, STATS = 10'
    EXEC(@cmd)
    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
    END

    I found this code which restores all backupfiles from a directory to a single server. Does not work due to folder not found issues. How can I restore all databases :- all mdfs to a single folder and all ldfs to a different single folder using the above code.

    I need to capture the initial logical names using FILELISTONLY and then use MOVE to move the mdfs to one folder and ldfs to another respectively.

    Thanks

    • This topic was modified 1 month, 3 weeks ago by  mtz676.
  • Just use powershell DBATools ( free modules ) DBARestore

    $File = Get-ChildItem c:\backups, \\server1\backups
    $File | Restore-DbaDatabase -SqlInstance Server1\Instance -UseDestinationDefaultDirectories

    Just keep in mind the target server service account needs access to the backup files

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • DECLARE @backup_path nvarchar(300);
    DECLARE @restore_path nvarchar(300);
    DECLARE @cmd nvarchar(1000);
    DECLARE @file_list TABLE (backup_file nvarchar(400));
    DECLARE @backup_file nvarchar(300);
    DECLARE @Table TABLE (LogicalName nvarchar(128),[PhysicalName] nvarchar(128), [Type] varchar, [FileGroupName] nvarchar(128), [Size] nvarchar(128),
    [MaxSize] nvarchar(128), [FileId]nvarchar(128), [CreateLSN]nvarchar(128), [DropLSN]nvarchar(128), [UniqueId]nvarchar(128), [ReadOnlyLSN]nvarchar(128), [ReadWriteLSN]nvarchar(128),
    [BackupSizeInBytes]nvarchar(128), [SourceBlockSize]nvarchar(128), [FileGroupId]nvarchar(128), [LogGroupGUID]nvarchar(128), [DifferentialBaseLSN]nvarchar(128), [DifferentialBaseGUID]nvarchar(128), [IsReadOnly]nvarchar(128), [IsPresent]nvarchar(128), [TDEThumbprint]nvarchar(128),
    snapshoturl nvarchar(128)
    );
    DECLARE @LogicalNameData nvarchar(128);
    DECLARE @LogicalNameLog nvarchar(128);
    DECLARE @user_mode nvarchar(200);

    SET @backup_path='E:\BackupsTest\';
    SET @restore_path='E:\Restore\'

    SET @cmd ='DIR /b ' + @backup_path;
    INSERT INTO @file_list(backup_file)
    EXEC MASTER.SYS.xp_cmdshell @cmd;

    SELECT @backup_file = MIN(backup_file)
    FROM @file_list
    WHERE backup_file LIKE '%.bak';

    WHILE @backup_file IS NOT NULL
    BEGIN
    DELETE FROM @Table;
    INSERT INTO @Table
    EXEC('
    RESTORE FILELISTONLY
    FROM DISK=''' + @backup_path + @backup_file + '''')

    SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
    SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

    --SET @user_mode='ALTER DATABASE ' + @LogicalNameData + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE';
    --EXEC(@user_mode);
    SET @cmd='RESTORE DATABASE '
    + @LogicalNameData
    +' FROM DISK='''
    + @backup_path + @backup_file
    + ''' WITH REPLACE'
    +', MOVE ''' + @LogicalNameData + ''' TO ''' + @restore_path + @LogicalNameData + '.mdf'''
    +', MOVE ''' + @LogicalNameLog + ''' TO ''' + @restore_path + @LogicalNameLog + '.ldf'''
    ;
    EXEC (@cmd);

    PRINT @cmd;

    SET @backup_file=(SELECT MIN(backup_file)
    FROM @file_list
    WHERE backup_file>@backup_file);
    --SET @user_mode='ALTER DATABASE ' + @LogicalNameData + ' SET MULTI_USER';
    --EXEC(@user_mode);

    END

    SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D') <---- If more than 1 mdf file it fails to work as it results more than 1 value.

    .How can I get this to work for multiple files...mdfs/ndfs files...

    SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')

    • This reply was modified 1 month, 2 weeks ago by  mtz676.
  • Restore-DbaDatabase -SqlInstance server1\instance1 -Path Z:\Backup -DestinationDataDirectory Z:\Restore -DestinationLogDirectory Z:\Restore

    Restore-DbaDatabase : The term 'Restore-DbaDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the

    spelling of the name, or if a path was included, verify that the path is correct and try again.

    At line:1 char:1

    + Restore-DbaDatabase -SqlInstance ServerName\InstanceName -Path Z:\Backup ...

    + ~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : ObjectNotFound: (Restore-DbaDatabase:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

    I am running into this error. The service account has access to the folders

  • mtz676 wrote:

    Restore-DbaDatabase -SqlInstance server1\instance1 -Path Z:\Backup -DestinationDataDirectory Z:\Restore -DestinationLogDirectory Z:\Restore

    Restore-DbaDatabase : The term 'Restore-DbaDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1 + Restore-DbaDatabase -SqlInstance ServerName\InstanceName -Path Z:\Backup ... + ~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Restore-DbaDatabase:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException

    I am running into this error. The service account has access to the folders

     

    Did you install-module dbatools ?

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I created the function Restore-DbaDatabase , loaded it and tried executing. Was not aware that the DBAtools module had to be installed.

Viewing 6 posts - 1 through 5 (of 5 total)

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