{"id":8576,"date":"2014-08-30T11:01:22","date_gmt":"2014-08-30T11:01:22","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=8576"},"modified":"2014-08-30T11:01:22","modified_gmt":"2014-08-30T11:01:22","slug":"crm-database-auto-backup-on-sql-express-editions","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/","title":{"rendered":"CRM database auto-backup on SQL express Editions"},"content":{"rendered":"

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.
\n\u00a0New Stuff:<\/strong><\/em><\/span> Sage 100 user mapping with Sage CRM User<\/a>
\nFull 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.\u00a0 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.
\nSQL File Creation<\/strong>:
\n1.Create one folder in any drive say \u201cSQL backup<\/strong>\u201d folder in D drive and in that folder create a file named \u201cBackupDb.sql<\/strong>\u201d. Open this file in text editor and write following script to the file.
\nDECLARE @name VARCHAR(50) — database name <\/em><\/span>
\n DECLARE @path VARCHAR(256) — path for backup files <\/em><\/span>
\n DECLARE @fileName VARCHAR(256) — filename for backup <\/em><\/span>
\n DECLARE @fileDate VARCHAR(20) — used for file name <\/em><\/span>
\n DECLARE @bcname VARCHAR(20) — used for backup name <\/em><\/span>
\n SET @path = ‘<Path of Backup Location>’ <\/em><\/span>
\n SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) <\/em><\/span>
\n DECLARE db_cursor CURSOR FOR <\/em><\/span>
\n SELECT name <\/em><\/span>
\n FROM master.dbo.sysdatabases <\/em><\/span>
\n WHERE name IN (‘<Database Name>’) <\/em><\/span>
\n OPEN db_cursor <\/em><\/span>
\n FETCH NEXT FROM db_cursor INTO @name <\/em><\/span>
\n WHILE @@FETCH_STATUS = 0 <\/em><\/span>
\n BEGIN <\/em><\/span>
\n SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ <\/em><\/span>
\n SET @bcname = @name + ‘-‘ + ‘Full Database Backup’ <\/em><\/span>
\n BACKUP DATABASE @name TO DISK = @fileName <\/em><\/span>
\n BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, INIT, NAME = @bcname, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/em><\/span>
\n FETCH NEXT FROM db_cursor INTO @name <\/em><\/span>
\n END <\/em><\/span>
\n CLOSE db_cursor <\/em><\/span>
\n DEALLOCATE db_cursor<\/em><\/span>
\n GO<\/em><\/span>
\nIn the above script, you need to mention below Path of Backup Location<\/strong> and Database Name<\/strong> variables.
\n<Path of Backup Location><\/strong>:\u00a0 Mention the path where you need to take backup of the database. \u00a0For e.g. \u201cD:\\SQL backup\u201d
\n<Database Name><\/strong>: Name of the database which you want to backup
\nBat File Creation<\/strong>:
\n1.Create a file named \u201cBackupDb.bat<\/strong>\u201d in the above mentioned \u201cSQL backup<\/strong>\u201d folder in D drive.
\n2.In this file write following command.
\nsqlcmd -S <ServerName><\/strong> -U <UserName><\/strong> -P <Password><\/strong> \u00a0-i <Path of SQL file><\/strong><\/em>
\nBelow are the parameters of above command.
\n<ServerName >:<\/strong> SQL Server Name
\n<UserName><\/strong>:\u00a0 User name of SQL Server
\n<Password><\/strong>: Password of SQL Server
\n< Path of SQL file<\/em>><\/strong>: Mention the path of above SQL file. i.e. \u201cD:\\SQL backup\\BackupDb.sql\u201d
\nSchedule a Job<\/strong>:<\/p>\n

1.Go to Control Panel | Administrative Tools | Task Scheduler<\/p>\n

2.Click on Create Basic Task<\/b> button<\/p>\n

3.Provide Job Name<\/b> and Description<\/b>. Please refer the below given screen shot for your reference.<\/p>\n

\"SQL1\"<\/a><\/p>\n

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.<\/p>\n

\"SQL2\"<\/a><\/p>\n

5.Click on Actions tab and click on New<\/b> button. In Program\/script<\/b> section browse the path of .bat file created above. For e.g. D:\\SQL backup\\BackupDb.bat and click OK button and Save it.<\/p>\n

\"SQL3\"<\/a><\/p>\n

After following above steps, new job will be created to take backup of database which will run every morning @6 AM.
\nAlso Read:<\/strong><\/em><\/span>
\n1) Sage CRM Database Maintenance<\/a>
\n2) How to identify and backup correct Sage CRM Database<\/a>
\n3) How to access views of Different Database in CRM<\/a>
\n4) Access field value from database and display the same on the screen using client side script<\/a>
\n5) CRM database daily backup<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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. \u00a0New Stuff: Sage 100 user mapping with Sage CRM\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73,102,204,332,380,1],"tags":[607,740,793,921,1471],"class_list":["post-8576","post","type-post","status-publish","format-standard","hentry","category-configuration","category-database-sage-crm","category-js-file","category-sage-crm","category-sql","category-uncategorized","tag-bat-file","tag-configuration","tag-crm","tag-database","tag-javascript"],"yoast_head":"\nCRM database auto-backup on SQL express Editions - Sage CRM \u2013 Tips, Tricks and Components<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CRM database auto-backup on SQL express Editions - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"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. \u00a0New Stuff: Sage 100 user mapping with Sage CRM\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2014-08-30T11:01:22+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg\" \/>\n<meta name=\"author\" content=\"greysagecrm\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greysagecrm\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/\",\"name\":\"CRM database auto-backup on SQL express Editions - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg\",\"datePublished\":\"2014-08-30T11:01:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#primaryimage\",\"url\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg\",\"contentUrl\":\"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CRM database auto-backup on SQL express Editions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\",\"name\":\"Sage CRM \u2013 Tips, Tricks and Components\",\"description\":\"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\",\"name\":\"greysagecrm\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"greysagecrm\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CRM database auto-backup on SQL express Editions - Sage CRM \u2013 Tips, Tricks and Components","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/","og_locale":"en_US","og_type":"article","og_title":"CRM database auto-backup on SQL express Editions - Sage CRM \u2013 Tips, Tricks and Components","og_description":"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. \u00a0New Stuff: Sage 100 user mapping with Sage CRM\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2014-08-30T11:01:22+00:00","og_image":[{"url":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg","type":"","width":"","height":""}],"author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/","name":"CRM database auto-backup on SQL express Editions - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#primaryimage"},"image":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#primaryimage"},"thumbnailUrl":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg","datePublished":"2014-08-30T11:01:22+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#primaryimage","url":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg","contentUrl":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-content\/uploads\/2014\/08\/SQL1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2014\/08\/30\/crm-database-auto-backup-on-sql-express-editions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"CRM database auto-backup on SQL express Editions"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/","name":"Sage CRM \u2013 Tips, Tricks and Components","description":"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f","name":"greysagecrm","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"greysagecrm"},"url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/8576","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/comments?post=8576"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/8576\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=8576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=8576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=8576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}