Backup & recovery

If you want to keep the data, back it up; even if it’s your cell phone!

Another major data loss is showing that if you want to keep your data, its up to you to back it up.  T-Mobile customers who have a sidekick phone are being told this weekend that they shouldn’t turn off their cell phones.  If they do they will loose their address book data and anything else saved on the phone.  Apparently there are some major data center problems going on over at Danger (the folks who make the Sidekick) and the Sidekick relies very heavly on the servers in the Danger data center.  Apparenly there have been problems going on for a couple of days now.  So far there’s no planned update from T-Mobile until Monday.

The problems are serious enough that T-Mobile has suspended the sale of the phone, and posted a very serious notice on there forums “Sidekick customers, during this service disruption, please DO NOT remove your battery, reset your Sidekick, or allow it to lose power.”.

This is just another indicator that you need to be responsible for backing up your data.  You also need to know how your devices work.  Personally I have a blackberry and it is synced up with my Exchange server.  I know that if something happens to my Exchange server, I know that my local copy is fine.  Same goes if there is a problem with RIMs servers.

Don’t rely on your service provider to backup your device, if you want to keep your data back up the data your self.  If your phone provider doesn’t offer a way to back it up, check on the internet.  Most cell phone models have some way to back them up so that you don’t loose everything if something goes wrong.

Denny

Learn to use the COPY_ONLY flag for one off backups

Taking a full backup when doing major database upgrades is a great idea.  However if you are taking differential backups this one off full backup will break the differential backup chain. Continued »

Clean up the backup history

Something that most people don’t release needs a little TLC in SQL Server is the backup history.

Every time a database is backed up records are written about it.  Over time this can add up to a lot of useless data floating around the SQL Server in the msdb database.

If you like to use the UI to restore you databases, this can also lead to the UI stalling when the restore database window comes up.

Fortunately Microsoft has provided a system stored procedure which you can use to clean up this old data.  This procedure is the sp_delete_backuphistory system stored procedure.  The usage of this procedure is very simple.  It takes a single parameter @oldest_date which is simply the oldest date of data you want to keep.  As an exmple:

EXEC sp_delete_backuphistory ‘1/1/2009′ would delete backup data older than Janunary 1, 2009.

Denny

Developing a SQL Server Database Backup Plan

Last week I published a new article on Enterprise IT Planet called “Developing a SQL Server Database Backup Plan” in which I go over some of the techniques which are used to backup databases and why you need to ensure that your databases are backed up.

Denny

Mirroring isn’t a backup solution

In case you live under a rock and haven’t heard about Journalspace.com’s little mistake, they have gone out of business due to a database problem.  Here’s a screenshot in case the site is down when you look at it.

In a nutshell it appears that they were relying on a RAID1 array as the database backup.  While we see this all the time in small database shops as noted on /. this site has been up since 2002 and had an Alexa page rank of 106,881 with 14k monthly visitors (according to Quantcast).  For a site so large to be making such a simple mistake is just unacceptable. Continued »

Order To Restore Database Backups In

I’m often asked (both online and offline) once you have all your database backups, in what order to they need to be restored in?

I’ve actually asked this very question to senior level DBAs as an interview question before, and gotten some very interesting answers.

When restoring your database you start with your full backup, then restore your diffential backup (if you have taken one) then restore all the transaction log backups begining with the backup taken after the full (or differential) and going until the last transaction log backup available (or until you reach the point in time you want to stop the restore at).

Denny

How to setup a server to read log files nightly.

So you’ve been tasked with setting up a quick and dirty reporting server.  The goal is to restore the log files from the production server to the reporting server nightly.

 The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine.  But how do you restore the logs to the reporting server nightly.

 Well I’ve got a two step SQL job which should help you out.

Step 1 kills all current sessions in the database, and step 2 does the actual restores.

The code for step 1 is: 

declare @spid varchar(20)
declare cur CURSOR FOR
select spid
from sys.sysprocesses
where dbid = db_id(’Your Database Name Here’) /*<—Put your database name here*/
and spid > 50
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
exec (’kill ‘ + @spid)
fetch next from cur into @spid
END
close cur
deallocate cur

The code for step 2 is:

create table #Files
(FileName nvarchar(4000),
Depth int,
IsFile bit)insert into #Files
exec xp_dirtree ‘d:\’, 1, 1

I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.

Denny

New Article: Tips for scheduling and testing SQL Server backups

I’ve just published a new tip on SearchSQLServer.com called Tips for scheduling and testing SQL Server backups.  In it I’m talking about server backups including how to schedule those backups on SQL Server Express edition which doesn’t have the SQL Server Agent.

Denny

Webcast Recover the data, the whole data and nothing but the data you need available for viewing

The webcast which I did with Quest Software a couple of weeks ago “Recover the data, the whole data and nothing but the data you need” has had the recording posted for viewing on the Quest website.  If you missed the webcast live now is your chance to view it.

 Denny

Webcast: Recover the Data, the Whole Data and Nothing but the Data You Need

Coming on the heals of the extremely successful webcast “Under The Hood of SQL Server - Checking Out Internals”, Quest Software has asked me to co-present another webcast.  This one is entitled “Recover the Data, the Whole Data and Nothing but the Data You Need” and will be on March 6, 2008 at 8am Pacific (11am Eastern).  I’ve been told that so far the sign up rate has been incredible so far.

If you are interested in the product or doing object level restores (restoring one table or stored procedure, etc instead of restoring the entire database) then I highly recommend the webcast; granted I am a little biased.

See you at the webcast.

Denny