How to restore SQL database in a VM.

Question

We are planning to restore the SQL database in a VM.

To a target VM. After restoring the source VM from the latest available snapshot.

Please provide the procedure to restore the SQL database , to the target VM.

King Regards,

Harish Nagaraja

Answer 1

There is no difference from restoring the database to a physical server. For instance:

RESTORE DATABASE db FROM DISK = '\\server\share\DB.bak'
WITH MOVE 'db_data' TO 'D:\MSSQL\Data\Db.mdf',
     MOVE 'db_log' TO 'D:\MSSQL\Log\DB.ldf'

One way or another the backup must be readable from the VM (again, no different from a physical server). Either you copy the backup to the VM, or you put it on a file share.

Answer 2

Hi @Harish Nagaraja

Here are the possible steps to restore a SQL database from one SQL Server to another.

If you already have a backup from a source use that backup to restore on destination server else

1.      Create a full backup of the source database on the original SQL Server.

You can use SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) to create the backup.

You can find the script and GUI method of taking backup here- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver16#take-a-backup

2. Copy the backup file to the destination SQL Server. You can use any method to copy the file, such as a shared folder, FTP, robocopy command or cloud storage.
3. Restore the backup file on the destination SQL Server.

You can use SSMS or T-SQL to restore the backup. Here are the steps to restore the backup using SSMS:

You can find the script and GUI method of restoring backup here- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver16#restore-a-backup

4. After the restore process completes, you may need to update the database properties, such as the owner, compatibility level, and recovery model, to match the original database.

Reference documents- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver16

https://www.mssqltips.com/sqlservertip/3113/sql-server-database-restore-with-move-or-not-with-move/

Hope this helps!

Let us know if you encounter any issue.

Thank you!