Vinicius Gonçalves Vinicius Gonçalves - 5 days ago 5
C# Question

SQL Server - Restore backup from varbinary(max) variable backup file

I am trying to create a procedure that should restore a backup of the database.

I would like to pass the backup file through a varbinary (max) type parameter.

It is possible? I could not find any reference to that.

I need to perform this task because the backup file will not be on the server, it will be on a client machine.

I need to call this procedure on code (c#)

EDIT

Today, I've the following code that creates and returns the backup file:

DECLARE @MdfFilePath AS VARCHAR(MAX),
@BackupFileName AS VARCHAR(MAX),
@DbName AS VARCHAR(MAX) = DB_NAME()
SELECT
@MdfFilePath = physical_name
FROM
sys.master_files
WHERE
database_id = DB_ID(@DbName)

SELECT @BackupFileName = LEFT(@MdfFilePath,LEN(@MdfFilePath) - charindex('\',reverse(@MdfFilePath),1) + 1) + @DbName +'_' +
REPLACE(CONVERT(VARCHAR(MAX), GETDATE(), 121), ':', '-') + '.bak'

BACKUP DATABASE @DbName
TO DISK = @BackupFileName
WITH INIT


DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = 'SELECT * FROM OPENROWSET(BULK ''' + @BackupFileName + ''', SINGLE_BLOB) rs'

EXEC sp_executesql @SQL


I need a feature that allow me to pass the backup file back to the server, next, restore it.

Answer

No - SQL Server Engine must be able to "see" the physical file to be restored. The best you can achieve is to set your client PC backup location as a shared drive that the SQL Server Service host Server can map to.

Comments