It was last year that I was forcefully reminded that regular backups are not always enough. I was working on the database of a live website. I had attached to it remotely across the internet via Query analyser, and was deleting a customer. It was after midnight at the end of a long working day, and I typed in..

DELETE FROM Customer

       WHERE customer_ID=23745

It was part of a long process of stitching and mending, and the Query window was full of code. I thought I'd highlit the whole of the above code, but I hadn't. I clicked on the Execute button and then saw, to my horror, that I'd deleted the records of several thousand customers. I feel sure I'm not the only one who has ever done this.

Fortunately, as a graduate of the school of hard knocks, I'd just synchronised the live system with the test server before starting work, (using SQL Data Compare) so as to give myself a rapid back-out if things went wrong. Within a minute or two, everything was mended. It did, however, frighten me sufficiently into being extra especially methodical in performing this sort of operation. I had, of course, done a regular backup, but the restore process would have taken the server to the point it was at an hour previously, and I'd have lost data. It would also have been a protracted and tiresome process.

No wonder DBAs dream of Object-level restores. However, being able to restore a table at a time isn't always ideal.

The whole point of a SQL Server backup is that is backs up the data to a point in time in the past. When you restore, you restore the database, you restore it, as an intricate, interdependent whole, to its state at a particular time. Doing an 'Object-Level Restore' to a normalised relational database is a paradox.

If you are careful, then database objects can be backed up and restored perfectly easily. SQL Server already has the tools to do this, although in the typical Microsoft 'roll-your-own' manner. For those of you who don't like creating their own Object-level backup and restore tools, A TSQL script of mine, called spScriptObjects, is already given on this site in More Database Administration and Development Automation using DMO , which will back up all your database objects as build scripts. These can then be archived or put into source control. However, to save the data in the tables requires a separate operation. I generally prefer to backup database objects into other databases rather than putting them in disk files. To this end, there is also a script there, called spDBTransfer which will copy an entire database between servers, or as much or as little as you want. You can use it for copying an entire database or just a few objects. Obviously, it can be used for object-level backups and restores, where the data and schema is held in a second server. Taking copies of the data from tables in a live production system, and subsequently doing an 'Object-Level restore' can be tricky if values are changing rapidly and there are a large number of foreign keys. This is because the entries represented by the foreign keys in your tables may, in the interval taken by the copying procedure, have been changed or deleted, thereby destroying the referential integrity of any database that is recreated from the data. If this is not the case, then a simple BCP operation can be put in place to back up the contents of your base tables. There are plenty of other ways of writing data out to a file, but the advantage of BCP is that it is dead simple, can be executed from the Windows Scheduler and would not depend on the SQL Server Agent being operational.

Here is a procedure that will create a script for backing up the base tables, which can be copied and pasted into a DOS batch command file, which can then be put in the Windows Scheduler, and 'run as' a user with the necessary admin rights, under Windows Authentication.

CREATE PROCEDURE spBCPTableScript

@InOrOut VARCHAR(5)='out',           --in to BCP the files in

@BackupPath VARCHAR(255)='c:\backup\'--your backup directory

/*

spBCPTableScript 'in'

*/

AS

SET nocount ON

DECLARE @cmd VARCHAR(8000)



IF @inOrOut NOT IN ('in','out')

       BEGIN

       RAISERROR 

               ('
the variable @inOrOut must be set to either ''in'' or ''out''',16,1)

       RETURN 0

       END

IF @inOrOut='out'

       SELECT 'bcp ' + DB_NAME() + '..' + name 

               + ' '+@InOrOut+' "'+@BackupPath + DB_NAME()

               +'-'+name + '.bcp" -S' + @@servername + ' -N -T'

FROM sysobjects WHERE xtype='U'

ELSE --we need to check to see if there are any identity columns

       SELECT 'bcp ' + DB_NAME() + '..' + name 

               + ' '+@InOrOut+' "'+@BackupPath + DB_NAME()

               +'-'+name + '.bcp" -S' + @@servername + ' -N -T'

               + CASE WHEN c.id IS NULL THEN '' ELSE ' -E' END + ''''

       FROM sysobjects o 

               LEFT JOIN (

               SELECT DISTINCT id 

               FROM syscolumns 

               WHERE status & 0x80 <> 0) c

                       ON o.id = c.id

       WHERE o.xtype = 'U'

For a production server, which is unlikely to add or remove a table, this will work well, but is short of any reporting of errors. There is also the problem of remembering to change the batch file every time there is a change in the structure of the database. If the data has any sensitive information in it, then you must, of course, protect the backup directory you specify.

If you have SQL Compare, and SQL Data Compare, and your database is a reasonable size, then doing object backups and restores 'comes out of the box'. To script out your database, you can set it to just script out the changes from a snapshot, or the entire database. You can do the same with Data too. The 'Pro' version will allow you to set up the backup process from a batch file.

DMO and SMO allow you to encapsulate the BCP processes in the BulkCopy object. This is used as a parameter to the ImportData method of the Table object and the ExportData method of the Table and View objects. With this, you have a way of managing table and view backups from a central point, and you do not have the potential problem of leaving user_Ids or passwords on all the servers in your care. You can, very easily, write out the data in all your tables or views, to file. The problem, of course, is that the presence of all that raw, unencrypted data is a potential security problem. The directory that receives the data must be secured from access by all but the administrator. I'd suggest that it also has to be zipped up with encryption and password-protected, and copied out via FTP or a temporary network connection to a remote location.

So there is quite a lot of sense to make copies of ones database objects regularly, as well as before doing any operation that has a risk attached to it. However, I'd always like to consider this as a separate operation to doing backups, for a different purpose. And I'd consider copying the data to another database, or synchronising them, rather than dumping them out as text files. Whatever you choose, Microsoft have provided a wealth of alternative tools to help to do this.