European SharePoint 2010 Hosting :: Just Solution - Backup Your SharePoint 2010 with SQL Server

Hmm… Just say that you have built your SharePoint 2010 site, now the question is what options are available to backup your data in a SharePoint 2010? Today, I will show you the way.

Solution

This is an excellent question and hopefully something that is being planned out prior to implementing SharePoint in your environment. There are numerous parts of SharePoint that can be backed up individually or completely. A good TechNet article to start with is this one: Backup (SharePoint Server 2010). Reviewing this article you will find it contains many solutions to backing up SharePoint data, and although it is SharePoint 2010 there are a few options that apply to any version. A caveat: Since I am short a good lab environment I’m going to focus on the tools I have available to me. So, since I’m a DBA by trade it will be using the SQL Server option. The script I provide below is how I backup all the SharePoint databases in my environment. Among the numerous databases that are created when you install SharePoint, the content database is the one that “holds all the marbles” for your customers/users. Now, the others are important as well, but the content database can be the one that makes or breaks your company (or your job) depending on how it is implemented and used.

In the article a link shows 3 options that can be used to backup the content database: Windows PowerShell, SharePoint Central Administration, and SQL Server tools. I have used the Central Admin on WSS versions and found it to be not exactly what I wanted. SharePoint 2010 offers the ability to utilize PowerShell which is a great, powerful tool to learn. My preference is SQL Server. The script below is built toward use with SQL Server 2008, but can be adjusted to work with SQL 2005 very easily.

The one feature of the script that is specific to SQL Server 2008 is something called Backup Compression (see this TechNet article). If your environment has SQL Server 2008 (non R2 editions) this feature is restricted to Enterprise Edition only. In SQL Server 2008 R2 editions, this feature has been made available to Standard Edition and higher. It is a wonderful feature and may/may not benefit you in your environment. In my environment it was benefical in saving storage space for the backups. I strongly suggest you test anything prior to implementing it. Check the link under “Next Steps” below for some information on pros/cons to using backup compression.

First you may want to enable compression at the server level. You can skip this step and do it at the database level, but I went ahead and enabled it

--Enable backup compression server wide
EXEC sp_configure 'backup compression default', 1
GO
RECONFIGURE WITH OVERRIDE
GO

--Enable backup compression server wide

EXEC sp_configure 'backup compression default', 1

RECONFIGURE WITH OVERRIDE

With the next script you can just copy and paste into an SQL Server Agent job step to run on a scheduled basis, or run it when needed. The portion of the script in the BACKUP DATABASE statement contains the keyword COMPRESSION, this should be removed if using this script with SQL Server 2005 edition. Even though you can leave this out with SQL Server 2008, if enabled at the server level (according to the TechNet article referenced above), I went ahead and put the command in.

/*
***************DISCLAIMER***********************************
I don't claim to be the original author of this script,
      this is just my version of it. It was a common syntax
      that could be Google/Bing for with "SQL backup script".
I offer no guarantees on this script and its use in
      your environment.  You shoud not take my word on anything
      and test this before implementing in a production environment.
************************************************************
*/

DECLARE @name VARCHAR(50) -- database name�
DECLARE @path VARCHAR(256) -- path for backup files�
DECLARE @fileName VARCHAR(256) -- filename for backup�
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @type VARCHAR(5) -- backup type

--The directory where you want to store the backup files
SET @path = 'D:\BACKUPS\CIWS\'
--This is strictly used for naming of the backup file
SET @type = 'FULL' 

--Pull current date, for use in the file name
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

--YES I use cursors!!!
DECLARE db_cursor CURSOR FOR�
SELECT name
FROM msdb.sys.databases
WHERE name LIKE 'WSS%'�
/* Most installations of SharePoint tag "WSS" on the name of the database. If
your environment is different you may need to adjust the WHERE clause a little. You can just copy the SELECT statement and paste in query window by itself to make sure it will return the desired databases to backup. If you want to only do specific ones simply make it:
WHERE name IN ('dbname','dbname','dbname')
*/

OPEN db_cursor �
FETCH NEXT FROM db_cursor INTO @name �
--Goes through the list of databases returned by the SELECT statement above
-- and performs a FULL backup on each one.
WHILE @@FETCH_STATUS = 0 �
BEGIN �
      --Set the filename up to be used
      SET @fileName = @path + @name + '_' + @fileDate + '_' + @type + '.BAK'
      --full database backup with compression
      BACKUP DATABASE @name TO DISK = @fileName  WITH FORMAT, COMPRESSION
      --go get the next database name to backup
      FETCH NEXT FROM db_cursor INTO @name �
END  

--clean up
CLOSE db_cursor �
DEALLOCATE db_cursor

***************DISCLAIMER***********************************

I don't claim to be the original author of this script,

this is just my version of it. It was a common syntax

that could be Google/Bing for with "SQL backup script".

I offer no guarantees on this script and its use in

your environment. You shoud not take my word on anything

and test this before implementing in a production environment.

************************************************************

DECLARE @name VARCHAR(50) -- database name�

DECLARE @path VARCHAR(256) -- path for backup files�

DECLARE @fileName VARCHAR(256) -- filename for backup�

DECLARE @fileDate VARCHAR(20) -- used for file name

DECLARE @type VARCHAR(5) -- backup type

--The directory where you want to store the backup files

SET @path = 'D:\BACKUPS\CIWS\'

--This is strictly used for naming of the backup file

SET @type = 'FULL'

--Pull current date, for use in the file name

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

--YES I use cursors!!!

DECLARE db_cursor CURSOR FOR�

SELECT name

FROM msdb.sys.databases

WHERE name LIKE 'WSS%'�

/* Most installations of SharePoint tag "WSS" on the name of the database. If

your environment is different you may need to adjust the WHERE clause a little. You can just copy the SELECT statement and paste in query window by itself to make sure it will return the desired databases to backup. If you want to only do specific ones simply make it:

WHERE name IN ('dbname','dbname','dbname')

OPEN db_cursor �

FETCH NEXT FROM db_cursor INTO @name �

--Goes through the list of databases returned by the SELECT statement above

-- and performs a FULL backup on each one.

WHILE @@FETCH_STATUS = 0 �

BEGIN �

--Set the filename up to be used

SET @fileName = @path + @name + '_' + @fileDate + '_' + @type + '.BAK'

--full database backup with compression

BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, COMPRESSION

--go get the next database name to backup

FETCH NEXT FROM db_cursor INTO @name �

END

--clean up

CLOSE db_cursor �

DEALLOCATE db_cursor

Enjoy this tutorial.

European SharePoint 2010 Hosting :: Just Solution – Backup Your SharePoint 2010 with SQL Server

Solution

BLOGROLL

Featured On

European SharePoint 2010 Hosting :: Just Solution – Backup Your SharePoint 2010 with SQL Server

Solution

BLOGROLL

Featured On

Tags