Code2night
  • Home
  • Blogs
  • Tutorial
  • Post Blog
  • Tools
    • Json Beautifier
    • Html Beautifier
  • Members
    • Register
    • Login
  1. Home
  2. Blogpost
30 Apr
2022

Batch Script for Creating Database backups from Sql server

by Shubham Batra

2374

Batch File (.bat)

We can use a .bat file to write script that can automate database backup from server.You can take one text file and write this script

@ECHO OFF
CLS
REM SET VARIABLES VALUES 

SET SqlServer=Code2night\SQL2017
SET InstanceName=Code2night\SQL2017
SET Username=sa
SET Password=Admin123

SET BaseFolderPath=C:\DatabaseScript\DatabaseScriptBackup
SET TodayDate=%DATE:/=%
SET SubFolder=%Backup%
SET LocalFolder=%BaseFolderPath%\%SubFolder%
SET SourceFile=C:\DatabaseScript\Backup
SET limit = (Get-Date).AddMinutes(-1)
SET Databases=%LocalFolder%\Databases.txt
IF NOT EXIST %LocalFolder% mkdir %LocalFolder%

:: *****************************************************************************

REM date
set mydate=%date:~7,2%%date:~4,2%%date:~10,4%

REM hour
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%

REM min
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%

REM secs
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%

ECHO DB Backup finished...

REM SETLOCAL
setlocal EnableDelayedExpansion

REM SAVE DATABASES LIST IN TEMP FILE
SqlCmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name]  IN ('ALISTrucking','ALISTruckingClean','ALISTruckingPreProd')" > "%Databases%"

REM SAVE BACKUP OF EACH DATABASE

FOR /F "tokens=* delims= " %%I IN (%Databases%) DO (
 
ECHO Backing up database: %%~nI 

REM SAVE BACKUP OF EACH DATABASE into folder with DDMMYYHHMMSS
sqlcmd -S %SqlServer% -U %Username% -P %Password%  -Q "BACKUP DATABASE %%~nI TO  DISK = '%LocalFolder%\%%~nI_%mydate%%hour%%min%%secs%.bak' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;"

ECHO.)

REM DELETE TEMP DATABASES LIST
IF EXIST "%Databases%" DEL /F /Q "%Databases%"

ENDLOCAL

ECHO DB Backup finished...

 

SqlServer: This must be the sql server instance name to connect to your sql server.

UserName: This must be the sql server Login usernname to connect to your sql server database.

Password: This must be the sql server login password to connect to your sql server.

BaseFolderPath: This must be the location of folder where you want your database backups to be saved.

Executing Batch Script

AFter adding the script you have to save the file as .bat extension. And then you have to double click on the file to save the script. After that go to the location and check if you have your database backups there.

So, this is how you can create Batch Script for Creating Database backups from Sql server

  • |
  • Batch Script , Database backup , Powershell , ASpNet , Sql Server Backup

Comments

Follow Us On Social Media - Like Us On Facebook

Best Sellers

product 1

Hand Hug Bracelet For Women Men Cuff Bangle Adjustable Lover Couple Bracelets

Can be given as a gift to your family, relatives, or friends

Buy $15.99
product 1

Teddy bear hug bear plush toy bear cub

Can be given as a gift to your family, relatives, or friends


Buy $49.99

Tags

LinkedinLogin
LinkedinProfile
GetLinkedinProfile
C#
Aspnet
MVC
Linkedin
ITextSharp
Export to Pdf
AspNet Core
AspNet
View to Pdf in Aspnet
Model Validation In ASPNET Core MVC 60
Model Validation
Model Validation In ASPNET Core MVC
Model Validation In ASPNET
Image Compression in AspNet
Compress Image in c#
AspNet MVC
Thank you for Downloading....!

Subscribe for more tutorials

Support our team

Continue with Downloading

Welcome To Code2night, A common place for sharing your programming knowledge,Blogs and Videos

  • Panipat
  • info@Code2night.com

Links

  • Home
  • Blogs
  • Tutorial
  • Post Blog

Popular Tags

Copyright © 2023 by Code2night. All Rights Reserved

  • Home
  • Blog
  • Login
  • SignUp
  • Contact
  • Terms & Conditions
  • Refund Policy
  • About Us
  • Privacy Policy
  • Json Beautifier