question

RajeevMishra-6299 avatar image
0 Votes"
RajeevMishra-6299 asked Yufeishao-msft commented

Auto backup of MSSQL database on SQL express 2012

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

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.



0 Votes 0 ·

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

0 Votes 0 ·

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





0 Votes 0 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @RajeevMishra-6299,

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://docs.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".


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.