Auto backup of MSSQL database on SQL express 2012

Question

Auto backup of MSSQL database on SQL express 2012

Rajeev Mishra 1

Hi

I want to schedule auto back up of my SQL database in sql express 2012 edition. For that I used batch file and it works on SQL server 2017 but it says login failed when it executes on express edition. Batch Code is as under:

@Echo off
cls

set SqlServer=LAPTOP-GN1OEU2R
set InstanceName=SQLEXPRESS
set Username=sa
set Password=Olwtswl@123
set Database=StatconNoida
set LocalFolder=D:\SQLBackups

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.

Thanks in advance

YufeiShao-msft 7,146 Reputation points

2021-10-20T07:33:02.96+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

3 answers

Your answer

YufeiShao-msft 7,146 Reputation points

2021-10-20T07:33:02.96+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

Olaf Helper 47,436

but it says login failed when it executes on express edition

Has nothing to do with the edition.
You use SQL login for authentication; is your SQL Server running in "Mixed Mode" = SQL + Windows auth enabled? Default is Windows auth only.

Olaf Helper 47,436 Reputation points

2021-10-19T07:24:15.413+00:00

set InstanceName=SQLEXPRESS

Additional, you defined an instance name, but never use it. Is it a named instance? If so use "LAPTOP-GN1OEU2R\SQLEXPRESS" as server name to logon.
Rajeev Mishra 1 Reputation point

2021-10-21T06:39:23.64+00:00

I checked for the instance name using services.msc and it is MSSQLSERVER. I changed Instance name to MSSQLSERVER and still it doesn't start back up.

set SqlServer=WIN-6S16M45PFE8
set InstanceName=MSSQLSERVER
set Username=sa
set Password=dfgJHG8579%
set Database=StatconNoida
set LocalFolder=C:\DB

I also checked it by setting the server name with instance.
set SqlServer=WIN-6S16M45PFE8/MSSQLSERVER
set InstanceName=MSSQLSERVER
set Username=sa
set Password=dfgJHG8579%
set Database=StatconNoida
set LocalFolder=C:\DB

Even by changing instance name to SQLEXPRESS, it doesn't work.

When I login using the credentials directly on SSMS. I can login.
For windows authentication
WIN-6S16M45PFE8
No password required.

Using SQL authentication
103.228.113.208
SA
dfgJHG8579%

Both mode works but batch file to take auto back is not working on SQL Express 2012.

I checked this batch file on SQL server 2017 and it works here.

I am stuck with this problem. What is wrong here? Server is located on cloud and I am creating the batch file on the server.

Thanks
Olaf Helper 47,436 Reputation points

2021-10-21T08:10:08.37+00:00
set SqlServer=WIN-6S16M45PFE8/MSSQLSERVER
set InstanceName=MSSQLSERVER

That's wrong, too. MSSQLSERVER is the display name or an unnamed (standard) instance, you can not us it as instance name; remove it.

and if you have a named instance, then you have to add with a backslash \, not with a slash, like

set SqlServer=WIN-6S16M45PFE8\SQLEXPRESS

Answer 2

YufeiShao-msft 7,146

Hi @Rajeev Mishra ,

Please try to use mixed authentication. On SSMS, server properties-> security undere Server authentication, select SQL Server and Windows Authentication mode

you backup of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password.

There is a doucment about schedule and automate backups of SQL Server databases in SQL Server Express, you can refer to:
https://learn.microsoft.com/en-us/troubleshoot/sql/admin/schedule-automate-backup-database

-------
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

YufeiShao-msft 7,146 Reputation points

2021-10-26T07:58:27.227+00:00

Please do you have any progress to share, and remember to accept the answers if they are helpful. Your actions will be helpful to other users who are experiencing the same problem and reading this thread.

Answer 3

Tom Phillips 17,771

Forget about the script and figure out your login.

Open a command prompt and run:

sqlcmd -S SqlServer -U Username -P Password

Until it works.

Also, please post the exact message you are receiving.

Share via

Auto backup of MSSQL database on SQL express 2012

3 answers

Your answer