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.



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

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