Email this page
Send a link

Backup



Script to backup one certain MySQL database

#!/bin/sh

GZIP="/usr/bin/gzip"
DATO=`date "+%d"`
TIME=`date "+%H"`
DATOTIME="${DATO}_${TIME}"
USER="username"
PASS="password"
BASE="database"

FILE="/home/user/backup/db/databse_$DATOTIME.sql"

FLAGS="-u $USER --password=$PASS -c --create-options -e $BASE"

/usr/bin/nice -10 /usr/local/bin/mysqldump $FLAGS > $FILE

/usr/bin/nice -10 $GZIP -f $FILE

if [ "$TIME" -eq "00" ]; then
        /usr/bin/nice -10 /usr/local/bin/rsync -q -e "ssh -p 22" -av $FILE.gz user@domain:/home/user/backup/db/databse_$DATOTIME.sql.gz
fi


And the crontab to go with it
10      */1     *       *       *       /bin/runlock backup-db /home/user/bin/backup-db.sh


This will backup the database to local disk every hour, and the midnight copy will also be sent to a remote host for further archiving and security measures.

Please note that the /bin/runlock utility is a custom script of mine.

Backup full, diff, and log from MSSQL

I use a simple database backup script for this.

It is invoked like this:

"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S (local) -E -i d:\backup\MSSQL\full.sql


Where the file full.sql contains:

sp_BackupDatabase 'database_name', 'F'
GO

Restore differential MSSQL backup to a new database

Usually when I test a backup it happens on the same server as the production database is at. This means I can't and wont use the same database name for restoring. A few options needs to be correct to make this work.

  • First of all, create the new database. This is just easier.
  • Right click and select Tasks / Restore / Database.
  • The "To database" should be the new test database you just created. Leave it like that.
  • Now select "From device" and add the file of the FULL backup.
  • Check "Restore" in the table view.
  • Change to the "Options" page.
  • Check the option "Overwrite the existing database (WITH REPLACE)".
  • IMPORTANT: Change the "Restore As" to the new database files, like d:\db\test.mdf and d:\db\test_log.mdf. Other wise it tries to overwrite the production database.
  • IMPORTANT: Change the recovery state to "Leave the database non-operational, and do not roll back uncommitted transactions. Addtional transaction logs can be restore. (RESTORE WITH NORECOVER)". Otherwise you will not be able to restore the differential backup afterwards.
  • Press OK.

That was the full backup restored. Now on with the differential backup.

  • Again, right click and select Tasks / Restore / Database.
  • Leave the "To database" as it is.
  • Check "Restore" in the table view.
  • Change to the "Options" page.
  • IMPORTANT: Do NOT check the option "WITH REPLACE".
  • IMPORTANT: Make sure that the recovery state is "Leave the database ready to use by rolling back uncommited transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)".
  • Press OK.

Tadaaaah. You have just restored a differential backup to a new database (name).