Click here to monitor SSC
  • Av rating:
  • Total votes: 26
  • Total comments: 9
Grant Fritchey

SQL Server 2014 Backup Basics

02 January 2014

There is nothing mysterious about SQL Server backups. They are essential, however you use your databases. Grant Fritchey explains the basics of database backups and restores with SQL Server 2014.

Getting SQL Server backups right is the single most important thing that a data professional can do for the business they work for. Inappropriate, missing, or untested backups can actually cause the entire business to fail. That issue makes backups a fundamental business problem, not a technical one. However, the solution to the business problem is through technical means, the various backup processes offered by SQL Server 2014.

Backups

A backup is nothing more than a copy that is created as a type of insurance policy in the event that the original goes away. The same applies to SQL Server database backups to an extent, but database backups are not simply a file copy. They are a very specific type of copy that is aware of the transactional nature of SQL Server. This copy will be created in such a way as to deal with transactions that are ‘in flight,’ that have not yet been completed. Simply copying the files that define a database will not deal with transactions and can lead to serious data corruption. For this reason, you should, in most circumstances, use the native backup processes or, third party tools that work directly with the native processes such as Red Gate SQL Backup. There are some large scale systems that will need to work with non-standard backup mechanisms such as SAN snapshots. These are far outside the scope of this article.

Most of the fundamental backup mechanisms and some of the more advanced ones are covered in this article. However, database backups present a very complex topic. This article will ensure that you have the information you need to get backups in place on your systems but there’s even more to learn about the processes within SQL Server that underlie the backup.

In order to get started with SQL Server backups, you first need to first understand the recovery models offered by SQL Server.

Recovery Models

All backups within SQL Server are affected by transactions and transaction log management. The methods of transaction log management are controlled by the database Recovery Models. There are three:

Simple: All transactions are written to the log. After a checkpoint, all committed transactions are automatically removed from the log, also known as truncating the log. No point-in-time recovery is possible.

Bulk-logged: All transactions are written to the log except certain types of minimally logged operations such as BULK INSERT and INSERT INTO. The log can only be truncated with a log backup operation. Point-in-time recovery is possible as long as no minimally logged operations are in any of the required log backups.

Full: All transactions are written to the log. The log can only be truncated with a log backup operation. Point-in-time recovery is possible.

While the Bulk-logged Recovery Model can somewhat reduce the size of logs, the limitations it places on point-in-time recovery make it less viable for most databases on most systems. The overriding majority of people use either the Simple or Full-Recovery Model. I’m going to focus on the two primary models within this article.

It is the requirements of the business that determine whether you need the Full or Simple Recovery model. You may need to work with the business to define the Recovery Point Objective (RPO). The RPO more or less defines the amount of information that the business could tolerate losing. Within Simple Recovery, all the data since the last Full or Differential backup will be lost. This is because the log is truncated after each checkpoint (an automated process that cleans up memory and transactions within SQL Server). Within Full Recovery, it is only the data in the system since the last Log backup that is lost. It may even be possible to  retrieve that data by running what is known as a tail-log backup (more on that in the Restore to a Point-in-time section below).

So, the first determination has to be what RPO will you be shooting for within the database in question. If it’s a secondary system with data that is not that important, Simple Recovery can make your backups and log management much easier. But you must remember that you surrender the ability to restore to a point-in-time. For most production systems, where the data is vital for the business, you should plan on Full-Recovery along with the necessary scheduled log backups.

Backups under Simple Recovery

To begin working with a database in Simple Recovery we need to first set it. I’m using AdventureWorks2012, the Microsoft supplied sample database (available here). To make sure it is in Simple Recovery I can run this T-SQL statement:

ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE;

That will place the database into Simple Recovery and this database no longer has the ability to be restored to a point-in-time.

Full

To get started with a backup you can issue the following T-SQL command:

BACKUP DATABASE AdventureWorks2012

TO DISK = 'D:\BU\AdventureWorks2012.bak';

That will create a page-by-page copy of the complete database, including any uncommitted transactions. Uncommitted transactions are included in the backup in order to support a process called recovery. Recovery  happens during a restore (more details on that in the restore section below). The backup is called a Full backup because it is all of the database, including every bit of data, all structures and code, even the database security information. A file is created at the location specified. It’s better to backup to file and then copy that file to other storage locations such as an off-site location or secondary media such as tape (if anyone is still using tape) because it makes the backup process faster and safer. You also have the ability to do a faster restore because you have the backup stored on a disk.

I’m using T-SQL instead of the graphical user interface for two reasons. First, you’ll understand better exactly how backups work by learning from T-SQL since it pares everything down to the bare essential commands and doesn’t have all sorts of clutter like the GUI. Second, when you start building out your backup processes on your systems, you’re going to need to automate and control these, and you get much better options for automation and control through T-SQL, so best to learn it right from the start.

Differential

If you are using Simple Recovery, you can then take regular backups.  If, for example, you back up every week, you are, however, still at risk of losing up to a week’s worth of data. To avoid this, you can take additional differential backups between the full ones.  A differential backup is a copy of all pages within the database that have been modified, in any way, since the last full backup, plus any uncommitted transactions for recovery. The most important part of that last sentence are the five words, ‘since the last full backup.’ Let me explain that. Assume that we take a full backup on Sunday evening. Then, on Monday evening we take a differential backup. The differential backup will contain all the pages that have been modified since Sunday, one day’s worth. If, on Tuesday evening, we take another differential backup, that will contain all the pages modified since Sunday, two day’s worth. And so it goes.

Differential backups are handy because they make for smaller, faster backups. This means you’re putting less load on the system during the backup process. But, they can add to the time it takes to restore a database, which will  impact your Recovery Time Objective (RTO). That is the amount of time it takes you to restore your databases. That number goes up as you add additional work, such as restoring a differential backup or restoring log backups on top of restoring the full backup. RTO is something you have to take into account when working out your backup strategy with the business.

The T-SQL syntax to get a differential looks like this:

BACKUP DATABASE AdventureWorks2012

TO DISK = 'D:\BU\AdventureWorks2012_Differential.bak'

WITH DIFFERENTIAL;

You’ll note that the syntax looks almost identical to the backup syntax except for the change in the file name, and the addition of the WITH DIFFERENTIAL clause. There are a number of additional options available in the WITH clause. We’ll only cover a few in this article. For more detail, I strongly suggest using the Books Online.

COPY_ONLY

Differential backups come with a potential snag.  They represent the difference between the current database and what it was when the last full backup was taken. Imagine that you have a scheduled backup regime where you take a full backup, followed by differentials over a couple of weeks.  Obviously, to do a restore, you need that backup and the latest differential.  However, Tim, your rather sketchy colleague, takes another backup in the meantime in order to copy the database for a test. Having done so, he then deletes the backup since he doesn’t need it any longer. Then, your Database crashes and burns. You do the full restore successfully, but then your differential doesn’t bring you up to date because it represents the difference from Tim’s deleted backup. Instead, you get an ugly-looking error. So, a special command is needed to ensure that you can take extra ad-hoc backups safely:

BACKUP DATABASE AdventureWorks2012

TO DISK = 'D:\BU\ADW_Copy.bak'

WITH COPY_ONLY;

Using the COPY_ONLY statement will ensure that this full backup doesn’t interfere with subsequent differential backups.

Backups under Full Recovery

In order to recover to a point-in-time, you must be able to perform log backups. This means having a database in Full Recovery:

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

Now, I not only can take log backups of this database, but I need to take log backups of this database.

Full

Nothing really changes for a full backup when the database is in Full Recovery. The syntax and results are identical. The same thing goes for differential backups. They don’t change what they do or how they do it. You just have to worry about the addition of the log backups.

Log

With a database in Full Recovery, if you don’t run log backups on a scheduled basis, your log file will grow. It will continue to grow until it fills the drive (or drives) that you’ve placed it on. You must run regular log backups. The syntax is shockingly simple:

BACKUP LOG AdventureWorks2012

TO DISK = 'D:\BU\AdventureWorks2012_Log.bak';

This will backup the log of the AdventureWorks2012 database. Note, the only real change in syntax is going from the word DATABASE to LOG. That’s really all it takes. This will copy all the transactions in the log, committed or not. Then, it will truncate the log, which means that  all committed transactions are removed from the log. That will free space within the log for more transactions. But, note, it will absolutely not reduce the size of the log file. It just makes room for more transactions.

We need to introduce one more concept to our backups at this point. Up to now, all the examples have shown how to do one backup. But, if you are going to set up log backups, you will be doing lots of them. A good starting point would be to do log backups every 30 minutes. That's 48 log backups during one day. If you ran the T-SQL statement above 47 more times, you would have only one file named AdventureWorks_Log.bak on the D: drive. This is because SQL Server will append backups into a file, more or less stacking them together. You can control this behavior by choosing how you want to control your backups. You can do everything to one file, or, you can go to multiple files. But, if you ever attempt to perform a backup to an existing backup file name, SQL Server will stack that backup within that file. To stop this behavior, you can choose to modify your syntax like this:

BACKUP LOG AdventureWorks2012

TO DISK = 'D:\BU\AdventureWorks2012_Log.bak'

WITH INIT, FORMAT;

The INIT key word tells the backup process to throw away any existing backups and initialize the file with this new backup you’re running. It’s worth noting though, if you were to run this command 47 more times, you would still only have a single file, but it would only have the last log backup. That’s potentially a career-changing problem. UPDATE: Per Sean McCown, it's always a best practice to include FORMAT with INIT in order to avoid corruption of the header preventing a successful backup. Thanks Sean.

In order to restore to a point in time, you must have all the log backups from the point of recovery, meaning the restore of a full or differential backup, in a continuous chain. You can restore log backups for a given database against any full backup of that database as long as it falls within the time range of those log backups (making log restores completely different than differential restores). However, if there is a  break in the log chain, then all the log backups after that break are useless: You’ve lost that data.

You have two alternatives:  You can either stack the backups into a single file or have them all as individual files. Your choice depends on how you wish to manage the process. You can have a single file, which makes file management easy, but knowing what’s in it is more difficult (there are queries you can run, we’ll cover them in the Restore section) or you can know what’s in the backup because you name it carefully, but you’ll have lots more backup files to deal with. I’ve worked both ways and both have drawbacks. In general, I choose to keep full and differential backups as unique files and then stack the log backups into a single file.

You can schedule  your log backups with  any kind of scheduling tool that can make the necessary T-SQL call, but the easiest way to do it is to use the built-in service SQL Agent.

File and FileGroup Backups

For very large databases (and by that, I mean, at least 500gb, but more like 5-10tb or more), it can become too expensive to regularly run a straight full backup . So, where needed, you can choose to backup smaller pieces of the database by choosing to back up one of the files or file groups that make up a database. But before you start backing up files and filegroups you need to think about the restore process. The reason this was put into the Full-Recovery section is because, under Simple Recovery, you can only ever restore a READ ONLY file or filegroup. You can restore a file or filegroup under Full Recovery, independently from the rest of the database restore, which we’ll talk about next, but you must have a full chain of log backups in order to restore a read/write file.

To illustrate this, I’ll add a filegroup and a file to my AdventureWorks2012 database:

ALTER DATABASE AdventureWorks2012

ADD FILEGROUP SECONDFILEGROUP;

GO

ALTER DATABASE AdventureWorks2012

ADD FILE (NAME = N'AdventureWorks2012_Data2',

FILENAME = N'D:\Data\AdventureWorks2012_Data2.ndf',

SIZE = 2GB, FILEGROWTH = 10GB) TO FILEGROUP SECONDFILEGROUP;

GO

With that in place, I can then choose to backup just a filegroup:

BACKUP DATABASE AdventureWorks2012

FILEGROUP = 'SECONDFILEGROUP'

TO DISK = 'd:\bu\adw_fg.bak';

I could now restore this filegroup independently of the rest of the database. For more on recovery from file, filegroup and partial backups, read this article by Sean McGeehee.

Restoring a Database

A vital part of protecting your business is getting backups in place. But backups, as we’ve seen, are just files. A restore means that you have a database again. You can only restore  what you’ve previously backed up and in the same way that it was backed up. By this I mean that if you have a full backup, and that’s all, you can’t do a differential or a log restore. Further, you can’t selectively restore bits and pieces of that backup. For example, someone dropped a stored procedure and you want it back. You can’t go to your backup and restore just that procedure. You’d need a tool like SQL Compare to do that.

In order to restore a database, there cannot be any connections in place. The processes within SQL Server that perform the restore must have exclusive access. You’ll need to have everyone disconnect from the database, including yourself. Once the system has access, the restore is very simple:

USE master;

go

RESTORE DATABASE AdventureWorks2012

FROM DISK = 'd:\bu\AdventureWorks2012.bak';

But if I run that with the current state of my database, Full Recovery, I’m likely to see this error:

Msg 3159, Level 16, State 1, Line 1

The tail of the log for the database "AdventureWorks2012" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

That means that I need to run a special type of log backup, tail log, which I’ll cover in the next section. I can choose to ignore this error and force the restore by simply modifying my restore command slightly:

RESTORE DATABASE AdventureWorks2012

FROM DISK = 'd:\bu\AdventureWorks2012.bak'

WITH REPLACE;

That will result in a database restored to the point-in-time when I took my first backup. You could also choose to run a restore to a different database name entirely, but, because a database is a set of files, you have to take this into account to avoid generating errors and add an additional clause, WITH MOVE:

RESTORE DATABASE AdwCopy

FROM DISK = 'c:\bu\AdventureWorks2012.bak'

WITH MOVE 'AdventureWorks2012_Data' TO 'c:\data\adwcopy_data.mdf',

MOVE 'AdventureWorks2012_Log' TO 'c:\data\adwcopy_log.ldf';

The MOVE statement uses the logical file names to move them to new physical locations. This is also the first time I’ve shown that you can have multiple WITH clauses. Just separate them by commas as you can see with the two MOVE statements, one for each file in the database. In the event that you don’t know what files make up a database, you can use another command to retrieve this information from the backup file itself:

RESTORE FILELISTONLY

FROM DISK = 'C:\BU\Adventureworks2012.bak'

 

This will show the logical file names and the physical locations for the database that was backed up. You’ll also see a bunch of additional information about the files that define that database.

Restore to a Point-in-time

If you don’t need to ever recover data up to the latest minute, you can just work with full and differential backups and never have to worry about log backups. But, if you want to restore to as close as possible to a particular point-in-time because of an error or some other reason, you’ll need to use log backups. To set up this test, we’ll do run the following script:

  • ensures my database is in Full Recovery
  • does a full backup
  • then does an edit, modifying data
  • then does a log backup to capture that transaction
  • then waits two minutes
  • does another modification
  • finally we’ll take a another log backup:

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

GO

BACKUP DATABASE AdventureWorks2012

TO DISK = 'c:\bu\pit_full.bak'

WITH INIT;

GO

USE AdventureWorks2012;

GO

UPDATE Sales.Customer

SET ModifiedDate = GETUTCDATE()

WHERE CustomerID = 42;

GO

BACKUP LOG AdventureWorks2012

TO DISK = 'c:\bu\pit_log.bak'

WITH init;

GO

WAITFOR DELAY '00:02'

GO

UPDATE Sales.Customer

SET ModifiedDate = GETUTCDATE();

GO

BACKUP LOG AdventureWorks2012

TO DISK = 'c:\bu\pit_log.bak';

 

Notice that my second UPDATE statement is a huge mistake. It doesn’t have a WHERE clause, so it has modified all the Sales.Customer rows. I don’t want that, so I’m going to do a point-in-time restore. First, I need to restore the database, but, leave off the last step of a full restore, the recovery, where it rolls forward or back any open transactions from the time of the backup:

--just in case, backup the tail of the log

BACKUP LOG AdventureWorks2012

TO DISK = 'c:\bu\pit_log_tail.bak';

GO

USE master;

GO

RESTORE DATABASE AdventureWorks2012

FROM DISK = 'c:\bu\pit_full.bak'

WITH REPLACE,

NORECOVERY;

I went ahead and backed up the log one more time, just as a precaution. Because of that, I didn’t have to use the WITH REPLACE option, but, again, as a precaution  in case another transaction came through before my RESTORE operation runs, I put it in. You can see the status of a database by running this query:

SELECT DATABASEPROPERTYEX('AdventureWorks2012','STATUS'); 

This database is still not back online.

Now I need to restore my logs to get to the point-in-time just before the mistake was made. All the log backups that were taken went to a single file, so I’ll need to identify which backup I’m referring to within that file when I run the process. Here’s how it will work. First, I have to start with the logs that are applicable to my full backup and the point-in-time I’m interested in, so, I’ll take a look at the backups within the file:

RESTORE HEADERONLY

FROM DISK = 'c:\bu\pit_log.bak';

In my case, this only returns two backups, but it could return any number depending on how you set up your backup processes. With the knowledge of the files I need to refer to, and the knowledge of the point-in-time I wish to stop at, just before the offending error, I can build a log restore like this:

RESTORE LOG AdventureWorks2012

FROM DISK = 'c:\bu\pit_log.bak'

WITH FILE = 1,

STOPAT = '2013-12-16 07:29:59.000',

NORECOVERY;

I had to reference the file within the backup because I chose to stack them together this time. I also had to supply the STOPAT time for my log so that I don’t accidently run over and I left the database in a recovering state so I could apply more logs. Here’s the next statement:

RESTORE LOG AdventureWorks2012

FROM DISK = 'c:\bu\pit_log.bak'

WITH FILE = 2,

STOPAT = '2013-12-16 07:29:59.000',

NORECOVERY;

I will continue restoring logs, incrementing the file, until I’m sure that I’ve hit the last log that will have the transactions ending at the time I have defined. Then I’ll stop. But, the database will still not be recovered, so I’ll have to do one more step:

RESTORE DATABASE AdventureWorks2012

WITH RECOVERY;

That will put the database through its final recovery, bringing it online to a point-in-time.

Backup to URL

The novelty of SQL Server 2014 is that it allows you to use Azure Blob Storage in association with your on-premises databases. You can backup directly to a URL. This allows you to immediately have your backups in an offsite location as insurance against the loss of your facilities. The process is easy. First, you have to set up an Azure account and, on Azure, a blob storage account. Once set up, the rest is easy. You’ll need to get the Access Key from your storage account to then create a Credential within SQL Server:

CREATE CREDENTIAL MyCredentialName

WITH IDENTITY = 'MyStorageAccountName',

SECRET = 'MyAccessKey';

You have to give the Credential a name, then you use your Storage Account name and the Access Key. With that in place, there’s just a slight modification to the backup command:

BACKUP DATABASE MyNewDB TO 
URL = N'http://myserver.blob.core.windows.net/bu/MyNewDB.bak'
WITH  CREDENTIAL = N'MyCredentialName',
NAME = N'MyNewDB-Full Database Backup',
STATS = 10;

In addition to showing you how to use the URL in place of a disk and include the Credential, two other WITH statements that you can use on your backups are added. You can NAME backups to give them more descriptive designators to make it easier to understand why a backup was created or what it’s for. This is available to you when you run a RESTORE HEADER_ONLY command. You can also see how long a backup is taking by adding the STATS command and an increment measured in percentages. STATS = 10 will show as each 10% of the pages of the full backup are copied over, allowing you some idea of the status of the backup as it runs.

A restore is just the same thing in reverse:

RESTORE DATABASE AdventureWorks2012

FROM URL = N'http://myserver.blob.core.windows.net/bu/MyNewDB.bak'

WITH  CREDENTIAL = N'MyCredentialName',

REPLACE;

Backing up to Azure storage is currently limited to files of 1TB or less. You’ll need to manage those files out on Azure storage too. We can help with Cerebrata Azure Management Studio.

Database Snapshots

A database snapshot is not really a backup as we consider it, but, it can be used to create a “copy” of your database and then restore from that copy, so it’s useful to know what a snapshot is and how it works. Snapshot backups were introduced in SQL Server 2005 and are available with Enterprise versions of SQL Server. When you create a snapshot, a read-only copy of your database is created by maintaining copies of pages of the database that are modified, but only those pages that are modified. In other words, before a page gets modified, it will be copied over to the snapshot. This makes the snapshot a constant copy of the database, a lot like a backup. But, it’s not really a backup, nor should this mechanism be used to substitute for backups under most circumstances. I would suggest only using it for occasions when you need a short-term backup of your database. For example, during a production upgrade, you could take a snapshot instead of a full backup to allow you to rollback from the changes you’re making to your database. This is because, once you have a snapshot in place, you can actually revert the database back to the state at which the snapshot was taken. Because it’s only moving around pages that have changed, taking a snapshot is radically faster than backing up a database and the same goes for restoring the snapshot.

The command required to create a snapshot is utterly different than the backup commands we’ve been running:

CREATE DATABASE ADW_Snap

ON (NAME = AdventureWorks2012_Data,

FILENAME = 'c:\data\ADW_Snap.ss')

AS SNAPSHOT OF AdventureWorks2012;

 

We’re actually creating a database. It’s a read only copy of your database. You need to make sure you have room to store the pages, which will grow as the underlying database is modified, but it’s very simple and fast.

You can ‘restore’ these too. You will need to make sure that you only have one, so if you create more than one snapshot on a database, you’ll need to remove all except the one you want to restore. Once that’s done, the process is incredibly simple:

USE master;

GO

RESTORE DATABASE Adventureworks2012

FROM DATABASE_SNAPSHOT = 'ADW_Snap'; 

That will restore the database, by working in more or less in a reverse fashion from the original creation of the snapshot, copying over the pages that were copied in the other direction.

Best Practices

There are any number of “best practices” for database backups. I’m only going to cover a few of the most important. First and foremost, and I do repeat this a lot, database backups are something the business should be defining, not technical people. Work with your business to identify the best methods for ensuring that your information is protected.

You should backup to a different disk to the one where you store your data and the operating system of your servers. This is to help to insure against a single failure point. That’s also why it’s a good idea to use off-site storage, maybe through backup to URL or through other processes. If you didn’t have to worry about disks failing, then you really wouldn’t need to do backups of your databases at all. But hardware does fail, so take that into account.

The vast majority of databases should be configured with Full Recovery in mind. This means you will need to set up log backups on a regular basis. The frequency really depends on your recovery requirements that you can work with the business to define.

As you’ve seen, it is pretty simple to get backups set up and running. There’s not much to it. But, a backup is nothing but a file that is stored somewhere. It’s useless unless you can restore it. The single best mechanism you have to ensure that you can restore it is to actually restore it.  By doing so, you not only test whether the backups worked, but also get practice in the process of restoring it when the time comes. Practice your restores frequently.

Finally, I used T-SQL in this article to show how to perform backups and restores. I did so because you should be using scripting for all your backups. That’s because scripting is going to allow you to exert maximum control over what gets backed up, how it gets backed up, where it gets backed up, etc. It’s also a good idea to come up with a naming convention for your backups so that it’s relatively easy to identify what that file represents. You can incorporate database names, server names and even dates and times into the name of the file. It’s up to you, but clarity is your friend in these situations. Scripting will help here too.

For even more on backup best practices, read through this article on 7 Preventable Backup Errors.

Summary

This was an overview of the capabilities of the backup processes for SQL Server 2014. Most of what was covered is largely applicable, as written, all the way back to SQL Server 2000. I’ve tried to identify when different functionality was introduced, but you can always refer to the Books Online for your version of SQL Server. Just remember, backups are primarily a business problem, not a technology one. To arrive at a good backup strategy, you must work with your business to ensure that you’re supplying adequate coverage for them. Don’t forget to practice restores as often as you can, especially if your backups are complex. Managing backups isn’t hard, but it’s vital that you get it right. Take your time. Think through your strategies. Mix and max the different technologies you have on hand in order to arrive at an optimal method for your business.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 26 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Diff backups
Posted by: Perry Whittle (not signed in)
Posted on: Saturday, January 04, 2014 at 10:33 AM
Message: Great article Grant but a word or two on diffs. They don't always make for smaller faster backups. I had a database previously that was managed by a std backup regime i run. The backups for one db were huge almost as large as the full. Turns out its a staging db and when loaded the tables are first truncated :-)

Generally diff backups make for faster restores, its far easier to restore one full and one diff than one full and a fistful of log backups

Regards Perry

Subject: re: Diffs
Posted by: Grant Fritchey (view profile)
Posted on: Tuesday, January 07, 2014 at 5:05 AM
Message: Hi Perry,

No argument. By their nature, they won't work in all situations and in some situations will make things, if anything, worse than just doing full backups. Similar situation with the logs too.

Subject: Great Article
Posted by: Charles Kincaid (not signed in)
Posted on: Tuesday, January 07, 2014 at 10:03 AM
Message: With the year in the title I thought that you might be talking about something specific in new SQL Server versions. After giving this a read I find two things: First, there is nothing new here. That makes me feel good. Second, it confirms that you a great writer.

I agree with you statement about learning to do backups and restores with SQL statements. SSMS, as wonderful as it is, can lead a poor soul down the garden path. Case in point is that we have copies of production databases from several of our clients. All of them on the same server here. To avoid mayhem you have to specify the physical names on all restores. This is something most folks won't have to deal with. Still if you get used to having to specify what you need then you are better equipped to deal with this when (not if) your circumstances change.

Subject: re Great Article
Posted by: Grant Fritchey (view profile)
Posted on: Tuesday, January 07, 2014 at 10:57 AM
Message: Thank you! Very kind words.

Backup to URL is completely new. There's also a new service called Managed Backup, but writing it into the article would completely overwhelm the other information. I might do a separate article on that. Other than those, yeah, backups are pretty much backups.

Subject: great overview
Posted by: Leon Orlov (not signed in)
Posted on: Tuesday, January 07, 2014 at 2:09 PM
Message: sweet and short... thanks Scary DBA

~Leon Orlov
from Salem

Subject: I really liked this article
Posted by: Phil Factor (view profile)
Posted on: Thursday, January 09, 2014 at 6:14 AM
Message: Thanks, Grant. It is a useful article for reference. So often, writers home in too remorselessly on the detail of backup and restore, but this is just right.

Subject: INIT
Posted by: Sean McCown (not signed in)
Posted on: Thursday, January 16, 2014 at 6:23 AM
Message: I might also point out that with the INIT flag, it doesn't rewrite the header. So if the header becomes corrupt (has happened to me) or if you change the number of files, you'll get an error.

You can repro this by taking a simple back with a single file using INIT. Then stripe it to 2 files. You'll get an error that the header isn't formatted for 2 files. Now use INIT, FORMAT and it'll work.

You should always use FORMAT and INIT together.

Subject: re: INIT
Posted by: Grant Fritchey (view profile)
Posted on: Thursday, January 16, 2014 at 6:37 AM
Message: And that's why I love working in SQL Server. This community is awesome.

I absolutely did not know that. Thanks for the education Sean.

Subject: INIT
Posted by: Sean McCown (not signed in)
Posted on: Thursday, January 16, 2014 at 7:01 AM
Message: Hey, that's why we're here right? I learned this lesson the hard way actually... the same way I learn everything.

On another note, even though this article was basic I read it anyway. You've got such an informal style that I find myself reading even when I know the topic. Many times I can even close my eyes and imagine you talking directly to me, calling my name, feeling your hand run up and down my......... oh sorry, got carried away there. Anyway, write good and stuff.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.