SQL Server Backup Script

  • Hello all,

    not sure if I'm on the right thread but if not please do point me to the correct one.

    basically I have a script that run every end of the month to do a backup of a database (Detach, Mount a new, and move the database to a new location) bu since this month this is coming back with an error when running it. as far as i know nothing has changed since two or three months ago but this is not longer working.

    Assistance is really needed. here.

    Here is my code but this is now coming back with

    Msg 5105, Level 16, State 4, Server PRITL1, Line 1

    Device activation error. The physical file name

    'E:\SQL\MSSQL\Data\Syslogd.mdf' may be incorrect.

    @echo off

    rem ***************************************************

    rem * This script automates the Syslog Backup process *

    rem ***************************************************

    echo 192.168.0.9 > report.txt

    date /t >> report.txt

    time /t >> report.txt

    blat report.txt -to itgroup@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Starting Syslog DB Maintenance" -server 192.168.0.26

    echo ******************************************* >> report.txt

    echo * Stopping the Kiwi Syslog Daemon Service * >> report.txt

    echo ******************************************* >> report.txt

    net stop "Kiwi Syslog Daemon"

    for /F "tokens=3 delims=: " %%A in ('sc query "Kiwi Syslog Daemon" ^| findstr " STATE"') do (

    if /I "%%A" NEQ "RUNNING" (

    echo Stopping the Kiwi Syslog Daemon Service succeeded >> report.txt

    echo. >> report.txt

    ) else (

    echo -----!!! Stopping the Kiwi Syslog Daemon Service failed !!!----- >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    )

    )

    echo ********************************** >> report.txt

    echo * Detaching the Syslogd database * >> report.txt

    echo ********************************** >> report.txt

    ping 127.0.0.1

    ping 127.0.0.1

    osql -E -b -Q "master..sp_detach_db 'Syslogd'" >> report.txt

    set ReturnCode=%errorlevel%

    if %ReturnCode% equ 0 (

    echo Detaching the Syslogd database succeeded >> report.txt

    echo. >> report.txt

    ) else (

    echo. >> report.txt

    echo -----!!! Detaching the Syslogd database failed !!!----- >> report.txt

    echo. >> report.txt

    echo ********************************************* >> report.txt

    echo * Restarting the Kiwi Syslog Daemon service * >> report.txt

    echo ********************************************* >> report.txt

    net start "Kiwi Syslog Daemon"

    for /F "tokens=3 delims=: " %%A in ('sc query "Kiwi Syslog Daemon" ^| findstr " STATE"') do (

    if /I "%%A" NEQ "STOPPED" (

    echo Restarting the Kiwi Syslog Daemon Service succeeded >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    ) else (

    echo -----!!! Restarting the Kiwi Syslog Daemon Service failed !!!----- >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    )

    )

    )

    echo *************************************************** >> report.txt

    echo * Moving the Syslogd database to the _Burn folder * >> report.txt

    echo *************************************************** >> report.txt

    Move E:\SQL\MSSQL\Data\Syslogd*.* \\server\Syslogs2\_Burn\

    set ReturnCode=%errorlevel%

    if %ReturnCode% equ 0 (

    echo Moving the Syslogd database to the _Burn folder succeeded >> report.txt

    echo. >> report.txt

    ) else (

    echo -----!!! Moving the Syslogd database to the _Burn folder failed !!!----- >> report.txt

    echo ************************************ >> report.txt

    echo * Reattaching the Syslogd database * >> report.txt

    echo ************************************ >> report.txt

    osql -E -b -Q "master..sp_attach_db 'Syslogd','E:\SQL\MSSQL\Data\Syslogd.mdf','E:\SQL\MSSQL\Data\Syslogd_log.ldf'" >> report.txt

    echo. >> report.txt

    echo ********************************************* >> report.txt

    echo * Restarting the Kiwi Syslog Daemon service * >> report.txt

    echo ********************************************* >> report.txt

    net start "Kiwi Syslog Daemon"

    for /F "tokens=3 delims=: " %%A in ('sc query "Kiwi Syslog Daemon" ^| findstr " STATE"') do (

    if /I "%%A" NEQ "STOPPED" (

    echo Restarting the Kiwi Syslog Daemon Service succeeded >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    ) else (

    echo -----!!! Restarting the Kiwi Syslog Daemon Service failed !!!----- >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    )

    )

    )

    echo **************************************** >> report.txt

    echo * Restoring the blank Syslogd database * >> report.txt

    echo **************************************** >> report.txt

    osql -E -b -Q "Restore Database Syslogd from disk = 'E:\SQL\MSSQL\Backup\SyslogD_Blank.bak'" >> report.txt

    set ReturnCode=%errorlevel%

    if %ReturnCode% equ 0 (

    echo Restoring the blank Syslogd database succeeded >> report.txt

    echo. >> report.txt

    ) else (

    echo. >> report.txt

    echo -----!!! Restoring the blank Syslogd database failed !!!----- >> report.txt

    echo ************************************ >> report.txt

    echo * Moving back the Syslogd database * >> report.txt

    echo ************************************ >> report.txt

    Move \\server\Syslogs2\_Burn\Syslogd*.* E:\SQL\MSSQL\Data

    echo ************************************ >> report.txt

    echo * Reattaching the Syslogd database * >> report.txt

    echo ************************************ >> report.txt

    osql -E -b -Q "master..sp_attach_db 'Syslogd','E:\SQL\MSSQL\Data\Syslogd.mdf','E:\SQL\MSSQL\Data\Syslogd_log.ldf'" >> report.txt

    echo. >> report.txt

    echo ********************************************* >> report.txt

    echo * Restarting the Kiwi Syslog Daemon service * >> report.txt

    echo ********************************************* >> report.txt

    net start "Kiwi Syslog Daemon"

    for /F "tokens=3 delims=: " %%A in ('sc query "Kiwi Syslog Daemon" ^| findstr " STATE"') do (

    if /I "%%A" NEQ "STOPPED" (

    echo Restarting the Kiwi Syslog Daemon Service succeeded >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    ) else (

    echo -----!!! Restarting the Kiwi Syslog Daemon Service failed !!!----- >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    )

    )

    )

    echo ******************************************* >> report.txt

    echo * Starting the Kiwi Syslog Daemon service * >> report.txt

    echo ******************************************* >> report.txt

    net start "Kiwi Syslog Daemon"

    for /F "tokens=3 delims=: " %%A in ('sc query "Kiwi Syslog Daemon" ^| findstr " STATE"') do (

    if /I "%%A" NEQ "STOPPED" (

    echo Starting the Kiwi Syslog Daemon Service succeeded >> report.txt

    ) else (

    echo -----!!! Starting the Kiwi Syslog Daemon Service failed !!!----- >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    )

    )

    echo ************************************** >> report.txt

    echo * Compressing the Syslog backup file * >> report.txt

    echo ************************************** >> report.txt

    "C:\program files\winrar\rar.exe" M -df -ep -ag -m5 \\server\Syslogs2\_Burn\PR-Syslog \\server\Syslogs2\_Burn\syslogd*.* >> report.txt

    echo. >> report.txt

    set ReturnCode=%errorlevel%

    if %ReturnCode% equ 0 (

    echo Compressing the Syslog backup file succeeded >> report.txt

    echo. >> report.txt

    ) else (

    echo -----!!! Compressing the Syslog backup file failed !!!----- >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report" -server 192.168.0.26

    exit

    )

    echo **************************** >> report.txt

    echo * Completed * >> report.txt

    echo * Ready for burning! * >> report.txt

    echo * Check Archived files on * >> report.txt

    echo * \\\server\Syslogs2 * >> report.txt

    echo *************************** >> report.txt

    echo. >> report.txt

    date /t >> report.txt

    time /t >> report.txt

    blat report.txt -to itrequest@Mydomain.com -f Syslog.Queen@Mydomain.com -subject "Queen - Syslog DB Maintenance Report - Ready to Burn" -server 192.168.0.26

  • Has somebody tinkered with the registry?

    http://support.microsoft.com/kb/836873

  • I assume you've already checked that this path exists:

    E:\SQL\MSSQL\Data\Syslogd.mdf

  • Nothing has changed at all since the last few months, not even updates.

  • Have you checked if the workaround in that link might apply to your situation?

  • Syslogd_Data.mdf and Syslogd_Log.ldf on my E:\SQL\MSSQL\Data\ hence the script is looking onto the Syslogd*.* and it did work since at least 4 months .

  • Sounds like a no?

  • Sorry didnt get that, what do you mean about sounds like a NO?

Viewing 8 posts - 1 through 7 (of 7 total)

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