Microsoft SQL Server is a relational database management system (RDBMS) that stores and retrieves data for a variety of software applications in the enterprise environment. While .Bak is a file name extension or file used to store data backups by software tools. And it is often employed to store SQL database contents.
SQL Server database backup and restore involve tools like SSMS, T-SQL, and SQLCMD. Or you can use an easier alternative solution. Here in this blog, you will learn multiple methods for SQL Server backup and restore from .Bak file. Without further ado, let’s get started.
How to Backup Database SQL Server into a .Bak File?
1. Backup SQL Server into a Bak File Using SSMS (SQL Server Management Studio)
1) Launch SSMS and connect it to your server instance.
2) Choose the database instance you want to backup in Object Explorer.
3) Right-click and select Tasks> Backup.
4) Select backup type and destination.
5) Click on add... to add a backup file named database path .bak.
6) Click OK to create the job.
2. Backup SQL Server Database with Transact-SQL Command
1) SQL Server Full Backup
BACKUP DATABASE databaseexample
TO DISK = 'filespath';
2) SQL Server Differential Backup
BACKUP DATABASE databaseexample
TO DISK = 'filespath'
WITH DIFFERENTIAL;
3) SQL Server Transaction Log Backup
BACKUP LOG databaseexample
TO backupdevice_created_before;
GO
Before database recovery, you should know:
Disconnect the database active connections to prevent failure.
All methods mentioned allow only recovery from old versions to new versions.
Perform transaction log backup before full backup in case of transaction loss.
If you want to restore a database from another server, manage metadata to make it accessible on another server.
How to Restore SQL Server Database from .Bak File?
1. Restore Database SQL Server from .Bak File with SSMS
1） Open SSMS and connect to the server instance.
2） Right-click Databases> Restore Database… in the Object Explorer pane.
3） Choose Device for Source and click … icon on the right to Add the .bak file, click OK. Again, click OK to return to the previous window. Enter the full name to locate the file should you cannot find it.
4） Check all the information entered and click OK to restore the SQL Server.
2. Restore Database SQL Server from .Bak File with T-SQL
1) Start SSMS and connect it to the SQL Server instance.
2) Click New Query and run the restore statement in the Query Editor window.
3) Restore the whole database.
RESTORE DATABASE databaseexample FROM DISK = 'filespathfilesname.bak'
Restore a file.
RESTORE DATABASE FILE filebame = FROM DISK = ' filespathfilesname.bak '
Restore from differential backup.
RESTORE DATABASE databaseexample FROM DISK = 'filespathfilesname.bak' WITH NORECOVERY
GO
RESTORE DATABASE databaseexample FROM DISK = 'filespathfilesname.bak' WITH RECOVERY
GO
4) Hit Execute to run the command lines.
3. Restore Database SQL Server from .Bak File with SQLCMD
SQLCMD is affiliated with SQL Server 2014 and lower versions.
1) Hit Win + R and enter “cmd”. Click OK.
2) Run the following command lines.
Restore a SQL Server differential backup.
SqlCmd -E -S .Servername -Q “RESTORE DATABASE databaseexample FROM DISK='filespathfilesname.bak' WITH NORECOVERY”
SqlCmd -E -S .Servername -Q “RESTORE DATABASE databaseexample FROM DISK=' filespathfilesname.bak' WITH RECOVERY”
Restore a SQL Server full backup.
SqlCmd -E -S servername -Q “RESTORE DATABASE databaseexample FROM DISK='filespathfilesname.bak'”
Simpler Alternative to Backup and Restore SQL Server
Methods mentioned above for SQL Server backup and restore are the most common ways and widely used by SQL users, which allow them to back up and restore database contents inside SQL Server. However, not all versions support the features and they could result in errors due to an oversight with the command or file locations.
Hence, I’d like to introduce Vinchin Backup & Recovery, a data solution that backs up and restores SQL Server in physical and virtual machines with central management, database compression, SQL Server hot backup, database consistency auto-check, etc.
The solution supports Windows systems including Windows Server 2008, 2012, 2016, and 2019.
You can download 60-day full-featured free trial here:
1. How to Backup SQL Server Database?
There are several things to be done before we proceed: we need to install and register the SQL Server backup agent on the login screen, here, I’ll focus on database backup and recovery operations of the software.
1) Select the backup source from the left column, expand the database instances, and choose the database (s) to be backed up.
2) Specify a backup destination.
You can choose the backup node on which you want the backup data to be processed and stored from the Target Node selection list.
The storage associated with the chosen backup node can be chosen from the Target Storage selection list.
3) Configure the database backup strategies.
Set up the backup schedules, backup types (Full Backup, Differential Backup, Log Backup), data storage policy, retention policy, etc.
Review and submit the backup job after the setup.
You can check the running process by clicking the job name.
2. How to Restore SQL Server Database?
1) Select the target restore point under the database.
2) Select the target database instance on which you want to restore.
3) Configure restore strategy.
Override Original Database and Create New Database (recommend to use first for data verification) are the two alternatives for database restoration. Then set up the new database.
Confirm all the information entered and submit the job.
To manage all the database backup data, navigate to Database Backup> Backup Data.
Conclusion
There are 2 methods for SQL Server backup and 3 ways to restore the database inside the SQL Server. You can also use a reliable and simple third-party data solution, Vinchin Backup & Recovery, to backup and restore SQL Server easily without dazzling command lines. It also supports backup and recovery of MySQL, Oracle, and PostgreSQ databases.
