Developing some industrial WinForms application for some industrial setting, I wanted to provide users of our software with a convenient way to back up the database the software uses (to send it to developer for investigation of any issues that might arise, or just to keep it at hand in case something goes wrong).
The assumptions (more or less checked to be true in our environment):
- The GUI of software works on some server, accesing the DB over an SQL connection
- The user of the GUI is the person most likely to report the problems; he is an enginier, not an IT guy
- We store no confidential data - anyone with the right to use the database is ok to read it all (not so ok with writing it all over)
- The database server administrator is not readily available (there is no dedicated database administrator; most of the time the thing just works, and when it does not, someone comes to fix the problem - and that "someone", having a rather wide area of responsibility, would like to have less things to care about, not more)
- Some computers this is to work on might be a part of domain, some not; SMB shares might exist, someone might know and/or periodically change the password, network names, all sort of stuff.
The solution I came up with is to back up the database onto the same computer the GUI is running on, over the same SQL connection GUI accesses the database through. Restoring is still manual.
The following is the SQL part. So, what do you say about it?
CREATE PROCEDURE get_backup @make bit = 1, @download bit = 1, @delete bit = 1 AS BEGIN SET NOCOUNT ON; DECLARE @count int, @part int, @sql nvarchar(max) DECLARE @files table (n int, pathname nvarchar(2000), path nvarchar(2000), quoted_pathname nvarchar(2000), quoted_name nvarchar(200)) IF @make = 1 BEGIN DECLARE @size int SET @size = (SELECT SUM(total_pages)/128.0 AS megabytes FROM sys.allocation_units) SET @count = (@size + 1000) / 1000 SET @sql = 'BACKUP DATABASE ' + QUOTENAME(DB_NAME()) + ' TO ' SET @part = 1 WHILE @part <= @count BEGIN SET @sql = @sql + 'DISK = ''' + REPLACE(DB_NAME(), '''' ,'''''') + '-part' + RIGHT('0000' + CAST(@part AS nvarchar(4)), 4) + '.tmp-bak''' IF @part < @count SET @sql = @sql + ', ' SET @part = @part + 1 END SET @sql = @sql + ' WITH FORMAT, COPY_ONLY, STATS = 1' EXEC(@sql) END INSERT INTO @files SELECT TOP 1 WITH TIES family_sequence_number, physical_device_name, REVERSE(RIGHT(REVERSE(physical_device_name), (LEN(physical_device_name) - CHARINDEX('\', REVERSE(physical_device_name), 1)) + 1)), '''' + REPLACE(physical_device_name, '''' ,'''''') + '''', '''' + REPLACE(REPLACE(REVERSE(LEFT(REVERSE(physical_device_name), CHARINDEX('\', REVERSE(physical_device_name), 1) - 1)), '.tmp-bak', '.bak'), '''' ,'''''') + '''' FROM msdb.dbo.backupmediafamily WHERE physical_device_name LIKE '%.tmp-bak' ORDER BY media_set_id DESC IF @count IS NULL SET @count = (SELECT COUNT(*) FROM @files) IF @count <> (SELECT COUNT(*) FROM @files) RAISERROR('Count mismatch', 16, 0) IF @download = 1 BEGIN PRINT CAST(@count AS nvarchar(4)) + ' files per backup.' SET @sql = ''; SET @part = 1 WHILE @part <= @count BEGIN SET @sql = @sql + (SELECT 'SELECT ' + quoted_name + ' AS name, BulkColumn AS data FROM OPENROWSET (BULK ' + quoted_pathname + ', SINGLE_BLOB) AS a' FROM @files WHERE n = @part) IF @part < @count SET @sql = @sql + ' UNION ' SET @part = @part + 1 END EXEC(@sql) END IF @delete = 1 BEGIN DECLARE @path nvarchar(2000) SET @path = (SELECT TOP 1 path from @files) EXECUTE xp_delete_file 0, @path, 'tmp-bak', '2080-01-01', 0 END END