Wednesday 29 August 2012

Backup MSSQL Database from Command Prompt

Start the command prompt and type the following:

C:\Users\Administrator>sqlcmd -E -Q "backup database databaseName to disk='C:\backups\ databaseName.bak' with format" > "C:\backups\backUpLog.log"

The text which hi-lighted by green color it is the cmd default 

Hintwith format is used to delete the previous backup and create new one, if you want the current backup to be pushed to the previous one just don't type-write- it.



You can backup more than one database in the same command like the following
"backup database databaseName to disk='C:\backups\ databaseName.bak' with format backup database databaseName to disk='C:\backups\ databaseName.bak' with format" and so on

Backup using Batch file:
You can Create sql query file like"backupDBs.sql" and type in it
backup database databaseName to disk='C:\backups\ databaseName.bak' with format
and call it to be executed from batch file which contains the following command
sqlcmd -s . -i "C:\backupDBs .sql" > "C:\backupDBLog.log"
and you can  Schedule it as you want via Scheduled Tasks

or you can place sqlcmd -E -Q "backup database databaseName to disk='C:\backups\ databaseName.bak' with format" > "C:\backups\backUpLog.log" in the batch file

1- Navigate to Control Panel, Scheduled Tasks, Add New Task, and select Command Prompt
2- Change the task path to backupdb.bat, start path to C:\ and set how often you want to backup


Mohammad Abu Hmead

No comments:

Post a Comment