April 25, 2024 at 2:03 am
The below code works for backup files only from local drives.
When replaced with a network path the code just runs through but does not restore any dbs.
SET @backup_path='\\ABCD\E$\BackupsTest\' ; - Does not work. Where is it going wrong ?
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\'; <--- Works
--SET @backup_path='\\ABCD\E$\BackupsTest\' ; <-- Does not work
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
April 25, 2024 at 10:56 am
Yes, it does
April 25, 2024 at 11:32 am
What is the result of the following:
SELECT servicename, service_account
FROM sys.dm_server_services;
April 25, 2024 at 12:28 pm
If I remember well, a regular sqlserver instance needs a startup parameter to be able to handle databases which are hosted on a remote share.
Why on earth do you want to use remote files ? ( io latency / Quiesce / network connectivity / ... )
ChatGPT confirmed my old memories 😉
"In SQL Server 2014 and earlier, trace flag 1807 was required to enable the database engine to create databases on network shares. However, starting from SQL Server 2016, this behavior is enabled by default, and the trace flag is no longer necessary."
Totally irrelevant answer ... you are just storing the .BAK files on a share. That is no problem at all, after you 've granted read auth to the traget instances sql server service account on the given share.
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
April 25, 2024 at 12:44 pm
The below code works for backup files only from local drives.
When replaced with a network path the code just runs through but does not restore any dbs.
SET @backup_path='\\ABCD\E$\BackupsTest\' ; - Does not work. Where is it going wrong ?
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\'; <--- Works
--SET @backup_path='\\ABCD\E$\BackupsTest\' ; <-- Does not work 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
how are you running this, via sql agent?
don’t just check the share permissions on
\\ABCD\E$\
drill down into
\\ABCD\E$\BackupsTest\
and check the ntfs file permissions too.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 25, 2024 at 1:57 pm
What is the actual error code you get when executing the sql statement ?
The service account of the target instance needs read auth on the backup share ( and location ).
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
April 26, 2024 at 8:36 pm
Ok so check the logs you should be seeing error in the restore likely with an OS error number
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2024 at 6:40 am
It's not the sqlagent service account, but the sqlserver service account that needs the auth to get to the file share !!
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
April 29, 2024 at 11:05 am
error number would be helpful
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 4:22 pm
sqlserver service account is running under LocalSystem Account on the destination server
May 1, 2024 at 4:29 pm
sqlserver service account is running under LocalSystem Account on the destination server
I guessed so if agent is running under that too, so have you checked the ACLs at the share level and folder NTFS ACLS on the Security tab.
Also drill down into the subfolder and see whats set in case inheritance has been switched off
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 2, 2024 at 5:22 am
If you cannot switch the sql server service to use a domain account, you'll have to grant the file share to the computer account.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply