Backup and Restore Using Command Prompt - SQLCMD and T-SQL


In this article, I explain how to do a backup and restore using an SQLCMD and T-SQL command prompt.

It's one of the most important responsibilities for DBAs. It works across all supported operating systems, whether they are 64-bit or 32-bit systems. A backup scenario in SQL Server is copying the data from a SQL Server database and creating a backup file. A restore scenario in SQL Server is restoring data from one or more backups and then recovering the database.

Using Command Prompt - SQLCMD

Now click Start, click Run, type CMD in the Run dialog box, and then click OK then a black window is opened.


For a backup, enter the following in the SQLCMD window.

Sqlcmd -E -S touch -Q" BACKUP DATABASE [Testing] TO DISK = 'D:\BACKUP\Testing. BAK'"


To restore, use the following in the SQLCMD window:

Sqlcmd -E -S touch -Q" RESTORE DATABASE [Testing] FROM DISK = 'D:\BACKUP\Testing. BAK'"


SQLCMD is the newest, fanciest command-line interface for SQL Server.

[-E trusted connection]
[-S server]
[-Q "CMD line query" and exit]

Using T-SQL- For backup


For restore


Types of SQL Server Backups

The various types of backups that you can create are as follows-

  • Full backups
  • Differential backups
  • File backups
  • File group backups
  • Partial backups
  • Copy-Only backups
  • Mirror backups
  • Transaction log backups


This article taught us about Backup and Restored Using Command Prompt - SQLCMD and T-SQL. 

I will explain in my next article the types of backups and how to create backups and restores.

Similar Articles