Database backup is very essential in business environment to keep disaster recovery options open. Maintaining every day database backup is routine practise for all software friendly industries. However doing this task manually is very tedious job and involves lot of risk if someone forgets to backup.
New Stuff: Sage 100 user mapping with Sage CRM User
Full version SQL installs provide a feature to create Maintenance plans using SQL jobs. However express version of SQL do not allow creating jobs as SQL agent does not come as part of SQL express install. My team tried addressing this issue by creating utility that can be configured on windows task scheduler of database server. Here are the steps in case someone is interested.
SQL File Creation:
1.Create one folder in any drive say “SQL backup” folder in D drive and in that folder create a file named “BackupDb.sql”. Open this file in text editor and write following script to the file.
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 @bcname VARCHAR(20) — used for backup name
SET @path = ‘<Path of Backup Location>’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN (‘<Database Name>’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
SET @bcname = @name + ‘-‘ + ‘Full Database Backup’
BACKUP DATABASE @name TO DISK = @fileName
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, INIT, NAME = @bcname, SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
In the above script, you need to mention below Path of Backup Location and Database Name variables.
<Path of Backup Location>: Mention the path where you need to take backup of the database. For e.g. “D:\SQL backup”
<Database Name>: Name of the database which you want to backup
Bat File Creation:
1.Create a file named “BackupDb.bat” in the above mentioned “SQL backup” folder in D drive.
2.In this file write following command.
sqlcmd -S <ServerName> -U <UserName> -P <Password> -i <Path of SQL file>
Below are the parameters of above command.
<ServerName >: SQL Server Name
<UserName>: User name of SQL Server
<Password>: Password of SQL Server
< Path of SQL file>: Mention the path of above SQL file. i.e. “D:\SQL backup\BackupDb.sql”
Schedule a Job:
1.Go to Control Panel | Administrative Tools | Task Scheduler
2.Click on Create Basic Task button
3.Provide Job Name and Description. Please refer the below given screen shot for your reference.
4.Click on Triggers Tab to configure this job for Daily, Weekly or Monthly basis. Also mention the time by when you want this job to execute, and then click on Ok button. Please refer below screenshot for your reference.
5.Click on Actions tab and click on New button. In Program/script section browse the path of .bat file created above. For e.g. D:\SQL backup\BackupDb.bat and click OK button and Save it.
After following above steps, new job will be created to take backup of database which will run every morning @6 AM.
Also Read:
1) Sage CRM Database Maintenance
2) How to identify and backup correct Sage CRM Database
3) How to access views of Different Database in CRM
4) Access field value from database and display the same on the screen using client side script
5) CRM database daily backup