Backup database sql server express using .bat

To generate a backup using sql server express, you can use 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 by using a script that backs up all instances of the instance:

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 server \ SQLEXPRESS -i "C: \ backupsqlserver.sql"

I back up multiple clients that have SQL Server.

  

When you place a task that calls a file.bat by the   to run, it leaves the System32 directory as   so you can solve this problem by changing the directory   to get to the directory that is the executable that will be backed up by the    CD command Example: CD "MEU DIRETÓRIO" or just putting the   executables required in the System32 folder, particularly what I   regardless of where the bat is, it will work because   the executables called on it will be recognized by Windows.

     

I use the SQL executable itself (sqlcmd.exe), 7-zip (7za.exe) to   and Windows forfiles.exe to delete the 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