question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked TiborKaraszi commented

Restoring logshipping databases in sqlserver

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

76780-restoringdatabases.jpg


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered TiborKaraszi commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TiborKaraszi , 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.

0 Votes 0 ·

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.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @SahaSaha-5270,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered TiborKaraszi commented

Hi @SahaSaha-5270,

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:
77005-backup.png

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.



backup.png (60.6 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@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.

0 Votes 0 ·

Hi, are these two versions of sql server the same?

0 Votes 0 ·

No copying data from sqlserver 2008r2 to 2016.

0 Votes 0 ·
Show more comments
TiborKaraszi avatar image
1 Vote"
TiborKaraszi answered

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).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.