Why is it such a mission to backup a SQL Server 2008 Express database?

Question

I wouldn't describe myself as afraid of change - but afraid of new technologies? YES INDEED! Technologies from operating systems, to database servers just seem to become bugged, inefficient and backward the further they "progress"

MSDE 2000 (what they might call "SQL 2000 Express" in today's world)

BACKUP [MyDatabase] TO FILE 'c:\backups\mybackup.backup'

SQL 2008 EXPRESS

Wait up! Its a 'user instance' - to back it up we need to attach it to a server instance
Wait up! To attach it we need SQL Management Studio Express (78MB download)
Wait up! When we login to our .\SQLEXPRESS server instance and try to attach our database it gives us an error that literally looks like a bug in our homebrew dev project:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

------------------------------ ADDITIONAL INFORMATION:

Parameter name: nColIndex Actual value was -1. (Microsoft.SqlServer.GridControl)

Can someone explain how to backup a user instance of a SQL Server 2008 Express database in T-SQL code?

(sorry if this comes across like a flame at ummmm, Microsoft - I'm actually a huge fan of theirs. Just really angry about things like this! please don't vote me down...)

Answer 1

SOME KEY TIPS TO NOTE WHEN TRYING TO ACHIEVE USER INSTANCE BACKUP

a.) Connecting

Your connection string should look like this:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True;Database=MyDatabaseAlias

It is essential that your connection string gives the connection an alias Database=MyDatabaseAlias - this alias cannot be duplicated concurrently on the same machine or your connection may fail.

b.) Backing Up

As pointed out above, the Transact SQL to backup a database is the same on SQL MSDE/2000/2005/2008/R2 - once you have your database attached and aliased!

BACKUP DATABASE MyDatabaseAlias TO DISK = 'c:\backups\mydatabase_20101117.backup'

Whats truly amazing is the bull$h!t errors you can get because your connection string doesnt have the alias Database=MyDatabaseAlias part.

e.g. Unable to open the physical file 'c:\Code\MyProject\App_Data\MyDatabase.mdf' Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".BACKUP DATABASE is terminating abnormally.

c.) Restoring

USE [master]; RESTORE DATABASE MyDatabaseAlias FROM DISK = 'c:\backups\mydatabase_20101117.backup'  WITH REPLACE

Do not forget the all essential USE [master]; at the beginning of this statement (and note that its all on one line for those executing the command from a DataContext or similar) If you do, it wont be able to overwrite the existing database because you'll still be connected to it.

Once again, the assortment of totally misleading errors you might receive here, due to an invalid connection string, is endless.

Answer 2

Um, if it's a user instance, then the simplest backup strategy is to copy the file. (whilst it's not connected to SQL Server).

If you need a more comprehensive backup strategy (e.g. transactional backups), then you really should be looking at a more comprehensive database (e.g. a "normal" one attached to a full SQL Server instance)