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#)
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()
@MdfFilePath = physical_name
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
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = 'SELECT * FROM OPENROWSET(BULK ''' + @BackupFileName + ''', SINGLE_BLOB) rs'
EXEC sp_executesql @SQL
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.