How to deal with large Sitecore databases: Part II

In first part, I had covered how to shrink the log file of database. This article focus on how to take backup of large database in multiple files at different locations and how to restore them.

Split database into multiple files

If you asked to any developer about how to take database backup, they will generally reply - its very easy. Just use SQL server backup window or use the ready-made script.

But if you asked them take the backup of 20 GB database and upload on some different machine?

If you are making a single heavy backup file with 20GB and try to upload on FTP server then it might take hours. By any chance, this upload operation truncate in between then again you have to waste your time by uploading the same file on FTP server as well as you have monitor them now.

Is there any better way?

Yes. SQL server provide a way where you can split single database into multiple smaller file. Let suppose you have 20 GB database and now you can take backup in 10 different files and each file having 2 GB in size.

Here are other advantages of splitting database into multiple files

• Store each file in different drive or locations.
• ZIP individual file 
•  Upload individual file on FTP location

Here is a sample script for backup database in 3 different files at three different locations.

BACKUP DATABASE SitecoreLessonsSitecore_Master

TO DISK = 'D:\Backup\SitecoreLessonsSitecore_Master\1.bak',

   DISK = 'C:\Other_Drive_Backup\SitecoreLessonsSitecore_Master\2.bak',

   DISK = 'E:\Daily_Backup\SitecoreLessonsSitecore_Master\3.bak'

GO

Note:- Folder path must exist for successful backup operation.

Use below query to restore these files

RESTORE DATABASE [SitecoreLessonsSitecore_Master_Staging]

FROM DISK = N'D:\Backup\SitecoreLessonsSitecore_Master\1.bak',

     DISK = N'D:\Backup\SitecoreLessonsSitecore_Master\2.bak',

     DISK = N'D:\Backup\SitecoreLessonsSitecore_Master\3.bak'

GO

Note:- Restore work on server where this database is not exist.

You can backup and restore by using SQL server management studio also. Here is a reference link for it https://solutioncenter.apexsql.com/how-to-create-sql-server-database-split-backup-files/

I have used this backup script multiple times.

I hope you like this Sitecore database trick. Stay tuned for more Sitecore articles.

Till that happy Sitecoring :)

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

 
To know more about Sitecore database, Download this eBook.

How to deal with large Sitecore databases: Part I

Sitecore developers generally don't care much about Sitecore databases. Why? Because they concentrated on Sitecore front end, C# and .NET tasks. They neglect database most of the time.

This is fine as long as your database size is small and handled by SQL server efficiently.

But as data is grow day by day, as a developer we have to look into database side as well.

I am writing article "How to deal with large Sitecore database" in two parts and this first article focus on how to reduce Sitecore database size?

Shrink Sitecore database

Many times I faced low disk space issue on different servers for most of the Sitecore projects.

In some projects - only the size of Master database is more than 20 GB. To maintain them, we have to purchase extra disk space from hosting provider.

Sometimes organization IT team is also facing challenges to maintain heavy databases for Sitecore development projects. Backup of these large databases is again an extra overhead and need extra disk spaces.

So if you want to reduce the database size, then you must know how SQL server storing data internally.

SQL server creating two file for each database - MDF and LDF. MDF file contains your actual data where as LDF file contains database log.

As Sitecore creates its own log in file system at "\data\logs" folder and if size of this folder grows we are deleting these log files time-to-time. In same way we can delete this database LDF log too, which ultimately reduce the size of the total database.

In equation form

Size of MDF file + Size of LDF file = Sitecore Database Size

MDF and LDF files

If I clear my LDF log file then 

Size of MDF file = Sitecore Database Size

Database size as MDF file size

Below script gets all log file of all databases having log file size more than 100 MB and shrink them to 1 MB.

DECLARE db_cursor CURSOR FOR 

SELECT db.name AS DBName, mf.Name AS Logical_Name

FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id Where type_desc = 'LOG' AND (size*8)/1024 > 100

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name, @logical_name  

WHILE @@FETCH_STATUS = 0  

BEGIN  

              exec('USE ' + @name + ' ;ALTER DATABASE ' + @name + ' SET RECOVERY SIMPLE;DBCC SHRINKFILE ('''+@logical_name +''', 1);ALTER DATABASE '+@name +' SET RECOVERY FULL')

              FETCH NEXT FROM db_cursor INTO @name, @logical_name  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

This script decreases the disk space usage on any Sitecore database hosted server. Below are the benefit of this technique

1.         Decrease disk space size by 10-80%

2.         Cost effective - its avoid the purchase of extended disk from Rackspace / hosting provider

3.         It takes less than 5 min to execute

4.         Improve the performance of Sitecore website

5.         Automate - Create a job in SQL server.

6.         We can apply this technique to any project on any environment

I have used this script several times and it works like a charm.

I hope you like this Sitecore database trick. Stay tuned for part-II article.

Till that happy Sitecoring :)

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

Download this eBook, to know more about Sitecore databases.