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

In this article I am explaining how to do backup and restore using a 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 the process of copying the data from a SQL Server database and creating a backup file. A restore scenario in SQL Server is the process of 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.

SQL1.jpg

For a backup enter the following in the SQLCMD window:

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

SQL2.jpg

To restore use the following in the SQLCMD window:

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

SQL3.jpg

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:

SQL4.jpg

For restore:

SQL5.jpg

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

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