Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revisionBoth sides next revision | ||
sa28-00 [2019/10/18 10:40] – Justin Willey | sa28-00 [2019/10/18 10:49] – [Moving Blobs Back Into The Database] Justin Willey | ||
---|---|---|---|
Line 13: | Line 13: | ||
- Create a folder on the database machine under which the blob files will be stored. Ensure the server process has full rights to it including the ability to create subfolders. | - Create a folder on the database machine under which the blob files will be stored. Ensure the server process has full rights to it including the ability to create subfolders. | ||
- The most important bit – ensure that the designated folder is backed up, preferably by some kind of live sync arrangement. | - The most important bit – ensure that the designated folder is backed up, preferably by some kind of live sync arrangement. | ||
- | - It is highly desirable that the designated folder is ONLY accessible by the Windows user account that the database engine is run under and the user account used for the sync / backup process. This will reduce the chance of any inadvertent editing / deleting of the blob files. | + | - It is highly desirable that the designated folder is ONLY accessible by the Windows user account that the database engine is run under and the user account used for the sync / backup process. This will reduce the chance of any inadvertent editing / deleting of the blob files, and can help provide protection against ransomware attacks. |
- Set params.BlobExternalRootFolder to the path to the above folder - for example:< | - Set params.BlobExternalRootFolder to the path to the above folder - for example:< | ||
- Set params.BlobExternalStorage to 1 ie:< | - Set params.BlobExternalStorage to 1 ie:< | ||
Line 30: | Line 30: | ||
====Moving Blobs Back Into The Database==== | ====Moving Blobs Back Into The Database==== | ||
- | To reverse the process, change the setting of BlobExternalStorage - new and edited blobs will now be stored internally. Again BlobRelocate will move the existing blobs for you. | + | To reverse the process, change the setting of BlobExternalStorage - new and edited blobs will now be stored internally. Again BlobRelocate.xml will move the existing blobs for you. |
====BlobRelocate Job==== | ====BlobRelocate Job==== | ||
Line 81: | Line 81: | ||
DECLARE " | DECLARE " | ||
-- create temp table to hold errors | -- create temp table to hold errors | ||
- | DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue(" | + | DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue(" |
+ | " | ||
-- regularise Standard and Alternative locations | -- regularise Standard and Alternative locations | ||
SET " | SET " | ||
Line 115: | Line 116: | ||
set " | set " | ||
if exists (select * from sp_list_directory(" | if exists (select * from sp_list_directory(" | ||
- | then update BlobStoreCheckIssue set " | + | then update BlobStoreCheckIssue set " |
- | else update BlobStoreCheckIssue set " | + | |
+ | else update BlobStoreCheckIssue set " | ||
+ | | ||
end if; | end if; | ||
END FOR; | END FOR; | ||
- | select " | + | select " |
+ | | ||
+ | | ||
+ | | ||
END; | END; | ||
</ | </ | ||
Line 151: | Line 157: | ||
-- Find the files in the file system | -- Find the files in the file system | ||
INSERT INTO BlobFiles(" | INSERT INTO BlobFiles(" | ||
- | SELECT ' | + | SELECT ' |
+ | from sp_list_directory(@AlternativeLocation, | ||
CREATE INDEX A ON BlobFiles(" | CREATE INDEX A ON BlobFiles(" | ||
-- select those files only listed in the file system | -- select those files only listed in the file system | ||
SELECT FilePath , | SELECT FilePath , | ||
- | | + | |
+ | | ||
END; | END; | ||
</ | </ | ||
Line 164: | Line 172: | ||
which gives a result like: | which gives a result like: | ||
- | ^FilePath^FileSizeInKB^Created^Modified^Accessed^ | + | ^FilePath^ FileSizeInKB^ Created^ Modified^ Accessed^ |
|i: | |i: | ||
|i: | |i: |