Surendra Sharma

Surendra Sharma

Search This Blog

Saturday, October 8, 2016

Sitecore database lesson 5 - Sitecore Database Backup Script




SQL Server Management Studio provide a front end UI to take backup of database. A new developer may find this very comfortable but over the period of time senior developer feels this very time-consuming process and they want some quick way to take backup of Sitecore databases.

I am using below script, which was shared by one of my colleague, to take backup of Sitecore databases before installing any package and modules in development environment. This script takes backup of multiple databases at specified location in single shot.


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

-- specify database backup directory
SET @path = 'D:\DB Backup\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('SitecoreLessonsSitecore_Analytics', 'SitecoreLessonsSitecore_Core','SitecoreLessonsSitecore_Master', 'SitecoreLessonsSitecore_Web', 'SitecoreLessonsSitecore_Sessions')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor




I hope you like this Sitecore database lesson. Stay tune for more Sitecore database related articles. 

Please leave your comments or share this script if it’s useful for you.