@echo off
cls
rem Set these variables to the desired values
set SqlServer=SERVER_NAME (The name of your SQL server)
set InstanceName=MSSQLSERVER (The name of the SQL instance)
set Username=SQL_USERNAME (The username with backup rights)
set Password=SQL_PASSWORD (The password for the above user)
set Database=DATABASE_NAME (The database to be backed up)
set LocalFolder=C:\Temp (Temporary local folder to hold backup, no quotes)
set NetworkFolder="\\remotecomputer\folder" (URL to permanent backup location, include quotes)
rem ************************************
rem * Don't touch anything below here. *
rem ************************************
echo Getting current date and time...
echo.
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select ltrim(convert(date, getdate()))" -h -1') do set CurrentDate=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(hour, getdate())), 2)" -h -1') do set CurrentHour=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(minute, getdate())), 2)" -h -1') do set CurrentMinute=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(second, getdate())), 2)" -h -1') do set CurrentSecond=%%a
echo.
echo Backing up database to %LocalFolder%
echo.
SqlCmd -S %SqlServer% -U %Username% -P %Password% -Q "Backup Database %Database% To Disk='%LocalFolder%\%Database%-%CurrentDate%_%CurrentHour%%CurrentMinute%%CurrentSecond%.bak'"
echo.
echo.
echo Copying backup to %NetworkFolder%
echo.
move /Y %LocalFolder%\%Database%-*.bak %NetworkFolder%
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (8)
Commented:
IMG_20180728_014824.jpg
Author
Commented:It looks like you have two problems:
1. You cannot use a URL, so you would have to map a drive letter to that network share.
2. You are getting a login error. Check your username and password.
Commented:
My UserName and Password is correct, I am able to backing up the Database through SQL Server Management Studio Even the ODBC connection is passed when I checked but Via batch file it is getting error.
Commented:
Great Script.
Thank you
Commented:
View More