How to write command line | Script backup database sql Server 2005

How to write command line | Script backup database sql Server 2005

Start from create the BAT File first. Open the Notepad Program
Copy the Script below to Notepad Program and then you must to change on Highlight .


@ECHO OFF

@echo.
@echo +++ Begining Backup DB +++
sqlcmd -Slocalhost -Usa -Psa -i"bk_app.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
rem cls
@echo.
@echo.
@echo.
@echo.
echo +++ Database Backup successful +++
pause
exit

:err_bkp_failed
@echo Database Backup Failed
pause
===================================================================

Next Step,

Write the SQL Script , I have writed the script to control the part and naming of file so you must to change for highlight.


declare @ty char(4)
declare @tm char(2)
declare @td char(2)
declare @tbkname char(50)

set @ty = '';
set @tm = '';
set @td = '';

set @ty = convert(char,year(getdate()))
set @tm = convert(char,month(getdate()))
set @td = convert(char,day(getdate()))
set @tbkname = 'D:\SLBACKUP\KLK_DBBACKUP\' + @ty + rtrim(@tm) + @td + '_app.bak'
--print @tbkname
BACKUP DATABASE [databasename] TO DISK = @tbkname WITH NOFORMAT, NOINIT, NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

And you don't forget to save the Last name of Sql Script to *.SQL but you develop on SQL Server the defult is *.sql .

when you need to used that you can duble click on BAT file and you can check the result on Backup Path and the peple usefull to BAT file backup for SQL 2005 Express becauase SQL Express it don't have Schedule Backup.




=====================================