I have a few remote databases, hosted at my web hosts. For mysql, I use periodic mysqldump and for MSSQL, I use bcp to back them up. How do I validate those backups? How do I make sure that the backup was not partial (its done over the public network).
+1
A:
Write a small PHP script (or similar) that prints the number of records in each table, and install it onto your websites. When you download your backups, load them into a local database and run the same script locally against that database, comparing the results with the ones out on the web.
RichieHindle
2009-06-06 22:44:05
Additionally, the script can make some data sanity checks and compare the results (provided than you don't already have all the possible checks in database constraints).
che
2009-06-07 11:24:03
+1
A:
In SQL Server you can use the RESTORE command with the option VERIFYONLY in order to validate the contents of a database backup file.
See the following Books Online reference for details:
http://msdn.microsoft.com/en-us/library/ms188902.aspx
Further considerations for SQL Server Backups, it is considered good practice to perform a DBCC CHECKDB of your database prior to performing a database backup, in order to ensure/validate the integrity of the database data. This may not be practical however, dependent on the size of your database.
Books Online Reference: DBCC CHECKDB
Performing a CHECKSUM as part of a BACKUP DATABASE operation is also considered a good practice.
See Books Online: BACKUP DATABASE
John Sansom
2009-06-06 22:47:21
does that work with backups taken with bcp? I have user permissions to the database, don't have the "database backup file".
Saurabh
2009-06-06 22:54:34
BCP (Bulk COPY Utility) is a program for importing/exporting data to/from SQL Server. It is not appropriate for/capable of performing a a true database backup per say, however you can of course use it to take a full copy of your table data.
John Sansom
2009-06-06 22:59:50
For details of using BCP see: http://msdn.microsoft.com/en-us/library/ms162802.aspx
John Sansom
2009-06-06 23:00:26
thanks a lot for the clarification. My database is hosted at a web host, and I don't have access/rights to real SQL database backup tools. BCP is the only option to get the data off-site.
Saurabh
2009-06-06 23:03:49
""It is not appropriate for/capable of performing a a true database backup per say"" --> I did use bcp successfully to export data from one web host and then transfer it to another when I was changing the hosting company. So, for a small database that I have, bcp is "good enough". It may not be good for large transactional database, but I just add 8-10 new rows per day.
Saurabh
2009-06-06 23:06:38
Hi Saurabh, absolutely if you are solely interested in copying the table data alone then BCP will be just fine for your need. If on the other hand you are looking to take a complete backup of your database i.e. to include things such as file structure/stored procedure/function/trigger source code, table relationships/indexes/view definitions/security settings etc. then you should you use the built in T-SQL command BACKUP DATABASE. I hope this answers your query.
John Sansom
2009-06-07 07:24:56
+1
A:
Ultimately, the way to validate a backup is to use it for a restore. The acid test is: can you recreate a fully working database from the backup. Ideally, you'd be able to create it on some machine other than the one where the backup was made - to simulate recovery after the destruction of the machine where the backup was made.
Some DBMS provide tools that allow you to simulate such a recovery.
Jonathan Leffler
2009-06-06 23:42:16
Absolutely, the only way to validate a backup is to use it. Restore to a test server or similar. This provesd the backup, tape or other media, processes etc. I have had situations where the RDBMS has reported valid backups but I could not restore
Karl
2009-06-07 11:25:50