Backup sql server express database using . bat

Question

I created a file .bat with the content:

USE T4FAT;  
GO  
BACKUP DATABASE T4FAT  
TO DISK = 'C:\Dropbox\MF-YCORN\BK_MF\T4FAT.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'T4FATBackups',  
      NAME = 'Full Backup of T4FAT';  
GO 

I added it in task manager Windows server 2012, when testing the file is called more is not doing the backup, within the sql server works.

https://support.microsoft.com/pt-br/kb/2019698

Answer 1

To generate a backup using sql server express, it is possible using the script :

USE T4FAT;  
GO  
BACKUP DATABASE T4FAT  
TO DISK = 'C:\Dropbox\MF-YCORN\BK_MF\T4FAT.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'T4FATBackups',  
      NAME = 'Full Backup of T4FAT';  
GO 

or using a script that backs up all the instance databases:

Example:

Create a file : backupsqlserver.sql

With the content:

DECLARE @name VARCHAR(150) -- Nome do Database  
DECLARE @path VARCHAR(256) -- Caminho do arquivo de backup
DECLARE @fileName VARCHAR(256) -- Arquivo do backup  
DECLARE @dia VARCHAR(10) -- dia do backup
SET @dia = CONVERT (varchar,GETDATE(), 112) --formata o dia no padrao iso (yymmdd)

-- Define caminho de destino do backup
SET @path = 'C:\Dropbox\MF-YCORN\BK_MFCONSULTIN1\'  

-- Cria um cursor para selecionar todas as databases,  
--  excluindo model, msdb e tempdb
DECLARE db_cursor CURSOR FOR  
   SELECT name 
     FROM master.dbo.sysdatabases 
    WHERE name NOT IN ('model','msdb','tempdb')  

-- Abre o cursor e faz a primeira leitura 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

-- Loop de leitura das databases selecionadas
WHILE @@FETCH_STATUS = 0   
BEGIN    
   SET @fileName = LTRIM( @path + @name + @dia + '.bak')  --remove os espacos 
   -- Executa o backup para o database
   BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT;  

   FETCH NEXT FROM db_cursor INTO @name   
END   

-- Libera recursos alocados pelo cursor
CLOSE db_cursor   
DEALLOCATE db_cursor 

Create the file : BACKUP.BAT

With the content:

osql -U sa -P password -S SQLEXPRESS server -i "C: backupsqlserver.sql"

Answer 2

I back up several clients that have SQL Server.

When you set a task that calls a bat file. by the scheduler of tasks to be executed, it is part of the System32 directory as default, so one can solve this problem by changing the directory to get to the directory that is the executable that will be backed up by commando CD Example: CD "MEU DIRETÓRIO" or just putting the executables needed in the System32 folder, particularly what I I do it because no matter where the bat is it will work because the executables called on it will be recognized by Windows.

I use the proper SQL executable (sqlcmd.exe), the 7-zip (7za.exe) for compress and Windows forfiles.exe to erase old backups, follow the batch I made:

@echo off
title "Titulo da Janela"
color b

SET date=
for /F "tokens=1-3 delims=/ " %%a in ('date /T') do set date=%%c%%b%%a

SET time=
for /F "tokens=1-3 delims=: " %%a in ('time /T') do set time=%%c%%a%%b

REM **Variavel que vai guardar a data e hora no backup.
SET DATAATUAL=_%date%-%time%

REM **Nome do Backup para identificar caso faça backups de vários bancos.
SET NOMEBACKUP=Nome

REM **nome ou ip do computador que está com o banco de dados.
SET SQLHOST=localhost


SET SQLDATABASE=nomeDoBancoDeDados

REM **Usuário da base de dados
SET SQLUSER=user

REM **Senha da base de dados
SET SQLPASSWORD=pass

REM **Diretório de destino do arquivo de Backup 
SET DESTBACKUP=C:\


@echo Efetuando o backup do banco de dados, aguarde...
sqlcmd -U %SQLUSER% -P %SQLPASSWORD% -S %SQLHOST% -d %SQLDATABASE% -Q "backup database %SQLDATABASE% to disk='%DESTBACKUP%%NOMEBACKUP%%SQLDATABASE%%DATAATUAL%.bak'" > %DESTBACKUP%%NOMEBACKUP%%SQLDATABASE%%DATAATUAL%.log

7za a "%DESTBACKUP%%NOMEBACKUP%%SQLDATABASE%%DATAATUAL%.7z" "%DESTBACKUP%%NOMEBACKUP%%SQLDATABASE%%DATAATUAL%.bak"

if exist  "%DESTBACKUP%%NOMEBACKUP%%SQLDATABASE%%DATAATUAL%.bak"  del  /q "%DESTBACKUP%%NOMEBACKUP%%SQLDATABASE%%DATAATUAL%.bak"

REM *Apaga backups mais antigos que 31 dias, se quiser alterar é só mudar a quantidade de dias.
forfiles -p %DESTBACKUP% -s -d -31 -m *.7z -c "cmd /c del /f /q "@path" "

timeout /t 5