DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to Take a Backup of a Table in MySQL

Posted on May 9, 2017 Written by Andy Hayes Leave a Comment

Sometimes, there is a requirement to make a backup of a table in MySQL. For instance, there could be a data update planned, schema change needed or that it is necessary to work with a copy of a table, perhaps for some report.

A possible approach to this is to run a full backup of the database to call upon if something goes wrong. This is feasible but sometimes not practical. The database dump could take some time to complete and if something goes wrong and you need to restore, restoring the dump could equally take some time to complete.

In this post, I will show a couple of ways to take backups of tables in MySQL.

Method 1 – Taking a Backup of a MySQL Table Using LIKE

CREATE TABLE table_backup; 
INSERT INTO table_backup SELECT * FROM table;

This method creates the structure of the table including indexes and then loading the data in via one statement.

The advantages of this method are that makes a backup of the table immediately available with the database. It is however to all extents and purposes just like any other SQL statement by way of consuming disk space on the data drive. So disk space should be a consideration when performing this method.

Method 2 – Taking a Backup of a MySQL Table Using mysqldump

mysqldump -u{backup_user} -p{backup_password} from_db_name table_to_backup > backup_file.sql

Instead of backing up the entire database, a backup of the table can be made using mysqldump.

This is my personal preference if the requirement is just that a backup is needed and that there is no requirement to have the table online like in the first example. The backup is taken using mysqldump and can be directed to a location of choice. Disk space is therefore not a consideration for the data drive, rather it is necessary just for the location being backed up to.

Filed Under: All Articles, MySQL Administration Tagged With: backup and restore, mysql

MySQL Replication Use Cases

Posted on February 6, 2016 Written by Andy Hayes Leave a Comment

In my last post, I summarized when MySQL replication is typically implemented. I now want to expand on that and talk about MySQL replication use cases.

Scaling out using MySQL Replication

Let’s take a typical example, the application is read-heavy versus writes. The server is under heavy read load and you want to reduce that. You have optimized indexes etc all you can but the database is still struggling to keep up with the demand. To alleviate the load, more read requests per second need to be processed.

You now have to make a decision, do you scale vertically with a larger server and hope the problem gets fixed this way or do you scale horizontally and add extra servers?

Scaling vertically with a bigger server may address the problem. More CPU’s, faster disks etc could well remove the bottleneck, however this involves a migration exercise from the old to the new server which can be a lengthy operation.

If the application is supporting a business which is experiencing growth and therefore increases in load, repeating the migrations each time (assuming that the hardware to support it has been invented that is faster than what you already have) is not a viable long term solution.

By scaling horizontally and implementing MySQL Replication, you can add additional servers and load balance read requests across them. Want more read throughput? You can add more replica’s.

I’ve used a read-heavy application as an example here. If you wanted more write throughput, then MySQL replication is not a solution to this problem and some form of sharding/HA cluster solution would be used.

Reducing Impact of Backups Taken By Mysqldump

If you use the out of the box tool for taking backups of your database – mysqldump, you may have noticed that it can cause locking problems as it takes the backup.

One get get around this by adding –single-transaction to the command but if you have a replica of your databases available on another server you may choose to take backups of mysql databases from the slave to take some of the load away from the master server.

After all, in a mysql master slave setup, there could be multiple slaves but only one master. So backing up a slave, rather than the master makes logical sense.

Facilitating Reporting Using MySQL Replication

The business wants information from its data. The data is in the production databases. Running queries against databases for reporting can be heavy and affect performance to the main application due to the need to join many tables and aggregate lots of rows.

So you could schedule your reports to run off peak via some cron or windows task. But wait, the business wants these reports sooner, it doesn’t want to have to wait for this information for many hours. The data analysts need them today!

This is another great reason to setup MySQL replication, the replica can be used for heavy reporting tasks, whether these are reports within the application, data analysts or ETL jobs, the fact that the replica is not used by the production application means these tasks have no impact. Everyone is happy 🙂

Providing Failover/High Availability

It’s worth noting that MySQL replication is not a complete high availability solution due to the master/slave configuration. If the master goes down for example, there is a bit of work needed to promote an existing slave to become the master. For more information on a complete HA solution for MySQL, check out MySQL Cluster

In my mind, when I think high availability, database services are not interrupted at any level. Instead, we could say that MySQL replication provides increased availability or higher availability because of the replica’s that exist which can be used in the event of failover.

For businesses which can suffer some brief interruptions whilst the master is promoted,  MySQL replication increases database availability and fulfills a need. With more investment, availability increases. There is a link here which I am quoting from that summarizes this perfectly;

Each architecture used to achieve highly available database services is differentiated by the levels of uptime it offers. These architectures can be grouped into three main categories:

  • Data Replication.
  • Clustered & Virtualized Systems.
  • Shared-Nothing, Geographically-Replicated Clusters.

In my next post, I am going to look more closely at the replication event types.

Filed Under: All Articles, MySQL Administration Tagged With: backup and restore, mysql

Implementing a Simple SQL Server Backup Script

Posted on October 21, 2013 Written by Andy Hayes 1 Comment

sql server backup scriptI’ve used this simple SQL Server backup script a number of times to backup sql server to a file or network drive. It’s ideal if you are running SQL Server Express for example which does not come equipped with maintenance plans.  This script can be used in conjunction with SQLCMD to backup SQL Server from the command line.

So let’s take a look at this.

What we want to achieve here is a way of executing the same backup database command for each database in SQL Server. The “bare bones” T-SQL used might look like this. In this example, I am using my local drive as the backup destination.

BACKUP DATABASE dbname TO DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\BACKUP\dbname.bak';

I say bare bones because the above command will backup a database to disk for you but there are a wide selection of different options which can be used to customize the backup. For more on that visit this link

We can query SQL Server for a list of the databases installed on the instance. You can filter out the databases which you are not interested in backing up.

SELECT Name FROM sys.databases WHERE Name NOT IN ('Model','TempDb');

So I get back this list..

Name
------------------------------
AdventureWorks2012
master
msdb
ReportServer$SQL2012
ReportServer$SQL2012TempDB
Test

I now want to write a simple loop to run through each of these names and execute the BACKUP DATABASE command producing a file on disk. For this, you could use a CURSOR or a WHILE LOOP. I’ll show you both ways.

Which one you choose is a matter of preference. Note that by default if you backup to the same file on disk, each backup is appended to the backup file. If you want to overwrite your backup files, use WITH INIT. Refer to the link above for more information on the syntax.

Backup SQL Server databases using a cursor

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\'

DECLARE backup_cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN ('Model','TempDB');
OPEN backup_cursor
FETCH NEXT FROM backup_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM backup_cursor INTO @dbname
END
CLOSE backup_cursor
DEALLOCATE backup_cursor

Backup SQL Server databases using a while loop

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)
DECLARE @id INT

SET @backupPath =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\'

SET @id = (SELECT MIN(database_id) FROM sys.databases WHERE Name NOT IN ('Model','TempDB'))
WHILE @id IS NOT NULL
BEGIN
SET @dbName = (SELECT Name FROM sys.databases WHERE database_id = @id)
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
SET @id = (SELECT MIN(database_id) FROM sys.databases WHERE Name NOT IN ('Model','TempDB') AND database_id > @id)
END

(Apologies if this code looks a little messy. I am looking for a better code formatting plugin – any suggestions? 🙂 )

Once you have customized the script to your needs, wrap it inside of a stored procedure. Depending on what version of SQL Server you running, you can either schedule this using SQL Server Agent or via the windows task scheduler by calling SQLCMD.

Backing up SQL Server using SQLCMD at the command line

Start a command prompt and type SQLCMD /? to get the help options up.

Connect to your SQL Server instance using the options available and then run the backup to test that it works ok.

backup sql server command line

You can set up a windows batch file to call SQLCMD. I could add the following line to a batch file and I am now ready to schedule this using windows task scheduler.

SQLCMD -E -S MACHINE\INSTANCE -Q "exec master.dbo.usp_backupDatabases"

Next Steps

It’s important that you get the right plan in place for your server. What I have provided is just a basic script, call it a template if you will. You can customize it to include differential backups, transaction log backups, different file locations, multiple backup sets, multiple files etc.

Have a read of the BACKUP DATABASE documentation to get a idea for what is possible. Communicate with the business to agree requirements, write a backup plan and test whatever code you write thoroughly to ensure that it is meeting the backup plan requirements.

Finally

Backing up using scripts is very handy for editions of SQL Server which do not come equipped with maintenance plans and SQL Server Agent. If you are running one of those editions that does have those features, then you might decide to choose those tools instead. Using backup scripts however still remain highly viable and are preferable to some DBA’s.

Filed Under: All Articles, SQL Server Administration Tagged With: backup and restore, sql server

An Introduction to SQL Server Backup

Posted on June 29, 2013 Written by Andy Hayes 2 Comments

sql server backup databaseBefore I get into this post on SQL Server backup, I must make something really clear without sounding like I am stating the obvious. 😐

Having a fully tested backup and restore plan in place for your SQL Server databases is one of, if not, THE most important job that you have to ensure you get right as a DBA.

Getting it wrong could mean huge financial costs to your company, potentially leaving your companies reputation in tatters and leaving you out of a job.

The important thing to bear in mind is that your database backups are only good if you can restore them correctly. If you do not have a tested disaster recovery plan in place, don’t put it off any longer, get one setup and test it regularly.

SQL Server Backup types

There are a number of different ways to backup your database and I will expand on these in later posts. For now here is a summary:

  • Full – a complete database backup which truncates the transaction log of inactive records
  • Differential – a backup of all of the changed data pages since the last full backup. Usually smaller than a full backup, assuming that not all pages have changed
  • Log– transaction log backup containing all transactions since the last transaction or full backup. Also truncates the log of all inactive log records
  • File – a way to backup individual database files
  • Filegroup – a way to backup a group of files contained inside of a filegroup
  • Copy-Only – a backup which can be taken without disrupting the log chain. Great for taking a copy of a production database for development purposes
  • Mirror – allows you to backup to more than once device simultaneously
  • Partial – similar to filegroup but will backup the primary, all read/write filegroups and optionally, read only filegroups

SQL Server Backup – which recovery model should you choose?

This is an important decision to make when setting up your databases. The recovery model determines whether you can perform point in time recovery or not. It also affects transaction log size.

There are 3 types of recovery model:

  • Full
  • Simple
  • Bulk-Logged

Full recovery model

If your database is enabled with a full recovery model, it means that the transaction log will only be truncated upon completion of a FULL backup or upon completion of a transaction log backup.

This recovery model is necessary for log shipping, database mirroring and transaction log backups to provide point in time recovery.

Point in time recovery is vital if you cannot tolerate much data loss. How often you backup your transaction log will determine how much data you can recover in the event that you have a disaster.

Backing up the transaction log is far superior than setting up differential backups every 30 minutes as a differential will backup all the changed data pages taken since the last full backup. This could take some time to complete and consume lots of disk space.

In comparison, a transaction log backup will simply backup the transactions written to the log since the last log backup – a much quicker and less disk space hungry operation 🙂

It is advised that if you implement the FULL recovery model, that you do also put in place a policy to backup your transaction logs regularly. Failing to do this on a busy system could mean that your transaction log disks run out of disk space very quickly!

Simple recovery model

With simple recovery, the transaction log is truncated on each checkpoint.

What this means is that if a transaction is committed to disk, the space used by that transaction inside of the transaction log, is emptied leaving space within the transaction log file.

If you are not concerned about point in time recovery or any of the other processes associated with the full recovery model, then this is a handy option to enable and requires less management of the transaction logs because the log is always truncating.

Bulk logged recovery model

Finally we have the bulk logged recovery model. For this option, it is still possible to backup the transaction log but it does not permit point in time recovery. The advantage with this recovery model is that it uses minimal logging in the transaction log.

This is not only faster because less is being written to the log but it also means that the transaction log will not grow as large compared with the full recovery model.

If the business requirements allow you to switch from full logging to minimal logging, you might choose to enable this during maintenance windows when index rebuilds are happening. This will help to reduce the transaction log growth and upon completion, switching back to the full recovery model for example.

You can make this switch because the log chain is intact. The bulk logged recovery model will preserve the log chain and not require you do take another full backup. However as the transactions recorded during the time when the database was in bulk recovery are only minimally recorded, you cannot restore your database to a point in time when the bulk recovery model was enabled.

Only a restore point before or after the recovery model was enabled will be possible for point in time recovery.

As the transactions could suffer data loss under this recovery model, it is advisable to only make this switch during periods when the users are not updating the database or when you are able to recover the transactions which were otherwise lost by some other means. So for example, you might have an SSIS package importing a bunch of files which could easily be re-imported.

Scheduling and retention of your SQL Server backup

It’s possible to implement scheduling of your backups using the SQL Server Agent. What you choose to implement will depend on the data retention requirements for your business. A very simple example of a schedule might look like this:

  1. Take a weekly FULL backup
  2. Take daily DIFFERENTIALS
  3. Take regular transaction log backups, for example every 30 minutes.

This schedule might repeat each week to a point when the backups are a certain age, they would no longer be kept in storage and re-used. This will depend on your business requirements.

In SQL Server, backup schedules can be put in place which are customized to your needs and available backup capacity. The available options provide great flexibility as everything can be done at the database level.

You may have databases which are critical to the business which need frequent backups and you may also have databases which do not change, for example read-only databases. You may only back these up very rarely in this instance but retain those backups for long periods of time.

Backup storage – what to use

I can think of some options:

  1. Backup to local disk
  2. Backup to network share or SAN
  3. Backup to tape
  4. Combination of disk/network/SAN plus tape – you may keep aged backups on tape and recent backups on disk for example.
  5. Backup to cloud – there are companies now which provide this service.
  6. Backup to FTP location

Ensure that whatever option or options to decide upon that there is redundancy there to help protect your backups.

A word on backing up to local disk

Where I work, we have a 3rd party solution which takes care of our backups. It authenticates to the SQL Servers and takes regular backups to a large SAN. Those backups are then copied to tape and the tapes are taken offsite.

The key to this is that the backups are stored external to the SQL Servers and not locally. The issue with local backups is that if your server dies for any reason, you can potentially lose your backups and you are in trouble.

You can backup locally and it could perform better than backing up over a network for example but you’ve got to make sure that you have copies of those backups made somewhere else to safeguard your data.

A word on backing up to tape

Similar to local disk backups, these backups are only as good as the tapes and device you put them on. So ensure you have copies made of your backups. I personally would not put a backup solution in place which consisted purely of tape backups.

A word on database snapshots

It’s possible to take a snapshot of a database in SQL Server. This is a great way of taking a cut of the database which you might choose to do some reporting on for example. It’s also possible to revert a database back to a snapshot however do not rely on snapshots for your database backups.

Snapshots exist on the same database server as the source database. If you lose your drives, you lose your source database and database snapshots! You may as well then go clear our your desk and hand in your security pass. 😐

SQL Server backup software

Now it would be foolish of Microsoft not to supply you with the tools to do this and thankfully, it has long been possible to completely manage your backup and restores using SQL Server Management Studio and the SQL Agent for scheduling.

The tools which are available to you do a pretty good job but as your backup requirements grow, you may decide it is time to invest in some backup software to help manage your SQL Server backups.

I’m going to be looking at some backup software for SQL Server in a future post.

Summary

I hope this provides you with some information on where to start with your SQL Server backup plan. In future posts, I will be going into more of the how to aspects of running backups and restores inside of SQL Server. For more information you can visit this link

Filed Under: All Articles, SQL Server Administration Tagged With: backup and restore, sql server

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close