Restore database in sqlserver 2016

Question

Current production database is version 2012 r2, used copy database wizard tool to copy database from one server to another while the database was online. Followed the document https://learn.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-ver15 The size of the log file was not the same size as production after restore.

Created a backup from source database and restored to destination database but while restoring got the message "A tail log backup of the source will be taken. View the setting in options. After restored also created a tail log backup.

Can I uncheck the tail log backup and proceed with restore? I have also checked on overwrite existing database.
What steps do I need to follow to complete restore without tail log backup. The source server will not be in use on the day of restore from source to destination.

Answer 1

Since the ldf file didn't have the same size, you used the Transfer method in the Copy Databse Wizard. Not that it really matters...

Yes, you can uncheck that tail log backup. It is checked by default is you are to restore over the existing production database. It defaults to do this "last log backup", in case you need to restore up to the last minute/second. That doesn't seem to be your scenario, and if you aren't interested in having a log backup of the destination database, then just uncheck that option.

Answer 2

Hi @Avyayah ,
In addition, based on my test, if you uncheck the “Take Tail-Log backup before restore” option, there may be an error:

The reason for the error is that the backup file that you are trying to restore is older than the database which exists in destination server.
If this error happened, you can check “overwrite the existing database (WITH REPLACE)” to overwriting the existing database (If the new data in the database on the destination server is not important).
Or You can check this “Take Tail-Log backup before restore” option, then restore the Tail-Log backup.
Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3

The answer as I see for your restore GUI is uncheck the "tail log backup" and select Overwrite the existing database The first check box under restore options on the top. This is saying to SQL Server that I am sure I am ready to restore on the current database. The current issue/ exception you are seeing has been put by MS to avoid accidental restores on the DB.