Using sqlcmd for SQL Server backups offers a simple, scriptable way to protect data. This guide covers local backup, restore steps, and enhancements to ensure reliability and efficiency in various environments.
Updated by Dan Zeng on 2025/05/15
Backing up and restoring SQL Server databases is a vital task for ensuring data protection and availability. Using the sqlcmd command-line utility, database administrators can efficiently manage backups and restores without relying on a graphical interface. This guide outlines the essential steps for performing a database backup to a local disk, restoring it on the same or a different server, and applying useful enhancements to streamline the process and improve reliability.
You can back up a database to a .bak file on your local disk using the following command:
sqlcmd -S . -E -Q "BACKUP DATABASE YourDatabase TO DISK='D:\backup\YourDatabase.bak'"
Parameter breakdown:
-S . — Connects to the local SQL Server instance.
-E — Uses Windows authentication.
-Q — Executes the SQL query that follows.
TO DISK='path' — Specifies the backup file destination.
Example:
sqlcmd -S . -E -Q "BACKUP DATABASE TestDB TO DISK='D:\backup\TestDB.bak'"
To restore the backed-up database on the same server, use the following command:
sqlcmd -S . -E -Q "RESTORE DATABASE YourDatabase FROM DISK='D:\backup\YourDatabase.bak'"
When restoring on a different server, the original file paths may not exist. In this case, use the WITH MOVE option to relocate the data and log files:
sqlcmd -S . -E -Q "RESTORE DATABASE NewDatabase FROM DISK='D:\backup\YourDatabase.bak' WITH MOVE 'LogicalDataFileName' TO 'D:\Data\NewDatabase.mdf', MOVE 'LogicalLogFileName' TO 'D:\Data\NewDatabase_log.ldf'"
To find the logical file names, run:
sqlcmd -S . -E -Q "RESTORE FILELISTONLY FROM DISK='D:\backup\YourDatabase.bak'"
Look for the LogicalName column in the output — these are the names you need in the WITH MOVE clause.
sqlcmd -S ServerName -U Username -P Password -Q "BACKUP DATABASE ..."
BACKUP DATABASE YourDatabase TO DISK='path' WITH COMPRESSION
Save your backup command in a .bat file and schedule it via Windows Task Scheduler to automate regular backups.
Although sqlcmd offers a lightweight and scriptable way to back up SQL Server, manual management becomes error-prone and inefficient when dealing with more complex requirements such as multiple databases, scheduled backups, retention policies, and disaster recovery.
To enhance the efficiency and reliability of database protection, it is recommended to use Vinchin Backup & Recovery—a powerful, agentless backup solution. It not only supports SQL Server, but also a wide range of other database types including Oracle, MySQL, PostgreSQL, Postgres Pro, and MariaDB. Designed for flexible deployment across both physical servers and virtualized platforms, Vinchin easily meets the demands of complex IT environments. Users can configure full, differential, incremental, and transaction log backup strategies based on their needs, enabling high-frequency, low-intervention data protection.
Vinchin not only offers flexible backup strategies but also simplifies IT operations by centrally managing all backup tasks through a user-friendly web console. For SQL Server, it leverages built-in consistency check tools to validate data integrity, further enhancing reliability. Its advanced backup technology can reduce database backup size by up to 70%, while consuming minimal CPU resources from the production environment—significantly improving both backup efficiency and storage utilization. With intelligent scheduling and automated job management, Vinchin delivers an enterprise-grade database backup solution that combines flexibility, efficiency, and ease of use, helping organizations reduce storage costs and overall maintenance workload.
Vinchin Backup & Recovery's operation is very simple, just a few simple steps.
1.Just select VMs on the host
2.Then select backup destination
3.Select strategies
4.Finally submit the job
✅ Click here now to claim your 60-day free trial of Vinchin Backup & Recovery and effortlessly start your journey toward automated database protection!
Q1: What could be the reason for backup failure?
A1: Path does not exist or permission is insufficient (SQL Server service account needs to have write permission)
Database name is misspelled
Login credentials are incorrect
File is occupied by another process
Q2: Can you use a relative path?
A2: Relative paths are not recommended. It is better to use full paths to avoid path resolution errors.
Backing up SQL Server with sqlcmd is effective for simple tasks, but for scalable, automated, and reliable protection across complex environments, Vinchin Backup & Recovery offers a smarter, agentless solution with broad database support and centralized management.
Share on:
