Jack of all that is Microsoft, Master of None

October 15, 2006

MOSS 2007 Backups – Find Your Databases

I was working on a Beta 2 -> TR upgrade the other day and came across a little tidbit that could come in handy one day, so here I am writing about it.   Basically, whenever I do anything that could lead to potential data loss in MOSS, I perform a couple of different backups just to make sure I’ve completely covered by rear:

  1. Run the MOSS Backup & Restore from Central Admin
  2. Backup all of the MOSS-related SQL Databases through the SQL Management Console
  3. Backup the IIS Metabase
  4. Backup the 12 directory

In 2003, my methodology was exactly the same, only for #1, I would run the SPS Backup Utility and for #4, I would backup the 60 hive.

Now, if you have a lot of databases to backup in SQL (my step #2), it can be a real pain to go in and manually backup each of them individually.  What I have found is that when you perform the MOSS Central-Admin based backup, yes, obviously it does backup the databases, and it backs each one up as an individual file… the only problem is that the filenames aren’t exactly the most informative (00000022.bak, 0000001A.bak, etc.), so you need to perform some simple work to determine what filename = what database. The reason I am writing about this is that once you know what filename = what database, you really no longer need to perform Step #2 and backup each database manually, thus saving you time.  You may be saying “Well then I dont have two backups of the databases,” but you can very quickly (and much more easily) have two!  So there are a few ways to figure out what database is what file after MOSS performs a backup and then get your extra copy:

 Method #1 – Log Files (Text way)

  1. Browse to the folder where you specified your MOSS backup should be saved to, and then open up the folder for the most recent backup (hint – look at the folder creation date).  If this is your first MOSS backup, the folder will be named spbr0000.
  2. In the folder, look for and then open the file spbackup.log.
  3. Browse through this file – you will want to find the first occurence of a line that looks very similiar to the following:Verbose: [SharePoint_Config] SQL Server Command: BACKUP DATABASE [SharePoint_Config] TO DISK=@db_loc WITH NAME=@db_name, STATS=5, NOINIT, NOSKIP, NOFORMAT, NOREWIND
     @db_name=SharePoint_Config, @db_loc=c:\mossbackup\spbr00000000022.bak
  4. Do you see what I see?  In the brackets [XXXXX] is the name of the database, along with right after @db_name – and then the last piece – @db_loc=  — this is what filename the database was stored as. 
  5. So now that you know the name of the file that correlates to your database, you can take a copy of that file and store it offsite, thus making life easier.

Note:  If you have any non-MOSS databases that are associated with applications within your MOSS deployment, you will still need to back these files up via SQL Management Console. 

Method #2 – via SQL Management Console (Visual Way)

I don’t have a copy of SQL 2000 installed on my MOSS server, so I don’t know if this method applies to SQL 2000-based installs.  But, for MOSS installed and using SQL 2005:

  1. Perform your MOSS backup through Central Administration.
  2. Open up the SQL Management Console.
  3. Right Click on a database whose backup filename you need.  Select All Tasks -> Backup.
  4. Within the window that appears, you will see the MOSS backup filename listed.  See the screen shot below… now you’ve got that filename!


Have a good one,


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: