If your SQL Server lives on Windows shared hosting, you probably already know that BACKUP DATABASE ... TO DISK is useless to you. The command runs inside the SQL Server process on the host's machine, so the path is theirs, not yours, and most hosts revoke the permission anyway. Your connection is not a file download channel.
The way around it is a logical backup: connect over the same port your app uses, read the schema, read the rows, write a .sql file on your side. Conceptually trivial. I spent a while building a service that does this on a schedule, and almost none of the difficulty was in the part I expected.
Here is what actually cost me time.
Snapshot isolation isn't there when you need it
A logical backup reads tables one at a time. If rows are changing while you read, Orders can be from 02:00:03 and Payments from 02:00:41, and you now own a file with foreign keys pointing at rows that don't exist.
SNAPSHOT isolation fixes this. It also has to be turned on at the database level first:
sql
ALTER DATABASE MyDb SET ALLOW_SNAPSHOT_ISOLATION ON;
On shared hosting it usually isn't, and you often can't turn it on. What you get instead is:
Msg 3952: Snapshot isolation transaction failed accessing database 'MyDb'
because snapshot isolation is not allowed in this database.
The tempting fix is to run the ALTER DATABASE yourself. Don't. A backup tool that quietly changes settings on someone's production database is a much worse product than one that can't guarantee consistency. Snapshot isolation turns on row versioning in tempdb and has a real cost on a busy database, and on shared hosting that tempdb is shared with strangers.
So: try snapshot, catch 3952, fall back to read committed, and record which one you used on the run. The failure mode you want to avoid isn't the inconsistency itself, it's the user not knowing it happened.
The backup that succeeds and is missing half your tables
This one I did not see coming and it's the reason I'm writing this post.
If you're using SMO's Scripter with EnumScript(), or anything built on it, it scripts what your login can SELECT. A table the login can't read is skipped. The run doesn't fail. You get a green tick, a file, a size, and a backup silently missing whatever it couldn't see.
This is very easy to hit on shared hosting, where permissions get granted per schema over months by different people. Someone grants db_datareader on dbo, an integration later adds a billing schema, nobody updates anything, and your backups have been incomplete since March.
Two things came out of this. First, script off the object list and diff it against what you actually wrote, then show the per table row counts on the run page so an incomplete backup is visible rather than implied. Second, ask for a login with read access to the whole database and say why.
If you're rolling your own, this is the check to write first. It's more valuable than compression, encryption, or scheduling.
INSERT statements are enormous
A rough rule from real databases: the generated script runs several times the size of the underlying data. 250 MB of rows can produce well over a gigabyte of text before compression. Every value gets quoted, every row repeats the full column list, NVARCHAR doubles up.
This blows through whatever limits you assumed. It also means memory, because the naive implementation builds the script in a string and then writes it. Stream it to disk, gzip on the way past, and put a hard ceiling on both the output size and the run duration so a database that grew past what you tested fails fast instead of eating a worker for two hours.
Some columns must not be copied
Computed columns are the obvious one. Script the value and the restore fails, because SQL Server computes it. rowversion and timestamp likewise, SQL Server generates those. Temporal history tables need thinking about separately.
The general shape of the problem: a logical backup has to know which values are yours and which ones the engine produces. Get this wrong and the file looks perfect right up until the moment you try to use it.
Ordering, which everyone underestimates
Tables come out in whatever order you enumerate them. Executing that against an empty database hits a foreign key constraint about four tables in. You need dependency ordering, and for circular references you need to create the tables, insert the data, and add the foreign keys afterwards. Same story for views that reference other views.
The part that actually matters
None of this is verified until you execute the output against an empty database and point an application at it. A .sql file that has never been run is not evidence of anything. Every logical backup tool including mine should be judged on that step, not on the file appearing in your storage.
Restore into a new empty database, check it, and only then touch your connection string. Running an old script over a live database, on the day you're already having a bad day, is how a recoverable incident becomes an unrecoverable one.
I wrote up the full version of this, including the comparison with provider panel backups and desktop tools like SqlBak and SQLBackupAndFTP, and exactly which objects a logical backup does and does not capture:
SQL Server backup on shared hosting without BACKUP DATABASE
That guide is on the site for Backup Ninja, which is the tool I built. It's a hosted service, so I'm obviously not a neutral party, but the guide covers the options that don't involve me too, and the limitations section is honest about what it can't do.
Happy to answer questions about any of the above in the comments, particularly if you've hit the partial backup problem. I'd like to know how common it is.
Top comments (1)
Your step‑by‑step breakdown of the pitfalls when building a SQL Server backup tool without BACKUP DATABASE is crystal clear and easy to follow. If you ever syndicate or cross‑post, this guide would be a great resource for the community on ZyVOP (zyvop.com).