[Full Guide] Backup and Restore SQL Database with Command Line
Backup and restore SQL Server database with command line
While users can back up SQL databases using SQL Server Management Studio (SSMS), using command-line backups is often preferred for more advanced operations, especially in free Express editions that lack features like Maintenance Plans and SQL Server Agent.
To backup and restore SQL databases using the command line, you can follow these steps. First, to fully back up all databases, use the command "mysqldump -u [username] -p[password] --all-databases > backup.
We will introduce an easier way to backup all remote SQL databases with flexibility.
How to backup SQL database via command line
To enter Transact-SQL statements, system procedures, and script files in Command Prompt, you need the SQLCMD utility. This utility is included with SQL Server versions 2014 and lower, but if you're using a higher version, you can download it from the link below.
To backup a SQL Server database, you can use the Command Prompt. The basic SQL Server backup command line is: `sqlcmd -S server_name -U user_name -P password -Q "backup database database_name to disk = 'backup_file_path'"`. This command connects to the specified server, user, and password, and then backs up the specified database to the specified file path.
In this command,
• The command can be used with a trusted connection, or with a username and password. To use a trusted connection, use the -T switch. If you want to use a username and password instead, you can replace it with the -U and -P switches.
• database name - the name of the database you want to backup
• TO DISK = 'filepath' - The path where you want to store the created backup files is the location where the backup files will be saved.
I use SQLCMD to backup a database named "example" in a named instance.
Note: We typically refer to the default instance by its server name, and to a named instance by its server name followed by the instance name. You can also find the accurate name by right-clicking an instance and selecting Properties from the menu.
How to backup all databases in a SQL Server instance
To simplify the process of backing up multiple databases in an instance, you can create a stored procedure in the master database. This allows you to specify all databases at once, rather than listing each one individually, making the backup command more efficient and easier to use.
Step 1. To create a new stored procedure, go to Databases > System Databases > master > Programmability > Stored Procedures, right-click it and select New Stored Procedure.
Step 2. Microsoft has provided a script for SQL Express backup, which can be copied into the SQLQuery window and executed to create a stored procedure named sp_BackupDatabases.
This stored procedure defines the databases to be backed up and allows for customization of the exclusion criteria to fit specific needs.
If you're using non-Express SQL editions, you can backup all databases in an instance except system databases by modifying the backup script. For example, under each backup type, you can add the command "DELETE @DBs where DBNAME IN ('master','model','msdb','tempdb')" to exclude system databases from the backup.
Step 3. Once the stored procedure is executed successfully, use the following command line to back up these databases:
The backupType field can have values of "F" for a full backup, "D" for a differential backup, or "L" for a transaction log backup.
If you want to use SQLLogin instead of Windows Authentication, add the -U and -P switches and remove the -E switch. This allows you to specify a username and password for SQL authentication.
Here my example is:
When the backup is complete, a .bak file will be generated for each database.
How to automate the SQL Server database backup
To avoid the hassle of manually setting up backups in the Command Prompt each time, you can automate the process by utilizing Windows Task Scheduler. This allows you to schedule regular backups of your database(s) without having to manually intervene each time.
To auto-backup a stored procedure in SQL Server using a script, you can follow these steps. First, create a stored procedure that will be used to backup the desired stored procedure.
Step 1. Open the text editor and enter the backupdatabases command mentioned above.
Step 2. Save the file as .bat extension.
Step 3. To create a scheduled task, go to Control Panel > Administrative Tools > Task Scheduler > Create Basic Task, and follow the wizard.
To select a Tigger, choose an action from the list, such as "Start a program", and then locate the batch file you created.
How to restore SQL database via command line
The command to restore a SQL database from a backup using the command line is: `sqlcmd -S server_name -E -Q "RESTORE DATABASE database_name FROM DISK = 'backup_file_path'"`
How to solve the "The tail of the log for the database has not been backed up" error?
To resolve the error "The tail of the log for the database "databasename" has not been backed up," you can use the BACKUP LOG WITH NORECOVERY command to backup the log if it contains work you don't want to lose. Alternatively, you can use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to overwrite the contents of the log. This should resolve the issue.
The error occurs because the backup file is older than the database you're trying to restore, and the solution is to use the solution stated in the prompt message.
To restore an older backup and overwrite the existing database, you can use the "WITH REPLACE" switch, which is equivalent to the "Overwrite the existing database" option in SSMS. This can be achieved by running a command like: `RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Path\To\Backup.bak' WITH REPLACE;`
This should solve your SQL database restore issue.
Alternative: Easier backup solution for SQL Server (Express) database
If you're looking for a convenient way to backup your SQL database, using the command line can be a good option. However, if you find the script complicated, especially when backing up multiple databases or instances, there's a simpler alternative with a user-friendly graphical interface.
Qiling Backup is a reliable enterprise backup solution that supports SQL databases and virtual machines within a local area network (LAN). It is compatible with SQL Server 2005 to 2022 and works on all Windows operating systems from Windows 7 and Windows Server 2008 R2 onwards.
The product offers a range of features and benefits to cater to various requirements, providing a convenient and efficient solution for users.
• Centralized and remote backup: Centrally backup all or selected SQL databases.
• Easy-to-use: The software provides an intuitive console, allowing users to easily perform MS SQL backup and restore operations without requiring extensive training. Simply following the software's instructions, anyone can successfully complete these tasks.
• Fast backup and instant recovery: This tool provides a fast speed to complete tasks, saving your time, and ensures data security, giving you peace of mind.
• Automatic SQL backup: Customize a backup task for MS SQL to perform daily, weekly, and monthly backups with ease. The tool supports various backup types, including full, incremental, and differential backups, allowing for flexible and efficient backup strategies.
• Multiple backup locations: To backup SQL server databases, you can save them to a local path, an external storage device, a network share, or a NAS drive. Additionally, you can also archive your backups to an Amazon S3 storage for extra security and accessibility.
You can download the software by clicking the download button, then follow the steps to easily backup and restore SQL server database.
Easy steps to take backup of SQL database
Execute the task. Once it's finished, you will find it in the Backup tab.
✍More useful features:
- Backup Cleanup This feature helps you automatically delete older backup versions, thereby freeing up storage space.
- Email Notification This feature allows you to receive email notifications when a task is either abnormal or successful.
How to restore SQL Server database from backup:
When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.
✍Notes:
- The "Restore to original location" option should only be available if the original location still exists. Otherwise, the user should only be able to select "Restore to new location".
- If you choose "Restore to original location", it will overwrite or delete the original database data, which could be a problem if the original database contains important data. It's recommended to choose "Restore to new location" instead.
Conclusion
This article provides a quick guide to backing up a SQL database using the command line, automating the backup process, and restoring from backups. For those who find this process complicated, an alternative software option - Qiling Backup - is also available.
The script simplifies the backup and restore procedure for SQL Server databases, making it easier to automate backups and restores, particularly when transferring databases to another server.
Related Articles
- Schedule Database Backup in SQL Server 2012/2014/2016/2019
You will learn how to schedule database backup in SQL server 2012/2014/2016 using SQL Server Agent jobs and SQL Serve Maintenance Plans. Also, an easier way is included. - Best Server Centralized Backup Strategy for Multiple Windows Servers
Do you need a centralized server backup strategy to protect multiple Windows servers or PCs on network? Here's a simple solution for home or business use. - How to Use PowerShell Script to Backup Multiple SQL Databases
Have multiple SQL databases that need to be backed up? Go through this article to learn two effective backup solutions – PowerShell script and Qiling Centralized Disk Master.