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

Introduction

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

Conclusion

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.