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:
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
Inspired by
