Backup and Restore

This is a direct follow up from my last post about filegroups because without those we wouldn’t be able to perform what is called a Piecemeal database restore and, trust me, this is something you want to know how to do.

This is something I see asked all over the internet and it’s also something for which there are a LOT of incorrect responses, dodgy solutions, and poor advice (including on the MSDN website). Therefore as I’ve had to resize a few transaction logs recently I thought I’d share the code and process I’ve used.

This is a massive misconception that has been going around for a long while and it needs to be wiped out.

Initially I would have said to set this within sp_configure because although it is CPU intensive, I have always found that the cost is more than covered by the level of reward.

This is a crucial addition to SQL Server which is invaluable at times. It basically takes a snapshot of the database rather than an actual backup. Therefore it can be used to restore a database and to provide a copy, but crucially it does not break the backup chain.

This is actually the cleanest and quickest way to produce a backup as it is a one line command in the form of:

Making backups using the GUI is a little longer than when using T-SQL (here), but they do provide a much easier way to see and customise your backup options.

It is possible and perfectly acceptable to put T-SQL commands into a scheduled task in order to create a full backup plan, but a Maintenance Plan is Microsoft’s way of allowing you the power of the GUI backup with the convenience of a scheduled task so that you don’t have to code your own.

There are 3 backup types within SQL Server… Full, Differential, and Transactional.


The reason for there being 3 types are not immediately obvious but understanding them is key to having a good backup strategy within your system. These are also relevant to the Recovery Mode of your databases.