Backing up SQL Server through C #

Question

How easy is it to back up a SQL Server database using C # code?

I see a lot of related questions, but no real answers.

Answer 1

Or: Create a backup copy of the script in Management Studio, put it in a stored procedure, execute the procedure from C # code.

CREATE PROCEDURE sp_backup
AS
BEGIN
    BACKUP DATABASE [YourDatabase] TO  DISK = N'C:\YourPathAndFile.bak' 
    WITH NOFORMAT, NOINIT,  
    NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
END
GO

Answer 2

SQL Management Objects - Microsoft.SqlServer.Management.Smo

He has the methods necessary to complete this action.

Answer 3

SMO ​​ #

, SMO # , .

Answer 4

script, ( "" ), . , - . MSSQL 2008 R2.

-- 
DECLARE @dbName nvarchar(MAX);
SET @dbName = N'AdventureWorks';

DECLARE @backupFileName nvarchar(MAX);
SET @backupFileName = N'C:\Temp\' + @dbName + N'.bak';
--EXECUTE (N'PRINT N''  '+ @backupFileName + '''')
--   
EXEC xp_cmdshell N'mkdir C:\Temp', no_output;
--     
DECLARE @sqlScript nvarchar(MAX);
SET @sqlScript = N'BACKUP DATABASE ['
    + @dbName
    + '] TO  DISK = N'''
    + @backupFileName
    + ''' WITH NOFORMAT, INIT, NAME = N'''
    + @dbName
    + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10';
--SELECT @sqlScript AS backupScript
EXECUTE (@sqlScript);
-- ,   3  ...       
--  
SET @sqlScript = N'
SELECT N''' + @backupFileName + ''' AS [backupFileName], BulkColumn AS [backupContent]
FROM OPENROWSET (BULK ''' + @backupFileName + ''', SINGLE_BLOB) MyFile';
--SELECT @sqlScript AS openRowsetScript
EXECUTE (@sqlScript);
--  
SET @sqlScript = N'EXEC xp_cmdshell ''del "' + @backupFileName + '"'', no_output';
EXECUTE (@sqlScript);

:

    backupFileName  backupContent
C:\Temp\AdventureWorks.bak  0x54415045000003008C000E01000000000000000000000000000...

Answer 5

, .

2 , , SQL, ,

T-SQL ADO.Net. Msdn, ,

Answer 6

If you want to work with a byte stream in C #, for example, compressing the stream before writing to disk, you can see the code from my project, a compressed backup of SQL Server . It has a small VDI virtual machine (SQL Server API) written in C ++ that faithfully displays each VDI option for .Net, and the rest of the (main) code is written in C #.