Table of Contents
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 or Powershell - SQLCMD
1. First, right click on Start button, then click Windows PowerShell admin.
2. For a backup enter the following in the PowerShell window. Don’t forget replace your database name and backup location.
sqlcmd -e -s touch -q "backup database KAV to disk = 'D:\Backup\KAV.bak'"
3. To restore a database, use the following in the PowerShell window. Don’t forget replace your database name and backup location.
sqlcmd -e -s touch -q "restore database KAV from disk = 'D:\Backup\KAV.bak'" 
Using T-SQL to backup and restore a SQL database
1. For backup a database to a file, run below command. Don’t forget replace your database name and backup location.
backup database [KAV] to disk='D:\Backup\KAV.bak'
2. For restore a database from a backup file, run below command. Don’t forget replace your database name and backup location.
restore database [KAV] from disk='D:\Backup\KAV.bak'
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.
