Hi all, i'm following the steps below on the mirroring and I'm stuck on
STEP 2 - Restore Database on Secondary Database (Mirror Server).
I have 3 VMs as per the instruction and not sure why i get the error "Msg 3201, Level 16, State 2, line 1
Cannot open backup device 'D:\backup.bak' Operating System error 3
Msg 3013, Level 16, State 1, line 1
RESTORE DATABASE is terminating abnormally."
On step 2 I put it on Mirror VM, it mention about restoring from the VM local drive. I don't quite understand how does it link to the Principal VM local drive ??
You'll either have to copy the backup file to your mirror server, or you'll have to share a network drive holding the backup file. In the latter case, keep in mind that SQL Server service account need to have access to the file share and the backup file.
You only use backup/restore when you configure database mirroring. When it is set up, it uses a technology similar to Service Broker to exchange changes to the database, so you don't have to worry about the backups during failover.
I got this error when trying to configure Step 2. Where can setup this log file ? I'm so sorry but my SQL knowledge is low. I'm just learning ...
Msg 3234, Level 16, State 2, Line 1
Logical file 'MyDatabase_log' is not part of database 'MyDatabase'. Use RESTORE FILELISTONLY to list logical file names.
Msg 3013, Level 16, State 1, Line1
RESTORE DATABASE is terminating abnormally
You presumably used the MOVE option in your restore command to try to move the file 'MyDatabase_log'. Unfortunatley, that file is not known to this database. Check the spelling of the logical name of the file, and see if there is anything obvious.
No, it will remain on the secondary server. Failing back would introduce a small amount of downtime. Think of it, if the "primary" server get some error causing it to reboot (blue screen for instance), would you fail back and have the risk of it happening again, or having it running on your "secondary" server? I would definitely want it to run on the "secondary" server. The same logic applies to clusters, where I may have a preferred node for an instance, but I NEVER configure failback.
If the server is replaced: definitely. If the same server is coming up again: As long as you don't have to reinstall SQL Server or restore the databases, AND you did not have to stop mirroring due to excessive transaction log growth, then the server should catch up when it is getting online again.
Keep in mind, while one of the servers is down, the VLFs (Virtual Log Files) within your transaction log cannot be freed, eventually causing your log to grow. You can see this by
select name, log_reuse_wait_desc from sys.databases
Where log_reuse_wait_desc is DATABASE_MIRRORING if database mirroring prevents SQL Server from freeing up space (VLFs) in your log file.