Post reply

Backing up SQL Express with batch file and SQL procedure.

  • 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.

  • 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.

  • March 5, 2021 at 10:09 am

    #3853732

    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

  • March 5, 2021 at 2:08 pm

    #3853840

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

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

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