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
ENDI 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 year, 1 month ago by mtz676.
-
Just use powershell DBATools ( free modules ) DBARestore
$File = Get-ChildItem c:\backups, \\server1\backups
$File | Restore-DbaDatabase -SqlInstance Server1\Instance -UseDestinationDefaultDirectoriesJust 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);
ENDSET @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')
-
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