What does it mean that BACKUP TABLE / RESTORE TABLE are not "yet" implemented?

Say you are one of those users who have been using SQL Server since 6.x days. And say you remember that in those versions there was the possibility to backup or restore a single table. Let’s try hard to imagine your memory is good enough to be able to recall the exact syntax you used to accomplish that task. And finally, let’s also imagine you’re a perseverant person who has been testing it on every new major version of SQL Server released since 7.0, to only confirm whether it was supported again, finally dropped off or still in "not yet implemented" status. :-)

Well, coincidentally I know somebody who is a Microsoft MVP and works in Solid and accumulates all those attributes, and this person asked me to investigate why SQL Server continues raising a 3015 exception, after nine years since those four major releases (7.0, 8.0, 9.0 and almost 10.0) have seen the light.

The point is that if you attempt running either of the following two TSQL statements:

BACKUP TABLE ThisTable TO DISK = ‘c:\dummy.bak’


RESTORE TABLE ThisTable FROM DISK = ‘c:\dummy.bak’

you get the following errors, respectively, back from the server:

Msg 3015, Level 16, State 4, Line 1
BACKUP TABLE is not yet implemented.

Msg 3015, Level 16, State 3, Line 1
RESTORE TABLE is not yet implemented.

It happens that starting with SQL Server 7.0 SQL Server’s T-SQL syntactic parser/analyzer continues accepting the BACKUP TABLE / RESTORE TABLE syntax, and we create specific command nodes in the query tree for those two statements, which we even compile to specialized compiled statement classes. But, in the end, when the relational engine invokes the actual execution of the compiled statement, there is no code implementation in there apart from the raising of the 3015 exception.

I approached the PMs in the Relational Engine team to see what were the future plans around this area, and it happens there are no plans to implement this. In fact, I’ve just filed a Work Item for somebody to finally remove that code in a future release and simply consider those two constructs syntactically incorrect.

Was this post helpful, I’m hundred percent positive it was not, but I hope it was, at least, entertaining. :-)

Comments (2)

  1. WesleyB says:

    Oh yes, I remember the sweet days of SQL Server 6.5.  I also remember using the RESTORE TABLE functionality which was nice for development purposes :-(

  2. dinah says:

    notes about restore, back up,and archive