One of the first tasks having installed a SQL server is to setup a backup process, coming at it from a Linux centric world this is rather different and actually poorly documented on the Microsoft knowledgebase so here’s how to do it.
1. Open SQL Server 2012 Management Studio, this should be on your start menu but if not then check you enabled it by re-running the installer.
2. Right-click on management -> Maintenance Plans and select Maintenance Plan Wizard
3. This will start the wizard, click next
4. Give your ‘plan’ a descriptive name – something you will recognise when you come back to it months or years later, if you only want to perform a backup you can select the schedule (i.e. when to run it) here although I do that later on.
5. You can simply backup the database but if it’s going to be busy, especially with a lot of delete/update queries I’d recommend also having it shrink on a schedule to avoid it growing out of proportion by ticking both boxes. Skip to step 9 if you’re not interested in the shrink process.
6. I prefer to reduce the database size before the backup to keep the backup small although if you don’t trust your hardware you may prefer to have a good backup before you run the shrink in case it hits anything unexpected (although it’s pretty good to be honest).
7. I chose to shrink all databases, you can select just specific if you prefer. Behind this you can also select how aggressive you want the shrink to be.
8. This is the most scary looking screen of the process but is quite logical and allows you to set in quite granular means if you desire how and when the shrink should run, once a week is sufficient for my needs.
9. Now we’re onto the meat of the matter, backing up SQL databases. I chose a full backup but you may prefer a ‘simple’ backup. For details on the various types see Microsoft’s MSDN article.
I have created a folder, c:\backup to store my backups into, the location is down to personal preference but I don’t like storing data within program files as it is too easy to forget to backup the backups off-site as program files are not normally high importance on a backup strategy.
10. Similar to step 8 this allows you to set the schedule for when you want your backup to run, I chose nightly at 00:30 to make sure it was complete before the separate offsite-backup process ran but your needs may vary.
11. Optionally have the process write a log file or email it to you, this is down to personal preference.
12. The ‘Maintenance Plan Wizard’ is now creating our jobs for us
13. Confirmation that the jobs have been created – do review this to make sure that you didn’t miss anything (e.g. it’s easy to overlook the all-important scheduling).
14. You thought you were all done didn’t you… if this is a fresh install then you will most likely find that the SQL Server Agent is disabled by default meaning your maintenance plans won’t run. Personally I think the ‘Maintenance Plan Wizard’ should enable this but it didn’t seem to for me, it is however a simple matter of right-clicking to enable it.
If you’ve followed those steps then you now have SQL Server setup to create a .bak file every night and a shrink maintenance job run ever week.
Don’t forget to include this file in your offsite backups… you do have offsite backups don’t you?