{"id":51,"date":"2012-04-13T04:33:00","date_gmt":"2012-04-13T04:33:00","guid":{"rendered":"http:\/\/pheonixsolutions.com\/?p=51"},"modified":"2026-07-01T13:40:17","modified_gmt":"2026-07-01T08:10:17","slug":"bacth-script-to-take-backup-of-all-the-database-mssql","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/bacth-script-to-take-backup-of-all-the-database-mssql\/","title":{"rendered":"Batch Script to Take Backup of All MSSQL Databases"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\">Introduction<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Taking regular database backups is an important task for server administrators. In Windows servers running Microsoft SQL Server, we can use a batch script with the <code>sqlcmd<\/code> utility to back up all user-created MSSQL databases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This script helps automate the backup process by listing all databases except system databases and creating <code>.bak<\/code> files in a date-wise backup folder.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Prerequisites<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Before running the script, make sure the following requirements are met:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Windows server with Microsoft SQL Server installed.<\/li>\n\n\n\n<li><code>sqlcmd<\/code> utility should be installed and accessible from Command Prompt.<\/li>\n\n\n\n<li>SQL Server instance name should be known, for example:<\/li>\n<\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">.\\SQLEXPRESS\n<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A valid SQL user account with backup permission should be available.<\/li>\n\n\n\n<li>Backup drive or folder should have enough free disk space.<\/li>\n\n\n\n<li>The backup destination folder should exist or be created by the script.<\/li>\n\n\n\n<li>Avoid using the <code>sa<\/code> account unless required. It is recommended to use a dedicated SQL backup user with limited permissions.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Batch Script<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Open Notepad and paste the below script.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">@ECHO OFF\nSETLOCAL\n\nREM MSSQL Database Backup Script\nREM This script will backup all user-created databases\n\nREM Set date format\nfor \/F \"tokens=2-4 delims=\/ \" %%A in ('Date \/T') DO SET NowDate=%%A-%%B-%%C\n\nREM SQL Server details\nSET SQL_INSTANCE=.\\SQLEXPRESS\nSET SQL_USER=sa\nSET SQL_PASS=YourPasswordHere\n\nREM Backup location\nSET BACKUP_PATH=D:\\Backup\nSET DBList=%SystemDrive%\\SQLDBList.txt\n\nECHO Backup Date: %NowDate%\n\nREM Create backup directory\nIF NOT EXIST \"%BACKUP_PATH%\\%NowDate%\" mkdir \"%BACKUP_PATH%\\%NowDate%\"\n\nREM Get list of user databases\nsqlcmd -S \"%SQL_INSTANCE%\" -U \"%SQL_USER%\" -P \"%SQL_PASS%\" -h -1 -W -Q \"SET NOCOUNT ON; SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')\" > \"%DBList%\"\n\nREM Backup each database\nFOR \/F \"usebackq tokens=* delims=\" %%I IN (\"%DBList%\") DO (\n    IF NOT \"%%I\"==\"\" (\n        ECHO Backing up database: %%I\n\n        sqlcmd -S \"%SQL_INSTANCE%\" -U \"%SQL_USER%\" -P \"%SQL_PASS%\" -Q \"BACKUP DATABASE [%%I] TO DISK='%BACKUP_PATH%\\%NowDate%\\%%I.bak' WITH INIT, COMPRESSION\"\n\n        ECHO Backup completed for: %%I\n        ECHO.\n    )\n)\n\nREM Remove temporary database list file\nIF EXIST \"%DBList%\" DEL \/F \/Q \"%DBList%\"\n\nECHO All database backups completed.\nENDLOCAL\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Steps to Run the Script<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Step 1: Open Notepad<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Open Notepad on the Windows server.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Step 2: Paste the Script<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Copy and paste the above batch script into Notepad.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Step 3: Update SQL Details<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Update the below values based on your server:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SET SQL_INSTANCE=.\\SQLEXPRESS\nSET SQL_USER=sa\nSET SQL_PASS=YourPasswordHere\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SET SQL_INSTANCE=.\\SQLEXPRESS\nSET SQL_USER=backupuser\nSET SQL_PASS=StrongPassword\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Step 4: Update Backup Path<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Update the backup location if required.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SET BACKUP_PATH=D:\\Backup\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The backup files will be stored inside a date-wise folder like:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">D:\\Backup\\04-13-2012\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Step 5: Save the File<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Save the file with <code>.bat<\/code> extension.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">backup.bat\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Make sure the file type is selected as <strong>All Files<\/strong> while saving.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Step 6: Run the Script<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Right-click the <code>backup.bat<\/code> file and select <strong>Run as Administrator<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The script will list all user-created MSSQL databases and generate backup files in the backup folder.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Backup Output<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">After the script runs successfully, the backup files will be stored in the following format:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">D:\\Backup\\DATE\\DatabaseName.bak\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">D:\\Backup\\04-13-2012\\mydatabase.bak\n<\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Important Notes<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Do not store SQL passwords in plain text on shared servers.<\/li>\n\n\n\n<li>Use a dedicated SQL backup user instead of the <code>sa<\/code> account whenever possible.<\/li>\n\n\n\n<li>Make sure the backup folder has enough disk space.<\/li>\n\n\n\n<li>Test the backup file by restoring it on a test server.<\/li>\n\n\n\n<li>Schedule the script using Windows Task Scheduler for automatic daily or weekly backups.<\/li>\n\n\n\n<li>Store an additional copy of the backup in another location for disaster recovery.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Using a batch script is a simple and effective way to take backups of all MSSQL databases on a Windows server. The script automatically collects the list of user databases, creates a date-wise backup folder, and stores each database as a <code>.bak<\/code> file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Regular database backups help protect business-critical data and make it easier to recover from accidental deletion, database corruption, or server failure.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction Taking regular database backups is an important task for server administrators. In Windows servers running Microsoft SQL Server, we [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1019],"tags":[182,204,180,35],"class_list":["post-51","post","type-post","status-publish","format-standard","hentry","category-cloud-aws","tag-backup","tag-batch-script","tag-database","tag-windows","psol-cat-cloud-aws"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/phn2x7-P","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/51","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=51"}],"version-history":[{"count":1,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/51\/revisions"}],"predecessor-version":[{"id":10459,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/51\/revisions\/10459"}],"wp:attachment":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}