Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points of sqlserver about log transmission log shipping". The explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the knowledge points of sqlserver about log transmission log shipping"?

1. The construction of logshipping is carried out on the master library. You must first fully prepare the master library, and there is no need to restore the slave library first. Right-click the master library-properties--Transaction logshipping, and you can refer directly to the graphical interface step by step. If the backup path of the master library is specified in the graphical interface, the slave library can be restored automatically. At this time, the recovery is in the form of norepalce by default.

2. If the slave database is restored first, if there is a log backup after the full backup of the master database, the log from restore to the last backup of the master database is required, and it is in norecovery mode, and then logshipping is built on the master database.

3. Log files in trn format exported by the master library, which are also log files in trn format when copied to the slave library.

4. The main library generates two job, one alert, and one backup,alert calls the stored procedure sys.sp_check_log_shipping_monitor_alert,backup to call the sqllogship.exe command. Each time a logshipping is built, the main library generates a backup job,alert job that will not be added.

5. The standby library generates three job, one alert, one copy, and one restore,alert calls stored procedures sys.sp_check_log_shipping_monitor_alert,copy and restore all call sqllogship.exe commands. Each time a logshipping is built, a copy job and restore job,alert job are generated from the library and will not be added.

6. The network backup directory of the master library, the local backup directory of the master library and the copy directory of the slave library can all be set to the same directory on the network.

7. If you use the mode of 6 above, you can turn off the copy job of the slave library without affecting log shipping

8. When the local backup directory of the master library and the copy directory of the slave library are in the same directory, the delete of both is valid. Whoever triggers the deletion condition first starts delete.

9. During the normal synchronization of logshipping, if the master database manually makes a backup of the backup log, then the log is truncated, and the restore log job of the slave library logshipping will start to report an error, and the log that needs to be recovered will not be found. At this time, the backup log job of the master database and the copy job of the slave database are still running normally (for example, bakcup log job is executed every hour, execution starts at 8:00, and suddenly backup log is executed manually at 8:30 When the backup log job is executed at 9:00, the log is only 8:30-9:00, while the slave library needs a log of 8:00-9:00, so the loggshipping reports an error from the restore log job of the library)

10. In the scenario of 9 above, if the backup package executed manually at 8:30 is still there, can it be restored?

The following two experiments are real.

Personal experiment 1: no, use the 8:30 log backup for restore directly from the library, report an error The log in this backup set begins at LSN XXX, which is too recent to apply to the database, and find that the log backed up manually at 8:30 cannot connect to the logshipping log LSN

Personal experiment 2: OK, use the 8:30 log backup directly from the library for restore, you can restore successfully, and the restore log job of the slave library will run normally.

11. Logshipping's backup log job uses sqllogship.exe, and our ordinary t/sql is the backup log command. Even if the suffixes generated by the two backups are different, the log backups generated by logshipping's backup log job can be manually restored using restore log.

12. After building the logshipping, the main library view msdb.dbo.log_shipping_primary_databases records the information of the logshipping, but the main library view msdb.dbo.log_shipping_secondary_databases does not. From the library, on the contrary, the information of logshipping is recorded from the library view msdb.dbo.log_shipping_secondary_databases, but not from the library view msdb.dbo.log_shipping_primary_databases

13. The full backup on the primary server does not affect logshipping, because the full backup does not truncate the log

14. Select NO recovery mode from the library Restore Transaction Log, and the small green arrow restoring is displayed from the icon status of the library, which cannot be read from the library at this time. Select Standby mode to display gray (Standby/Read-Only) from the icon status of the library, and the slave library can be read at this time

15. To check whether logshipping is normal, you can right-click the instance-> Reports-- > Standard Reports-- > Transaction Log Shipping Status. The master database only shows the status of the master database, and the slave database only shows the status of the slave database, so you must log in to the master database and the slave database to check.

16. To delete logshipping, as long as you log in to the master database instance, right-click the database-> Properties-- > Transaction Log Shipping-- > uncheck Enable this as a primary database in a logshipping configuration. After deletion, the job of both the master and slave libraries will be deleted automatically, even if job is disabled by disable.

17. After deleting the logshipping, restore the read-write status from the library.

If the original status icon from the library is to display the small green arrow restoring, execute the following

RESTORE DATABASE [testdb] with recovery

If the original status icon from the library is grayed out (Standby/Read-Only), do the following

ALTER DATABASE [testdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [testdb] WITH RECOVERY

ALTER DATABASE [testdb] SET MULTI_USER

18. Logshipping, after the failure of the master library, you need to switch the slave library to read and write. The operation is similar to the above 17, but the original copy job and restore log job should be manually deleted from the slave library, and some business job of the original master library should be exported for execution on the slave library, because logshipping will not synchronize job.

19. Logshipping can be deleted directly from the library, regardless of its Standby/Read-Only status or restoring status. Unlike mirror, mirror cannot be deleted by delete from the library restoring status.

The logshipping will be built at 8:30. In this process, if the master database performs a log backup at 8:30 and truncates the logs, can the slave database build a full backup at 8:00 using only the master database? No error will be reported from the beginning to the end of the build, which is different from mirror, but cannot be synchronized normally after a successful build. Assuming that the master library is successfully built at 8:35, the master library starts to execute backup log job, and the master library backup log job and slave libraries copy job and restore log job all execute normally, but the report Transaction Log Shipping Status of the slave library will appear alert alarm. The restore log job of the slave library will not report errors, but there is the following information. The starting log after the full backup time point cannot be found. All subsequent 8:35 log backups generated by the main library bakcup log job will be skipped.

Searching through log backup files for first log backup to restore. Secondary DB: 'testdb'

Skipped log backup file. Secondary DB: 'testdb', File:'\\ logship\ testdb_20190111083500.trn'

21. After the logshipping is successfully built, the main database view msdb.dbo.log_shipping_primary_databases can see how many databases have built logshipping. In the future, these databases can be excluded from the log backup script, that is, these databases will not back up the logs so as not to truncate the logs of these databases.

22. If during the backup of the main database, such as backing up to the network path, and the network is suddenly interrupted in the middle of the backup, bakcup log job will not stop immediately but will only report an error. When the network is ready, backup log job will retry a certain process.

2019-01-13 11 11 Backup file'\\ db\ LOG\ Epist20190113161502.trn'does not exist

2019-01-13 11 1V 04V 00.36 Retry backup database 'E'to file'\\ db\ LOG\ Epist20190113161502.trn'

23. If the restore log job of the slave library fails and the restore log job executes normally again, it will not be said that the former restore is halfway through, and the latter cannot be connected. This situation is the same as the above 22, the file on the network, restore halfway, then the network is down, and then rerun restore log job is normal.

Error: The log backup file'\\ db\ LOG\ Epist20190110020001.trn'was verified but could not be applied to secondary database

24. Logshipping, you can't see which main library is on the slave library. If you want to see it, right-click view history in the restore log job selection from the library, and the job log will record Primary Server and Primary Database

25. If there is a problem with the logshipping synchronization of the slave library, but the logshipping logs are not lost, you can directly use the backup of the master library to perform restore databae recovery from the slave library. Before executing this restore databae, you do not need to cancel the logshipping configuration of the master library, but it is best to stop and disable the restore log job of the slave library, and then enable this job after recovery.

26. The master database has backups of full and diff, as well as a loggshipping log before diff and all logshipping logs after that. Verified by experiments and production environment, after restoring full and diff backups from the library, the restore log job of the slave library will skip the log before diff, and the latter will restore, indicating that the restore log job of the slave library will use the latest LSN of the slave library to find the required log for recovery.

27. If the backup log job of the master library backs up the log to the network path, if a network failure occurs during the generation process, an error will be reported, the file will not be generated, and the log will not be truncated. The next time the backup log job of the master library runs, it will reconnect the last LSN point to continue to generate files. For example, if the job runs once an hour and an error is reported in the generation process at 8:00, the job at 8 o'clock will be rolled back, the file at 8 o'clock will not be generated, and the LSN is the LSN truncated by the normal backup log job at 7:00 last time. When it is generated at 9:00, it is the data from 7:00 to 9:00, so the 9:00 file will be twice as large as usual. The following dblog_1.trn file was not generated successfully and does not exist on the storage.

-START OF TRANSACTION LOG BACKUP-

2019-01-1508 Backing up transaction log. Primary Database: 'db', Log Backup File:'\\ logship\ LOG\ dblog_1.trn'

2019-01-15 08 First attempt to backup database 'db' to file'\\ logship\ LOG\ dblog_1.trn' failed because Write on "\ logship\ LOG\ dblog_1.trn" failed: 59 (An unexpected network error occurred.)

BACKUP LOG is terminating abnormally.

2019-01-15 08 Backup file'\\ logship\ LOG\ dblog_1.trn' does not exist

2019-01-15 08 Retry backup database 'db' to file'\\ logship\ LOG\ dblog_1.trn'

2019-01-15 09 Error: Write on "\\ logship\ LOG\ dblog_1.trn" failed: 59 (An unexpected network error occurred.)

BACKUP LOG is terminating abnormally.

-END OF TRANSACTION LOG BACKUP-

Exit Status: 1 (Error)

28. For logshipping reconstruction, you don't need to close logshipping, you just need to restore the database from the slave database. Restore database+restore diff is fine. The restore log job of logshipping will automatically find the log generated by the first logshipping behind the diff backup for restore. If the restore log job of the logshipping of the slave database is running too slowly, you can manually restore the backup produced by the master library of the slave library. At this time, disable restore log job from the slave library, but do not disable the backup log job of the master library. This allows you to restore database+restore diff+restore logshipping log on the slave library.

29. Loggshipping from the restore log job of the library always starts with the last successful log, the next log,restore log job log will record the last log of the last successful restore, and then find the next one. For example, the last successful restore log job,restore has logs No. 4, 5, and 6, the next restore log job will record that the Last Restored File is 6, and the next restore will be 7.

30. Both the backup log jog of the master database and the restore log job of the slave database will be rolled back. For example, the master database is backup 8:00-9:00 logs. If you stop it, the logs will not be truncated. The next time the master database reruns backup log job at 10:00, the master database backup logs from 8:00 to 10:00. The same is true for the restore log job of the slave library, such as restore the 8:00-9:00 log from the slave library, stop it, and rerun restore log job from the library at 10:00 next time, and restore the 8:00-10:00 log from the slave library.

31. During loggshipping, backup log is manually executed on the main database. After that, the logshipping master library continues to generate logs in logshipping format, and then back up the master library again. After that, the logshipping master library continues to generate logs in logshipping format. The slave library uses the database backup of the master library for restore database, plus the logshipping format logs generated by the master database backup. Loggshipping can go on normally.

For example, from 7:00 to 8:00, the main library normally generates logs in logshipping format, and at 8:15 the main library suddenly manually backup log (the log from 8:00 to 8:15 is truncated, and the backup log format is in manual backup log format), at 8:30, the main library normally generates logs in logshipping format (the log from 8:15 to 8:30 is truncated, and the backup log format is in logshipping format), and at 8:40 the main library rebacks the database. The 9:00 slave library is restored using the backup package of the master library. At this time, the logshipping slave library needs logs in logshipping format after 8:40, and these logs are available and can be logshipping normally.

32. In logshipping mode, no matter the master library or slave library, the above job is in a transaction state. If it is stopped manually, the transaction will be rolled back if it is not successful. Don't worry.

-START OF TRANSACTION LOG BACKUP-

-END OF TRANSACTION LOG BACKUP-

-START OF TRANSACTION LOG RESTORE-

-END OF TRANSACTION LOG RESTORE-

33. Pause the backup job of the master library, manually copy or create the log file to the slave library to see if the restore job of the slave library is abnormal

1. If you copy the first file after the last log file recorded from the library restore, such as logship_20190118061000.trn, no error will be reported from the library's restore log normal restore logship_20190118061000.trn,log log, and the restore jog will end normally.

2. If you copy a previous log file recorded from the library restore, such as copy logship_20190118060800.trn, you cannot restore from the restore log of the library

An error will be reported in the logship_20190118060800.trn,log log, but restore jog ends normally

Error: Skipping log backup file because the log terminates at an LSN value that is too early to apply to the database. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118060800.trn'

3. If you manually create an empty file named after the last log file recorded from the library restore, such as logship_20190118061200.trn, the file is ignored directly from the restore job of the library, no error is reported in the log log, and the restore job ends normally.

Could not find a log backup file that could be applied to secondary database 'logship'.

4. If you copy a log file generated by another main library and rename it to the file after the last log file recorded by the restore of the slave library, such as cp DBA_20190115074503.trn logship_20190118061300.trn, an error will be reported from the restore job cannot restore logship_20190118061300.trn,log log of the library, and the restore job will end abnormally.

Error: Could not apply log backup file'\ testdb1\ logship\ log\ logship_20190118062300.trn' to secondary database 'logship'

Error: The backup set holds a backup of a database other than the existing 'logship' database.

34. The restore job of the slave library is suspended, and the backup job of the master library is normal. After testing, it is found that the restore log job of the slave library is restored according to the generated name of the log. After finding the correct name, if you find that all the trn logs generated after this name have been restored, an error is reported. See the test in 2 below.

1. Delete a log file without restore from the library, then enable the restore job of the slave library, report an error from the restore log job log of the library, and exit abnormally

Error: The file'\ testdb1\ logship\ log\ logship_20190118070100.trn' is too recent to apply to the secondary database 'logship'

Searching for an older log backup file. Secondary Database: 'logship'

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065900.trn'

Skiped...

2. Rename the file above 1 from the library, change it to the previous time, and pass the last restore log time, then enable the restore job of the slave library, report an error from the library restore log job log, but exit normally

Cp logship_20190118070100.trn logship_201901180655000.trn

Error: The file'\ testdb1\ logship\ log\ logship_20190118070100.trn' is too recent to apply to the secondary database 'logship'.

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065900.trn'

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065800.trn'

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065700.trn'

Found a log backup file to apply. Secondary Database: 'logship', File:'\\ testdb1\ logship\ log\ logship_201901180655000.trn'

Error: Skipping log backup file because the log terminates at an LSN value that is too early to apply to the database. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065601.trn'

Error: The log in this backup set terminates at LSN 34000003927200001, which is too early to apply to the database. A more recent log backup that includes LSN 34000003932900001 can be restored

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065700.trn'

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065800.trn'

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065900.trn'

...

3. Rename the file from the library and change it later. The following log has not been restore from the library, then enable the restore job from the library, report an error from the restore log job log of the library, and exit abnormally

Error: The file'\ testdb1\ logship\ log\ logship_20190118070100.trn' is too recent to apply to the secondary database 'logship'

Searching for an older log backup file. Secondary Database: 'logship'

Skipped log backup file. Secondary DB: 'logship', File:'\\ testdb1\ logship\ log\ logship_20190118065900.trn'

Skiped...

35. After building the logshipping using the fullbackup of the master library, the slave library has already restore a lot of log. If you restore the diff backup based on this fullbackup from the slave library, you can restore it.

After setting up the logshipping using the fullbackup of the master library, even if the backup log job of the master library has been executed for a long time, the restore log job of the slave library has been executed a lot, and the restore log job of the slave library still has no restore left. Then the master database executes backup diff, and the slave library can use this backup diff to recover. After recovery, the restore log job of the slave library will skip the original log without restore, and only the restore master database will execute the log generated by the master library backup log job after backup diff, but only if This diif is based on the fullbackup used when building the logshipping, otherwise the error This differential backup cannot be restored because the database has not been restored to the correct earlier state.

36. If the log backup of the logshipping is placed on the shared storage, and the slave database is not copied to the local path but reads the file directly on the shared storage, once the backup log job of the master database cannot be written due to network outage, the backup log job of the master library will be stuck for a long time or about 10 hours. If the backup log job of the master library is stopped manually. Or the backup log job of the main database takes a long time to execute because the log is very large. At this time, because of special things such as alter database add datafile operation, the backup needs to be closed, and the backup log job of the main database is stopped manually. You will find that it is different from the scenario of 27 above, when the file exists on the shared storage, and the failed job does not indicate that the file does not exist, and there is no keyword such as END OF TRANSACTION LOG BACKUP, so you don't know whether the transaction is really over. This file is temporarily called A, and the files regenerated by the main library do not know whether it contains or does not contain all the information about this file A. And the A file will be read from the restore log job of the library. When reading the A file, you can see the corrupted A file from the library Management-- > SQL Server Logs. Personally, I think that in this case, the A file does not truncate the log, and the transaction rolls back normally, because the real scenario verifies that the next file using the A file can execute restore log manually. See "problems encountered in the formal environment" 4 below

37. Modifying the schedule time of job in the logshipping configuration of the main database is the same as modifying the schedule time directly in job.

38. After the master database is upgraded, the level can be modified. After the slave library is upgraded, the level cannot be modified, but after the log of the master database is synchronized to the slave database, the level of the database corresponding to the slave database will be the same as the master database.

39. When datafile is added to the master library, datafile will also be added to the slave library, and the path is the same as that of the master library.

40. During the execution of the restore log job from the library, a log of restore encounters too recent, indicating that the log is too new. The minimum lsn of this log is greater than the current lsn,restore of the slave library and the log encounters too early, indicating that the log is too old. The maximum lsn of this log is less than the current lsn of the slave library.

41. Compared with the last successful log recovery from the library loggshipping, the database after re-restore database from the library is too new, so some existing logshipping logs are too old. From the library restore log job, the skip logs are searched to the new log backup package, until the logshipping logs connected to the latest lsn of the library are found, and the normal restore log starts from the library.

For example, logshipping has been reporting errors from the restore log job of the library, the last log successfully recovered is DB_7.trn, and the next log should be DB_8.trn, but this DB_8.trn log has not been successfully recovered, and retore log job has repeatedly identified it for several days in a row. In this way, we can re-restore the latest database backup of the master database in the slave library, so that the current lsn of the restored slave database is greater than the maximum lsn of DB_8.trn, so that the restore log job of the slave library will skip the DB_8.trn log backup package.

42. Compared with the last successful log recovery from the library loggshipping, the database after re-restore database from the library is too old, so some existing logshipping logs are too new. From the library restore log job, the skip logs are searched to the old log backup package, until the logshipping logs connected to the latest lsn of the library are found, and the normal restore log starts from the library.

43. Loggshipping reports an error skip log from the beginning to the end of the restore log job of the library, and there is no log of the logshipping corresponding to the above 41 and 42 scenarios from the last successful restore of the library

Phenomenon: restore log job starts execution at 1:00 and ends at 9:00, indicating that the execution is successful. Log recording is a log from the first log in skip until 1:00 at the end of skip. When restore log job runs at 10:00 the second time, log recording is still a log from the first log in skip to 10:00 at the last skip.

Reason: I think the reason is that there is a problem with the restore database from the library, there is a problem with the recovered slave database, from the last LSN of the library is uncertain, the restore log job of the library will log skip one by one until the last generated logshipping log, and the next restore log job will run like this, from skip 1 to the newly generated log, over and over again. If the corresponding first log does not exist after restore database, the second log does not report to skip, but reports that the database is too old and the log is too new.

Solution: do not close the logshipping configuration, re-backup the master library, and restore the latest backup to the slave library

Solution practice: for example, the master database is backed up at 8:00, and the backup package is completed with the backup package A, and the backup package An is recovered from the library at 14:00. At this time, you can enter the log replication path of the slave database (the loggshipping log generated by the master database is in the path of the master database, and copy from the library to the slave database), delete the log backup generated by logshipping before 7:00, and find the first log at 7:00 from the restore log job runtime of the library. Skip this log until you find the 8:00 loggshipping log and start restore. In this process, if you still encounter the problem of skip all the time, you can also use the log of 7:00 logshipping to manually restore log. There should be an error, indicating that the log is too old. Continue to use the log of 8:00, 9:00 logshipping for manual restore log until success, so that when executed from the library restore log job, skip will skip the 7:00, 8:00, 9: 00 log, successfully prompt found first log backup file to restore, and then successfully enter the restore.

44. If the log backup of the logshipping is placed on the shared storage and the slave database is not copied to the local path but directly reads the file on the shared storage, once you encounter the backup log job of the main library which cannot be written due to network interruption, the backup log job of the main library will be stuck for a long time or so for about 10 hours. If you manually stop the backup log job of the main library, it will be different from the scenario of 27 above. At this time, the file exists on the shared storage, and the failed job does not indicate that the file does not exist, and there is no END OF TRANSACTION LOG BACKUP keyword, so I do not know whether the transaction is really over. This file is temporarily called A, and the files regenerated by the main library do not know whether it contains or does not contain all the information about this file A. Moreover, the A file will be read from the restore log job of the library. When reading the A file, you can see the corrupt information of the A file from the library Management-- > SQL Server Logs.

Personally, I think: if you encounter this problem, it is best not to close the backup log job of the main library manually, but if you do not close it, the job may be stuck for too long, such as 10 hours in this case.

Solution:

1. Manually restore a file after the bad file. If the restore is successful, rename the bad file to skip the file from the restore log job of the library, and the real environment has verified that it is feasible.

2. If the above 1 fails, only the full+diff backup of the master database can be restored to the slave database. The diff backup must be the diff backup after the LSN at the end of the corrupted A file, which has been verified once in the real environment.

45. Dblog_1.trn and dblog_2.trn actually exist. The backup of the main library indicates that the backup of the former failed (when the next job of the main library is scheduled, there is no information about this file, directly the file dblog_2.trn). When checking from the library restore, the former is automatically skipped if the format is not correct.

46. Both the master and slave libraries of logshipping have been upgraded, and the restore log job of the slave library has the following error. You can only restore the slave library again by full backup, but you do not need to dismantle the logshipping or reconfigure it.

2019-01-26 20 logship 35 logship 23.00 * * Error: Could not apply log backup file'\ logship\ LOG\ Backups_20190125121503.trn' to secondary database 'Backups'. (Microsoft.SqlServer.Management.LogShipping) *

2019-01-26 20 expected 35 error 23.00 * Error: SQL Server detected a logical consistency-based I error O error: incorrect pageid (expected 1 Error 1536251; actual 1 Error 864359). It occurred during a read of page (1at offset 0x000002ee1f6000 in file 1536251) in database ID 14 at offset 0x000002ee1f6000 in file'G:\ DEFAULT.DATA\ Backups.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

RESTORE LOG is terminating abnormally.

Processed 0 pages for database 'Backups', file' Backups' on file 1.

Processed 3273 pages for database 'Backups', file' Backups_log' on file 1. (.net SqlClient Data Provider) * *

47. If the file storage path of the backup log job of the master library is the same as that of the slave library copy job, then both the backup log job of the master library and the file delete date restricted by the slave library copy job are valid, and delete will start if the deletion condition is triggered first.

48. If you find an error in the format of the log from the library application log, it will also be skipped.

2019-02-21 06 Error: Could not apply log backup file'\\ logship\ LOG\ dblog_1.trn' to secondary database 'npdb2_1'. (Microsoft.SqlServer.Management.LogShipping) *

2019-02-21 06 on device 54 LOG 01.26 * Error: The file ID 1 on device'\\ logship\ LOG\ dblog_1.trn' is incorrectly formed and can not be read.

RESTORE LOG is terminating abnormally. (.net SqlClient Data Provider) *

2019-02-22 02 LOG 26 LOG 21.60 Skipping log backup file'\\ logship\ dblog_1.trn' for secondary database 'db' because the file could not be verified.

49. The logshipping configuration interface reconfigures the scheduler of backup log job and restore log job, but only finds that the scheduler of the backup log job on the master library has been changed normally, and the scheduler of the restore log job of the slave library has not changed, unless you manually modify the scheduler of restore log job from the library.

50. Add data files or log files to the logshipping master library, but if there is no corresponding directory in the slave library, the restore log job of the slave library will report an error. Even if the slave library has default datafile and logfile paths, the restore log job of the slave library has the following error message

File 'logship2' cannot be restored to'L:\ 20190401\ logship2.ndf'. Use WITH MOVE to identify a valid location for the file.

Directory lookup for the file "L:\ 20190401\ logshiplog2.ldf" failed with the operating system error 2 (The system cannot find the file specified.).

File 'logshiplog2' cannot be restored to'L:\ 20190401\ logshiplog2.ldf'. Use WITH MOVE to identify a valid location for the file.

51. The database of FILETABLE builds logshipping. When the library is in norecovery mode, the file system directory cannot be seen from the library. When the library is in standby read only mode, you can view from the library to the file system directory through the following three statements. However, in both modes, the\\ SERVERNAME\ FILESTREAM_SHARE_NAME\ FILESTREAM_DIRECTORY_NAME\ FILETABLE_DIRECTORY directory cannot be accessed directly from the library.

When the library is in standby read only mode, when you right-click the FILETABLE table from the library, you can see that the Explore FileTable Directory is gray and cannot be viewed.

Query the DIRECTORY_NAME of the database FILESTREAM function

Select db_name (database_id), * from sys.database_filestream_options

Query the DIRECTORY_NAME of FILETABLE table

Select object_name (object_id), * from sys.filetables

Query the full path name of a file in filetable table testdb.dbo.table1

SELECT FileTableRootPath () + [file_stream] .GetFileNamespacePath (), name FROM testdb.dbo.table1

52. When logshipping is built into standby mode, the slave database is also normal restore lob job to synchronize the data with the master database. This does not mean that the slave database is read-only at this time, and the log cannot be applied. At the beginning, the slave library is also initialized in norecover mode, and the restoreing is displayed from the library status. Once the first log is applied to the slave library, the Standby/Read-Only is displayed from the library status from now on. After the slave library is displayed as Standby/Read-Only, you can continue to apply the log of the master library.

53. With regard to the choice of mirror and logshipping, the log generated by the database in a short time is very large, for example, 500MB is generated in 15 minutes.

Then mirror is not as good as logshipping, because mirror consumes more memory, and mirror is prone to the state of suspend.

54. Of course, the restore log job of logshipping is much slower than that of normal manual restore log. I have personally tested the log of 500MB.

Manual backup command backup format (backup time is about 20 seconds) and then manual restore command recovery only takes 40 seconds

But the format in which logshipping's backup job is backed up (about 20 seconds long) takes 3 minutes to use logshipping restore job.

Thank you for your reading, the above is the content of "what are the knowledge points of sqlserver about log transmission log shipping". After the study of this article, I believe you have a deeper understanding of what sqlserver knowledge points about log transmission log shipping have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.