Restore

Question

Restore

SSCrazy Eights

Points: 9212

February 28, 2024 at 4:47 am

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 8 months, 4 weeks ago by mtz676.

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

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

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

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 8 months, 3 weeks ago by mtz676.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 3

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

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 ?