As I mentioned in last week’s post, using the Full recovery model in SQL in an Infor CRM database would prevent some data loss in the event of a database failure, but would require more careful management of the SQL logs. In today’s post, I will expand on that.
When using the Full recovery model in SQL, every transaction is retained in the logs until the database is successfully backed up. So when restoring, SQL can use the transaction records in the logs to bring the database to a point in time very near the point of failure. However, this means you need to monitor the database backups to make sure they are running, as the log file will keep growing and growing if backups are not running. Regardless of which recovery model you are using, you will want to schedule regular backups.
You can backup a SQL database by right clicking on the database, pointing at Tasks on the pop-up menu, then choosing Back Up. Just running a backup like this will backup the database once. To run regular backups, after getting the backup configured, click the dropdown arrow on the Script menu, and select Script Action to Job. This will allow you to set up the backup as a recurring job.
I am not going to go into all the options here, but I will mention you need to click New on the Schedules page to set up the schedule. That screen will look familiar to anyone who has scheduled anything is Windows recently. Below, I have scheduled the backup to run every weeknight at 11pm. You can set whatever schedule you like, depending on how fast you log file is growing, and other factors such as your company’s retention policy.