How to Backup SQL Server 2005 Databases

Backup Requirement

Daily full backup, transaction log backups every 15 minutes. Files are backed up to the local filesystem. Remote backup server copies changes from the server every 15 minutes. Although not an efficient use of space, it is useful to backup to the local hard drive in case a point in time restore is needed, or for restoring tests databases to other SQL Server instances.

Recoverability

If an unrecoverable failure occured on the database system the data will be recoverable to a state within 30 minutes (this is a worst case scenario if the remote backups aren’t synchronized: 15 since last transaction log and 15 minutes since last offsite backup) of failure by restoring the full backup and rolling the transaction log backups forward.

SQL Server backups

Create a Job to do a FULL backup daily. Overwrite the backup if it already exists, and it’s always a good idea to do a checksum and verify the backup:

BACKUP DATABASE [WSS_Content] TO DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.bak’ WITH NOFORMAT, INIT, NAME = N’WSS_Content-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’WSS_Content’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’WSS_Content’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”WSS_Content” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.bak’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Create a second Job to do a Transaction Log backup every 15 minutes. This time append to the backup file like this:

BACKUP LOG [WSS_Content] TO DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH NOFORMAT, NOINIT, NAME = N’WSS_Content-Transaction Log Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’WSS_Content’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’WSS_Content’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”WSS_Content” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

To prevent the transaction log backup file from growing infinitly large add a second step to the first Job, so that after the FULL backup an immediate transaction log backup occurs. The only difference is you want to overwite the backup set for the transaction log:

BACKUP LOG [WSS_Content] TO DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH NOFORMAT, INIT, NAME = N’WSS_Content-Transaction Log Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’WSS_Content’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’WSS_Content’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”WSS_Content” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Summary

The first Job runs daily and has two steps. The first step does a full backup of the database overwriting the previous full backup. The second step does a transaction log backup overwriting all the previous transaction log backups. The second job runs a transaction log backup, which gets appended to the previous transaction log backup every 15 minutes. While this is happening the backup server checks every 15 minutes archiving any new or changed files from the system.

Restoring

To restore the database, first restore the full backup, then restore each transaction log. Here’s an example of restoring the first four transaction logs:

RESTORE DATABASE [WSS_Content] FILE = N’WSS_Content’ FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 3, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 4, NOUNLOAD, STATS = 10
GO

Make sure the last log restore puts the database into RECOVERY mode by omitting the NORECOVERY command. The database can only have transaction logs rolled forward while in NORECOVERY mode. If you mess this up by putting the database into RECOVERY mode before the last transaction log is rolled forward, you have to start the restore process all over. So do not mess it up.

Push email.

What’s the big deal about push email?

RIM’s Blackberry and BES server, Apple’s iPhone and MobileMe service, Microsoft’s ActiveSync with Exchange all offer Push Email. That is, your phone instantly gets a notifcation of new email. I don’t get it. I don’t want my phone to tell me when I have new email. I disable push email and set it to check once an hour at most and when I feel like it I’ll read them to see if there’s anything urgent. I get tons of personal and work email throughout the day and barely have time to read them much less get interrupted every time one comes in. Sometimes I even exit Outlook while I’m at work if I need to get something done. I think most people don’t realize how much time checking email costs.

If anything I would like a phone that notifies me when I don’t get new email, because that probably means something is wrong with the mail server.

Submission to Authority

Submission to Authority

Barrack Obama just won the election. You and I know some of the reactions we’ll hear from Christians—and they won’t all be Christian (I know this from the Clinton years). So let me take this as an opportunity to remind you (and myself) that all authority comes from God. This is clearly explained by Paul in Romans 13:1-7 (ESV):

1 Let every person be subject to the governing authorities. For there is no authority except from God, and those that exist have been instituted by God.

We are to submit to governing authorities. Paul says here that all authority is established by God (Even leaders like Pharaoh were brought into that position for God’s purpose—Romans 9:17). John MacArthur says that God has instituted 4 authorities on earth1

  1. government over citizens
  2. church over believers
  3. parents over children
  4. masters over employees

2 Therefore whoever resists the authorities resists what God has appointed, and those who resist will incur judgment.
3 For rulers are not a terror to good conduct, but to bad. Would you have no fear of the one who is in authority? Then do what is good, and you will receive his approval,
4 for he is God’s servant for your good. But if you do wrong, be afraid, for he does not bear the sword in vain. For he is the servant of God, an avenger who carries out God’s wrath on the wrongdoer.

Disobedience to the government is disobedience to an institution established by God, which will result in punishment by the government. The government has the right to inflict judgment on those who do evil.

5 Therefore one must be in subjection, not only to avoid God’s wrath but also for the sake of conscience.

Not only should we be submissive because of God’s authority to avoid punishment, we should do it because it’s the right thing to do.

6 For because of this you also pay taxes, for the authorities are ministers of God, attending to this very thing.
7 Pay to all what is owed to them: taxes to whom taxes are owed, revenue to whom revenue is owed, respect to whom respect is owed, honor to whom honor is owed.

Taxes should be paid, even to evil governments. We should pay, respect, and honor those in authority over us.
Paul also says in 1 Timothy that we should pray for our rulers:

1. First of all, then, I urge that supplications, prayers, intercessions, and thanksgivings be made for all people,
2 for kings and all who are in high positions, that we may lead a peaceful and quiet life, godly and dignified in every way.
3 This is good, and it is pleasing in the sight of God our Savior,
4 who desires all people to be saved and to come to the knowledge of the truth.
5 For there is one God, and there is one mediator between God and men, the man Christ Jesus,
6 who gave himself as a ransom for all, which is the testimony given at the proper time.
7 For this I was appointed a preacher and an apostle (I am telling the truth, I am not lying), a teacher of the Gentiles in faith and truth.
8 I desire then that in every place the men should pray, lifting holy hands without anger or quarreling;

So what should your action be when Obama becomes president?

Money Advice

My church is going through one of Dave Ramsey’s finance programs to help people get out of debt and better manage their finances. Naturally attendees must pay a fee to get in. So, here’s some free advice from Ben:

Make more than you spend.
Have a budget, or a reverse budget. Make sure your saving, meeting your basic needs, and set some aside for fun.

Have some money in the bank ready for an emergency. Keep some cash stashed away in case you can’t access your bank account.
Save for retirement. Save for short term. Save for long term. Diversify your savings. Put your short term savings in high yield checking/savings accounts.

Spread your money into several bank accounts.

Don’t get into debt over something that’s not worth more than the debt.

Your time is worth more than your money.

Take a photo of everything in your wallet/purse.

Engaged

I’m Engaged!

ESV – Less restrictive than NASB


Being an NASB fan (but still using KJV on my computers and mobile devices because of the copyright issues) I’m excited to see that the ESV’s copyright is much less restrictive.

I use an iPhone, Blackberry, Windows, OSX, and Linux so it’s just not practical to purchase the NASB for each and every operating system. The ESV can be downloaded for free with programs like BibleDesktop, e-Sword, GnomeSword, etc. and the ESV has opened up an API for developers to access the ESV directly… resulting in webapps that are usable on mobile phones (the offical ESV Mobile website works great on BB, and iBibleSpace for the iPhone)

With fewer restrictions, an ESV Study Bible coming out later this year, and better covers Crossway Bibles is going to give the Lockman Foundation some serious competition.

Packing….

Oh, what a cluttered mess I am. Packing, packing, packing.

The biggest challenge is throwing away stuff I just don’t need or use.