Code Optimization

Interesting things about software development and code optimization

Remote SQL Backup to local PC

UPDATED: May 2019


Hello friends,


Each of us had issue with remote SQL backup files. Not with files itself but actually how to get bak files from remote sql server to local PC.

I googled a lot about this question (and I'm sure you did it as well) and did not find any solution. To be honest, there was no way to do it till yesterday :)

Yesterday, I did face with this problem again and seems I did find reliable solution or at least a chance to do it but have to say that it is not 100% working solution, it is about 99% and I will describe why at the end of this post.

So, in general it looks like this:

- execute an sql script to make backup of database;

- create a temp db with table and column of type varbinary type;

- get the *.bak file and insert it into the temp table;

- stream this row to your local pc and save as file;

- drop temp table and db;


That's it. Sounds like not very complex task but I can say that there may be some problems and you will have to solve them, and some problems even may be not possible to solve and then you are in the 1% who is not luck :(


Now lets take a look into each step more precisely, so first step is to create backup. But there are some problems, first problem is that any binary type field can include up to 2^31-1 bytes that is almost 2GB - 1 byte in size, so we will split our backup onto a few files, and there is the second problem - SQL Server supports splitting up to 64 files. So if our backup size is more than about 128GB I think that would be not possible :(

Ok, we have a few GB database and it is enough to split it onto up to 64 bak files (even less than 64), lets do it.

Lets calculate a size of our database: 

USE MyDB

SELECT CAST(SUM(size) * 8 / 1024 AS BIGINT)

FROM sys.database_files;

this will return long value in MB.

Next step is to create our bak files:

BACKUP DATABASE MyDB TO DISK = N'MyDB_tmp_1.bak'

,DISK = N'MyDB_tmp_2.bak'

,DISK = N'MyDB_tmp_3.bak'

,DISK = N'MyDB_tmp_4.bak'

,DISK = N'MyDB_tmp_5.bak'

WITH NOFORMAT, NOINIT, NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

This will backup our db into default backup location on the server.

Now its time to create temp db and table, or you may use the same db if you have no permission to create a new db:

IF db_id('TempDB') IS NULL

begin

create database [TempDB];

end

else

begin

use master;

ALTER DATABASE [TempDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

drop database [TempDB];

create database [TempDB];

end

use [TempDB];

create table Temp (filename nvarchar(512), [file] varbinary(max));

also we need to know the default backup path:

SELECT TOP 1 physical_device_name

FROM msdb.dbo.backupset b

JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id

WHERE database_name = '{0}'

and backup_finish_date >=N'{1:yyyy-MM-dd}'

and backup_finish_date < N'{2:yyyy-MM-dd}'

ORDER BY backup_finish_date DESC

Now we have everything we need to insert each file into the temp table and download them one by one,

lets insert file:

INSERT INTO [{0}].dbo.Temp([filename], [file])

SELECT N'{1}' as [filename], * FROM OPENROWSET(BULK N'{1}', SINGLE_BLOB) AS [file]

Here we may have another problem - you may get error that you have no permission to BULK INSERT and this is real problem as well, so you will finish here or you may try to upload a web app (if you use web hosting) and code it to add to the table as byte array.

Now everything is ready to download the file, but pay attention its better to use streaming instead of default batch reading:

SELECT * FROM TempDB.dbo.Temp WHERE [filename] = N'{0}'

and C# code:

sqlCmd = new SqlCommand("SELECT * FROM [" + tmpDBName + "].dbo.Temp WHERE [filename] = N'" +

string.Format(bakFileName, this.defaultBakPath, sqlConnection.Database, i) + "'", sqlConnection);


sqlCmd.CommandTimeout = sqlConnection.ConnectionTimeout;


SqlDataReader sqldr = sqlCmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

sqldr.Read();

string fileName = sqldr.GetString(0);

System.IO.FileStream file = new System.IO.FileStream(System.IO.Path.Combine(this.localPath, System.IO.Path.GetFileName(fileName)),

System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.ReadWrite);

long startIndex = 0;

const int ChunkSize = 1024 * 32; //32 KB block

byte[] buffer = new byte[ChunkSize];

while (true)

{

long retrievedBytes = sqldr.GetBytes(1, startIndex, buffer, 0, ChunkSize);

file.Write(buffer, 0, (int)retrievedBytes);

startIndex += retrievedBytes;

if (retrievedBytes != ChunkSize)

break;

}

file.Close();

sqlCmd.Dispose();

ok, we have got first file so now we need to repeat the same by deleting each downloaded row in the temp table and inserting next one file and so on.

Finally, delete everything you don't need anymore - temp database and table:

DELETE FROM [TempDB].dbo.Temp

use master; ALTER DATABASE [TempDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; drop database [TempDB];

Voila! You have your remote SQL backup file on your local PC. Cool!

I have written a simple C#.NET application that will do it all for you, but, please, make sure y ou have the following permissions:

- you have BULK INSERT permission or complete admin rights

- your backup files in total size less than 128GB

- some other problems that I have not faced with yet


You can extend it by deleting bak files on a disk, by calculating size of bak file to split it onto less number of bak files, and more.

If you have any comment - you are welcome.

Thank you.


RemoteToLocalSQLBackup.zip (9.4KB)
Source code

1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Comments (8) -

  • dor cohen

    5/3/2016 8:45:20 PM | Reply

    Great site. Plenty of useful info here. I'm sending it to several buddies ans additionally sharing in delicious. And of course, thank you to your effort!

    • okarpov

      5/3/2016 8:59:58 PM | Reply

      Thank you. Nice to hear Smile

  • Bogdan

    11/8/2018 12:07:59 AM | Reply

    My programmer is trying to convince me to move to .net from PHP. I have always disliked the idea because of the costs. But he's tryiong none the less. I've been using Movable-type on various websites for about a year and am worried about switching to another platform. I have heard good things about blogengine.net. Is there a way I can import all my wordpress content into it? Any help would be greatly appreciated!

    • okarpov

      11/12/2018 10:08:39 AM | Reply

      Hi, I don't think there is something to export/import.

  • Osvaldo Hodkinson

    12/8/2018 3:04:27 AM | Reply

    Very interesting info!Perfect just what I was searching for!

  • Levi

    3/17/2019 10:26:27 AM | Reply

    Thank you for this post. Good luck. Al Rubel Rana

  • okarpov

    5/3/2019 1:46:22 PM | Reply

    Updated: Post, T-SQL and C# build/src

  • Ariel Gesing

    5/10/2019 11:15:17 AM | Reply

    My brother recommended I would possibly like this website. He was once totally right. This publish truly made my day. You can not consider just how so much time I had spent for this info! Please visit my website for download free mp3,free music. Thanks!

Loading