How far has my backup or restore got?

I had a backup that was hanging: eventually I ended up restarting the server. It wasn't running through the GUI of SSMS, though. It was running from a process on the server, some 3rd party software. Stopping the software didn't stop the backup - it wouldn't. It's a file operation, and these things are pretty-much unkillable within SQL. Stopping the backup was possible solely by restarting the Full Text Search Engine on the Server (and, because there were two instances of SQL, both instances of FTE had to be restarted). However, I didn't quite twig this early enough in the process. It proved impossible to stop SQL because the backup was hanging. Eventually we powered down the server. However, I could tell how far the backup had got. A very nifty bit of script:

SELECT r.session_id,r.command,

CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],

CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(

SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000

ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

It's based on the sys.dm_exec_requests view, so works for SQL 2005 and above. The code was here but had a slight typo which meant it wouldn't work for restores in the first instance.

It still doesn't solve the problem of SQL sitting on 100% for a restore for an age and a bit - this is due to the way SQL restores work. First, they restore the data to the files, in the data-copy phase: this is the bit that racks up to 100%. Then, any committed transactions that were present in the transaction log at backup time are rolled forward, and any incomplete transactions are rolled back. This is the redo and undo phase, and SQL doesn't record the progress of this. If the log file is particularly large, and the database is in SIMPLE recovery, this can take quite a while to work through...

Sometimes, it ought to be obvious....

Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.

At which point, the backup job, created via BlackBaud, failed.

First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.

Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.

Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.

It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.

Backups are now running.