Backing up SQL Express with batch file and SQL procedure.

Question

Post reply

Backing up SQL Express with batch file and SQL procedure.

mulefeathers

Valued Member

Points: 63
More actions
March 4, 2021 at 9:34 pm
Go to Answer
#3853513

I have a procedure to back up a SQL Express database. It works when used SSMS. I created this and saved as SQLExpressBackup.sql
BACKUP DATABASE [Backup_test] TO DISK = N'C:\DBbackups\Backup_test.bak'
WITH NOFORMAT, INIT, NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
It is stored in a folder on C:\SQLScripts
I have tried using the following batch file to run the code and it doesn't work.
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE" -S
PSV063UWX25\Madison_BU -i "C:\SQLScripts\SQLExpressBackups.sql"
I then altered the code to the following:
ECHO Executing Job
ECHO.
Pause
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE" -S
PSV063UWX25\Madison_BU -i "C:\SQLScripts\SQLExpressBackups.sql"
ECHO Job execution completed
pause
cls
exit
This also fails but it does return some information. The first is a missing argument and second it can't find the path.
I am looking for any help that can be provided to backup this database using windows scheduled task. What argument is missing and why can't the path be found? The second attachment show the file structure on C.

Thank you

Attachments:
You must be logged in to view attached files.
frederico_fonseca

SSCoach

Points: 16212
More actions
March 4, 2021 at 9:39 pm
Answer
#3853518

it needs to be all in same line - based on your output you have the sqlcmd in one line then the server name on a new line.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135503 More actions · Answer 1

You could use a scheduled task to run this Powershell script

( save it as e.g. C:\Powershell\SQLServer_Backup_Full.ps1 )

<#
Create sqlserver backup for all databases of the local default instance

Johan Bijnens 2014-06-17
#>
clear-host 


if ( !(get-module sqlps) ) {
    push-location

    Import-Module -name sqlps -DisableNameChecking

    pop-location
    }

$CurrentYYMMDD = get-date -Format 'yyyyMMdd_HHmm';

Write-output $('{0} Backups Starting [{1}*]' -f ( get-date -Format 'yyyyMMdd_HHmmss' ), $CurrentYYMMDD ) ;

Set-Location SQLSERVER:\sql\$env:computername\default\Databases

foreach ($db in (get-childitem -force | where name -notin ('tempdb','model') | sort database_id -descending ))
    { 
        
        if ( $db.Status -in [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal, [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::AutoClosed ) {
            $DbName = $db.Name ;
            if ( $db.RecoveryModel -ne [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple ){
                Backup-SqlDatabase -Database $DbName -BackupAction Log -BackupFile $('<LocalPath>\Backup\{0}_{1}Log.bak' -f $CurrentYYMMDD, $DbName ) ; # niet voor ExpressEdtn -CompressionOption On ;
            
                }

            Backup-SqlDatabase -Database $DbName -BackupAction Database -BackupFile $('<LocalPath>\Backup\{0}_{1}FULL.bak' -f $CurrentYYMMDD, $DbName ) -Initialize ; # niet voor ExpressEdtn -CompressionOption On ;
            }
        else {
            write-verbose $('{0} Backup [{1}] skipped -> status [{2}]' -f ( get-date -Format 'yyyyMMdd_HHmmss' ), $DbName, $db.Status ) ;
            }
    } 

Write-output $('{0} Backups finised' -f ( get-date -Format 'yyyyMMdd_HHmmss' ) ) ;

$Copy2SafeZone = $true ;
#$Copy2SafeZone = $false ;
$CleanupBakFiles = $true ;

if ( $Copy2SafeZone -eq $true ) {

    Set-Location '<LocalPath>\Backup' ;
    $TargetFolder = $('\\<YourSafeZone>\ShareName$\{0}\Database\' -f $env:computername )
    remove-item "$TargetFolder*.bak" -ErrorAction SilentlyContinue ;
    # copy to safezone
    XCOPY "<LocalPath>\Backup\$CurrentYYMMDD*.bak" $TargetFolder /c /k /h /v /y 

    Write-output $('{0} Copy to Safezone finised' -f ( get-date -Format 'yyyyMMdd_HHmmss' ) ) ;


    if ( $CleanupBakFiles -eq $true ) {
        $TsRef = ( get-date ).AddDays( -1 )
        Get-ChildItem *.bak | where LastWriteTime -lt $TsRef | Remove-Item -Force ;
        }

    }


Write-output $('{0} The end' -f ( get-date -Format 'yyyyMMdd_HHmmss' ) ) ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

mulefeathers Valued Member Points: 63 More actions · Answer 2

Thank you. I figured it was something simple I just wasn't seeing. Thanks again

Backing up SQL Express with batch file and SQL procedure.

Attachments:

Cookies on SQLServerCentral