I have setup both of my Servers with Filestream for T-SQL access (and my DBs). I created tables on both Servers:
CREATE TABLE mytable(
[UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT (newid()),
[Data] [varbinary](max)FILESTREAM NULL)
I am inserting my File (.bak File, Backup of my DB 5,2GB) as:
INSERT table ( Data )
SELECT * FROM OPENROWSET (BULK 'F:\myDB.bak', SINGLE_BLOB) AS X
I insert it on a same table over Linked Server in my 2nd Server. Then i want to create my .bak file again:
DECLARE @fPath varchar(400) = 'F:\myDB.bak'
, @init int,
@data varbinary(max)
SELECT @data = (Select data from mytable)
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, @data; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources
But my File only comes out as 1,6GB. and after trying to restore it i get:
Msg 3013, Level 16, State 1, Line 9 RESTORE DATABASE is terminating abnormally. Msg 3287, Level 16, State 1, Line 9 The file ID 1 on device 'F:\myDB.BAK' is incorrectly formed and can not be read.
What am I doing wrong?
[varbinary](max)
without having FILESTREAM enabled but i have Files that are larger than 2GB