This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 55.00000000000001%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
After logshipping all databases are in restoring status and for the users to start testing, I have restored the database using the latest transactional log. Is this process correct? Or should I be using RESTORE DATABASE Database Name WITH RECOVERY GO
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
STANDBY isn't an option because the backups are from a lower version of SQL Server. (The database has to be upgraded for the STANDBY recovery do be done, and that cannot be undone at the next restore).
So either restore with RECOVERY and not be able to restore further log backups.
Or restore with NORECOVERY an be able to restore further log backups. But not able to access the database.
Above are the two options.
@tibor_karaszi@hotmail.com , thank you for clarifications. and yes I am upgrading from 2008r2 to 2016 and all my databases are in recovering mode. On the day when we are ready I will restore the database with the following command:
RESTORE DATABASE dbname WITH RECOVERY
Out of 50 databases we will be testing only 5 databases which will require log shipping again.
Sounds reasonable. Note that the 5 databases you are testing, you will have to re-do the log shipping if you want to keep doing it. You can't restore any more log backups when you have done RECOVERY.
Don't use the GUI, since it is far far more complex what the GUI does compared to using T-SQL commands directly.
Do you expect to restore more log backups?
If "yes", then leave the database in restoring or restore your log backups using STANDBY (which requires you to give SQL server a working file to use).
If "no", then you can say RESTORE DATABASE dbname WITH RECOVERY. Note that you will not be able to restore any more backups now (and this includes the log shipping).
Hi @Avyayah ,
When you prepare to restore a database, you need to restore a full database backup first, and then restore transaction log backups in order. And if you have a lot of transaction log need to restore, please refer to Restore a Transaction Log Backup (SQL Server), see below:
If you just need to restore last transaction log, you can use below T-SQL, and then database will get online after restore has been completed:
RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;
GO
If I misunderstood what you mean, please let me know.
Best regards,
Carrin
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.
@Carrin-msft , During logshipping, transactional logs are copied from primary server to secondary server and logs are deleted every 2 hrs. Executing this command
RESTORE DATABASE dbname WITH RECOVERY restored the database with output as. I have done some testing and any changes in the database was saved in the restored database.
Converting database ‘Database Name’from version 655 to the current version 852.
Database ‘Database Name’running the upgrade step from version 655 to version 668.
Database ‘Database Name’running the upgrade step from version 668 to version 669.
Database ‘Database Name’running the upgrade step from version 682 to version 683.
Database ‘Database Name’running the upgrade step from version 683 to version 684.
Database ‘Database Name’running the upgrade step from version 684 to version 685.
The example mentioned above restores probably the latest 3 logfile. Please advice, I think either process will have the same output.
Hi, are these two versions of sql server the same?
No copying data from sqlserver 2008r2 to 2016.
What is it you want to achieve?
Let users into the database? RESTORE DATABASE dbname WITH RECOVERY or just restore a log backup WITH RECOVERY with both achieve that. But you won't be able to restore any more log backups now.
Keep restoring log backups? Then don't do anything.
It is not possible to get into a database and then keep restoring log backups, since you go from 2008r2 to 2016.
Hi @Avyayah ,
SQL Server can configure the log shipping between lower version to higher version, but the database should in restoring mode only, STANDBY is not supported for cross versions of SQL Log shipping. Please refer to Will log shipping work on 2 different SQL Server versions? get more information.
Best regards,
Carrin
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.