Backup the SQL Server Databases

Modified on Wed, 15 Feb 2023 at 02:29 PM


The Microsoft SQL Server databases are critical as they contain all of the configuration details of the system and all of the call information for every call that has being recorded. If these are lost or corrupted then without a reliable backup you will not be able to search and playback calls using any of the meta data associated with those calls.

Having a reliable and up to date backup of these database enables the system to be recovered in a short period of time without any loss of historical information (up to the point of the last backup).

The system does have built in processes to maintain and backup the databases - see the Database Maintenance section for details.

How To

To backup all of the databases follow this procedure:

1. Log on to the server with the database role.
2. From the Start Menu open Microsoft SQL Server 2008 R2 -> SQL Server Management Studio
3. Set the Server type to Database Engine.
4. Set the Server name to "(local)\MCS".
5. Enter valid Authentication details.
6. Click on Connect.
6. Select File -> New -> Query with Current Connection. This will open a new query window within SSMS.
7. Copy this script into the new query window. This script is configured to backup all the database to the default destination of "c:\Backup\".
Before running this script ensure that there is enough free disk space on the relevant backup drive location.
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 = 'C:\Backup\' 
-- specify filename format
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

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
8. Click on the Execute button to start the backup. The time to run the backup will vary considerably depending on the size of the databases, the speed of the destination backup location and the performance of the server that this is running on.
9. Once complete the results window underneath the query window will show if the backup was a success.
10. Ensure that the backups are copied onto a reliable external storage device.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article