MS SQL Server Backup using Cmd Windows Command Line Backup with SQLCmd
In this MS SQL Server 2008 tutorial, I will try to summarize the steps how can we back up the database directly from the command line for developers and SQL administrators (DBA).
Instead of using additional SQL Backup software, sql developers can create their small applications as sql backup solutions.
Open a sql query editor tool like SQL Server Management Studio and code your sql backup script (t-sql backup) for your database and save the sql backup script as a file named "sqlcmd-backup-script.sql"
BACKUP DATABASE MySQLSamples
TO DISK = 'C:\SQLDatabases\MySQLSamples.bak';
I placed the sql backup script file sqlcmd-backup-script.sql in the root folder of C drive.
Following step is creating a new windows batch file and editing it with the following SQL command line utility statements.
sqlcmd -S . -i "c:\sqlcmd-backup-script.sql"
SQLCmd utilty will connect to local SQL Server with Windows authentication and execute the t-sql script within the sql file passed with the "-i" parameter.
"-S" defines that the Windows authentication will be used while connecting to SQL Server.
And the MS SQL Server connection will be made with the local SQL Server since the server name is identified with "."
I named the batch file as backup.bat and copied in the root file folder of C drive.
Now we are ready for taking SQL Server backup using the SQLCmd sql command line utility by calling the backup.bat batch script using the windows command line prompt.
As you can see we have build a very simple sql backup software application for our needs.
Run MS SQL Server Backup with Command Line Utility SQLCmd
After we have comleted the windows batch file and the sql backup script file, we can now execute the SQL Server backup using the sql command line utility SQLCmd.
Click on the "Start" menu and then select the "Run..." menu item.
Type "cmd" in the textbox on the displayed screen and click "OK" to run the command prompt screen.
Then be sure that you are on the same folder where the "sqlcmd-backup-script.sql" script is in.
Then type the name of the batch file and execute SQLCmd statement in the batch script for completing our sample windows command line backup.
This will trigger the sql backup command and create a SQL Server backup file.
As you will see in the above screenshot, after calling the windows batch file backup.bat in the windows command screen, the sql command line utility sqlcmd enables us to take the SQL backup of SQL Server database successfully into the file folder where we have configured in the sql backup script file.
T-SQL developers and SQL Server administrators can find a similar sql tutorial at How to Create Full Database Backup on MS SQL Server for a Database using T-SQL "Backup Database" command and SqlCmd Utility.