Extracting BLOB data to files

Trying to get a file out of a BLOB field and came up with this script to do it.

--Execute this once before running the following script.
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;

--GO
--sp_configure 'Ole Automation Procedures', 1;
--GO

--RECONFIGURE;
--GO

declare @init int

declare @file varbinary(max) = CONVERT(varbinary(max),(select FILE_CONTENTS_TX from DCOPS_January.DCOPS_OWNER.TESA_DB_DOCUMENTATION WHERE reference_ID = 44545
AND DOCUMENTATION_ID=413228)
)



DECLARE @FileName AS VARCHAR(50)


select @FileName=SOURCE_NAME_TX from DCOPS_January.DCOPS_OWNER.TESA_DB_DOCUMENTATION WHERE reference_ID = 44545
AND DOCUMENTATION_ID=413228


declare @filepath nvarchar(4000) = N'c:\temp\' + @FileName


SELECT @filepath


EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1;
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @file; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources


Source:




https://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

Powershell in SSMS

I had a restored database of about 300GB and wanted to monitor its progress.  The Stats argument on the RESTORE command will give you some limited feedback but wanted something more.  This Powershell will do a running update every 10 seconds.

import-module sqlps

for(;;)

{

cls

invoke-sqlcmd -serverinstance apcgs0nhqu398mv\privweb -query "select SUBSTRING(text, charindex('[', text) + 1, charindex(']', text) - charindex('[', text) - 1) AS [DB], command, percent_complete from sys.dm_exec_requests cross apply sys.fn_get_sql(sql_handle) where command like 'restore%'"

start-sleep -seconds 10

}

SQL restores

Had to do some SQL Admin work and wanted to put this into my tip list.
The UI in SMSS will generate the script for you but I wanted it for my own
understanding.


RESTORE DATABASE [DCOPS_January]


FROM DISK = N'G:\SQL_Restores_PRIVWEB\DCOPS_OwnerJanuary18.bak' WITH FILE = 1,


MOVE N'DCOPS_Owner' TO N'D:\SQL_Data_PRIVWEB\DCOPS_January.mdf',


MOVE N'DCOPS_Owner_log' TO N'E:\SQL_Logs_PRIVWEB\DCOPS_January_log.ldf',


NOUNLOAD, REPLACE, STATS = 10



GO


This command lets you move the original file names which were backed up to your own name of choice.