If you ever had a problem with a MySQL Table being foobar because of a human error (or other bug), you might need to be able to restore only 1 single MySQL table.
Restoring an entire Database may cause other data loss, and this is of course something we don’t want.
So, if you have a mysql dump available, you can ‘filter’ our that specific table by using the sed command.
Let say the name of your table is tableToRestore and the file dbdump.sql is the file containing your database backup:
$ sed -n -e '/CREATE TABLE.*tableToRestore /,/CREATE TABLE/p' dbdump.sql > dbdump_tableToRestore.sql
This will copy in the file dbdump.sql what is located between CREATE TABLE tableToRestore and the next CREATE TABLE corresponding to the next table.
You can then adjust the file dbdump_tableToRestore.sql which contains the structure of the table tableToRestore, and the data (a list of INSERT commands).
I am using Tara’s excellent MsSQL Backup script to backup my SQL databases. This is an excellent way to backup SQL DB’s if you have an SQL Express version installed. Heck, I even use this when higher SQL versions are installed (with maintenance plan possibilities and stuff).
On top of that, I needed a script which was able to:
– Move my MsSQL BAK files from a local disk to a network path. This is because the backup script does not allow to backup to UNC paths.
– Clean up older backups on the destination directory.
I found (actually, I already knew) that Robocopy was a very nifty tool to do things like this.
Prepare your destination (backup) drive on your network by creating 2 ‘dummy dir’s:
- 1 actual backup destination folder on your remote fileserver (eg \\bck_svr\SQL_Backup)
- 1 directory to move your older files too ( eg \\bck_svr\delete)
- 1 always directory that acts as a ‘delete mirror’ ( eg \\bck_svr\EMPTY)
After that, just download and install the Resource Kit Tools, and create a BAT script that looks like:
cd "C:\Program Files (x86)\Windows Resource Kits\Tools"
REM Move files older than 4 days to a Delete dir, but create them as empty files which fastens everything up (and also directly clears the diskspace :))
robocopy.exe \\bck_svr\\SQLBackup \\bck_svr\Delete /e /CREATE /MOVE /MINAGE:4
REM Delete the contents of the 'Delete' dir.
robocopy.exe \\bck_svr\EMPTY \\bck_svr\Delete /MIR /E
REM Make a new copy of the Dirictoy to backup.
robocopy "d:\sql_backup" \\bck_svr\SQL_Backup /mov /s