I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance. Many backups (like full, differential, and log) will be taken while using the new compatibility level 90 or 100. This database is also configured with log shipping. My question is can I change compatibility level back to 80 safely without threats or must I take any considerations?

I am setting up online and archived redo shipping from a source database (PRIMARY) to destination database (PRIMARY). I cannot use a STANDBY database as destination as I have to setup real time downstream capture using Oracle XStream and standby databases are READ only. The issue I am facing is once I setup both DBs following instructions from here and here , I am able to receive the archived redo log files (I see the files showing up after a log switch). But, I am not receiving online redo log files, which is required for real time capture. Anyone setup this and maybe help with what might be missing? Please note that if I switch the destination DB from PRIMARY to STANDBY I receive both online and archived redo log files, but it doesn't serve my purpose. I am using the simple remote login password mode to communicate between the two DBs.

We have a number of log-shipped SQL 2017 databases which we wish to start backing up using Microsoft Azure Backup Server (locally and to the cloud) on a 15 minute schedule. Does anyone know if it is possible for Azure Backup Server to hook into the log backups made by SQL Server log shipping?

I am trying to create a log shipping setup. The log shipping process restores the full backup to secondary, but the restore step is failing for transactional data. When I check the job history I see the following error message:

2023-12-12 18:12:01.96	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 18:12:01.96	*** Error: Object must implement IConvertible.(mscorlib) ***
2023-12-12 18:12:01.98	Found first log backup file to restore. Secondary DB: 'BusDatabase', File: 'C:\copy\BusDatabase_20231212165000.trn'
2023-12-12 18:12:01.98	*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2023-12-12 18:12:01.98	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 18:12:01.98	*** Error: Object must implement IConvertible.(mscorlib) ***

-----------------------------------------------------------------------------------------

Message
2023-12-12 19:00:05.42	The restore operation was successful. Secondary Database: 'BusDatabase', Number of log backup files restored: 1
2023-12-12 19:00:05.42	*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2023-12-12 19:00:05.42	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 19:00:05.42	*** Error: Object must implement IConvertible.(mscorlib) ***
2023-12-12 19:00:05.42	Deleting old log backup files. Primary Database: 'BusDatabase'
2023-12-12 19:00:05.42	*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2023-12-12 19:00:05.42	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 19:00:05.42	*** Error: Object must implement IConvertible.(mscorlib) ***
2023-12-12 19:00:05.42	Deleting log backup file. File: 'C:\copy\BusDatabase_20231209184000.trn', Last Write Time (UTC): '12/9/2023 6:40:00 PM'
2023-12-12 19:00:05.42	*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2023-12-12 19:00:05.42	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 19:00:05.42	*** Error: Object must implement IConvertible.(mscorlib) ***
2023-12-12 19:00:05.43	Deleting log backup file. File: 'C:\copy\BusDatabase_20231209185000.trn', Last Write Time (UTC): '12/9/2023 6:50:00 PM'
2023-12-12 19:00:05.43	*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2023-12-12 19:00:05.43	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 19:00:05.43	*** Error: Object must implement IConvertible.(mscorlib) ***
2023-12-12 19:00:05.43	The restore operation was successful. Secondary ID: 'eb60caf8-e53f-49a4-823d-e98d18c3120a'
2023-12-12 19:00:05.43	*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2023-12-12 19:00:05.43	*** Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data) ***
2023-12-12 19:00:05.43	*** Error: Object must implement IConvertible.(mscorlib) ***
2023-12-12 19:00:05.43	----- END OF TRANSACTION LOG RESTORE     -----

If I restart me secondary server the latest data will get restored, but fails again if I add an new records. Below is my configuration for log shipping - backup job occurs every 10 mins - copy job occurs every 11 mins - restore job occurs every 12 mins Can anyone please tell me how to resolve this issue?

I am new as a DBA. I want to ask. I want to create new log shipping a database 17815 from server primary to server secondary. I create new log shipping with right-click on database 17815. when I click ok, but I get this error

System.Data.SqlClient.SqlError: The file 'M:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PREUATPSSHSOH123CAB_Data.mdf' cannot be overwritten.  It is being used by database '13722'.

So what can I do? I was trying to restore manually with the restore command, and restore was successful. However, when I try to create new log shipping again, the error is the same.

I've seen a few variations of this question, but no clear cut answers to my situation. I have an existing log shipping setup, and want to add a secondary server. Here is how I understand what will happen, please correct me if I am wrong. 1. Restore backup from 12 hours ago to secondary, using no recovery. 2. Add the secondary database to log shipping which creates the copy and restore job on the secondary and the copy job copies ALL the .trn files from shared backup location to secondary server. (no matter if they are needed or not) 3. The restore job is able to look at the DB on the secondary and see what the last LSN applied is, then find the appropriate log backup to apply, then apply all subsequent log backups to bring the DB into a consistent state with the primary. last questions is where I am not sure... Do I need to manually apply all the log backups since the full backup myself, or will log shipping work as I described above and do it automatically?

I have two severs primary and secondary log shipped server , after configuring it, I noticed that the latency of last copied file and last restored file increased and the alert of monitor sever fired a lot of times as I see it in SQL server error logs so I modified the alert job at primary server to fire every 15 minutes instead of 2 minutes but now I decided to reschedule the time of copy jobs to fire every 15 minutes instead of 10 minutes to decrease the the time of latency for both of the copy and restored jobs. My question: is there any threats that may affect the log shipping process or is it normal to change the job schedule even for the alert, copy, restore and backup jobs?

I have configured log shipping with 20 minutes for backup, copy and restore. If i need to restart my primary server. do I need to stop all jobs (backup, copy and restore)? Please advise. Regards Arvind Mishra

I'm kinda the 'accidental DBA' so i could be totally off here. We are onprem, and we are talking about our test environment. We are planning on changing hosting company. We will move our AG to the new hosting company We would like somehow to be able to switch back fast to the old hosting company, if something is not working at the new. We have a lot of temporal tables, so normal replication via transaction log is not possible. Although it is a test environment, there is a lot of testactivity, that we don't want to loose if new hosting company fails. (we could backup and restore, but i was looking for a faster solution) Could logshipping be a solution to this, or is there another way you would suggest ? regards

I would like to understand if it's normal that while the log file is being restored, I have users disconnected from ALL databases on the instance and not just the DB being restored. I was under the impression that it's just the DB being restored... I'm asking becuase I have a CHECKDB process running on 'another' database and it's stopping as soon as the restore kicks off on the OTHER database. > Msg 233, Level 20, State 0, Line 0 > > A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

I'm trying to come up with a SQL script that will verify log shipping on about 100 databases in an instance. I need to be alerted if log shipping is failing, but I do not want 100 plus emails coming in every 15 minutes, so I'd like to have one SQL Server Agent job that checks log shipping logs and if there are < 50 failures in a 30 minute period, then I'll get paged. The problem is that I don't know how to write a SQL script for that. I have tried turning off notifications on all jobs except a handful, but the issue there is that if one of those fails but the others don't, I might not get paged.

Configuration: - **LSPrimary01** - Log Shipping Primary - **LSSecondary01** - Log Shipping Secondary - **OddServer03** - This server is not part of the LS configuration, the odd man out - *Sean\LSServiceAccount* - The domain user service account for log shipping on all servers A colleague of mine and I were configuring Log Shipping in a test environment. We took a full COPY_ONLY backup of a database on **LSPrimary01**, a log backup, copied to **LSSecondary01** and restored the backup and log WITH NORECOVERY. Then used the GUI on **LSPrimary01** to configure Log Shipping. We already configured file sharing and proper permissions for *Sean\LSServiceAccount*. The following error was the result: - START OF TRANSACTION LOG BACKUP - Error: Could not retrieve backup settings for primary ID'cb1564b4-4ffd-a42d'.(Microsoft.SqlServer.Management.LogShipping) - Error: Failed to connect to server **OddServer03**.(Microsoft.SqlServer.ConnectionInfo) - Error: Login failed for user '*Sean\LSServiceAccount*'.(Net SqlClient Data Provider) - END OF TRANSACTION LOG BACKUP I ran the following query to verify my Log Shipping configuration: SELECT [primary_database] ,[backup_directory] ,[backup_share] ,[backup_retention_period] ,[monitor_server] ,[user_specified_monitor] ,[monitor_server_security_mode] ,[last_backup_file] ,[last_backup_date] FROM [msdb].[dbo].[log_shipping_primary_databases] Everything in the result set was correct, save *monitor_server*, it had the value of **OddServer03**! As many of you know, when configuring Log Shipping through the GUI, it is damn near impossible to "accidentally" configure this option. We never intended having a monitor server in the mix. We use *Quest Spotlight* to monitor the aforementioned servers which will yield alerts when jobs fail or when log shipping gets behind. One other perplexing matter, the job that is getting created is LSAlert_OddServer03 instead of LSAlert_LSPrimary01. Why is the service account trying to authenticate to a server that isn't part of the configuration?

I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are being used in last 24hours in order to predict how much space I need on external storage. Unfortunately pgmetrics doesn't have such information. Do you know how to do it? WAL Files: WAL Archiving? no WAL Files: 5 +--------------------+--------------+ | Setting | Value | +--------------------+--------------+ | wal_level | minimal | | archive_timeout | 0 | | wal_compression | off | | max_wal_size | 64 (1.0 GiB) | | min_wal_size | 5 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_segments | 0 | +--------------------+--------------+

I have SQL2014 version database on my Windows12 server. I want to add the databases in SQL2014 to SQL 2022 on my Windows2016 server using the Log Shipping method. Since the versions are different, I think I had to select No Recovery Mode instead of Standby Mode in the "Restore Transaction Log" section. I also selected No recovery mode, but I could not access the database added from SQL 2022 on my Windows 2016 server. How can I access SQL2014 databases from SQL2022 database with Log Shipping?

We have a SQL Server instance installed on an AWS EC2 instance with a Linux operating system (no idea why this would be done) with hundreds of databases that need moving to a better place. In the meantime, we're trying to set up log shipping so we can replicate some of the more important databases. We have a directory for the transaction log backups, /var/opt/mssql/tlogs, and I can create a SQL Server Agent job that will successfully backup the database to that location. However, when we set up log shipping, targeting this same directory, and run the job in SQL Agent, it always fails. The step that fails is Log shipping backup log job step and the error is: > Executed as user: \. The step failed. Looking into the full error log, the actual errors logged are: > "Error: Could not retrieve backup settings for primary ID ''.(Microsoft.SqlServer.Management.LogShipping)" "Error: Failed to connect to server ip-.(Microsoft.SqlServer.ConnectionInfo)" "Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(.Net SqlClient Data Provider)" I know this error well from Linked Servers, but I've never seen it come up with transaction log shipping before. With this being a Linux installation, it's incredibly difficult to find any useful guidance online for how to fix this problem. The job is owned by "sa", which is the same as the backup job that DOES work. The Linux user is "mssql" and this user has been granted full rights to the backup folder. I have no idea how Linux deals with service accounts, but it seems like it just uses "mssql" for everything. We've tried creating the job via the UI in SSMS, on the machine, remotely, with a SQL script from the Microsoft documentation, all fail the same way. This is the basic script we used (the second part is irrelevant, as it just creates a schedule for 15 minutes, but we can run the job manually to watch it fail): DECLARE @LS_BackupJobId AS UNIQUEIDENTIFIER; DECLARE @LS_PrimaryId AS UNIQUEIDENTIFIER; DECLARE @SP_Add_RetCode AS INT; EXECUTE @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database @database = N'rr_test_replication', @backup_directory = N'/var/opt/mssql/tlogs', @backup_share = N'/var/opt/mssql/tlogs', @backup_job_name = N'LSBackup_test_replication', @backup_retention_period = 4320, @backup_compression = 2, @backup_threshold = 60, @threshold_alert_enabled = 1, @history_retention_period = 5760, @backup_job_id = @LS_BackupJobId OUTPUT, @primary_id = @LS_PrimaryId OUTPUT, @overwrite = 1; Edit: It should be noted that this failure happens while backing up the transaction logs, NOT when attempting to copy them or restore them onto a second server. We're testing this by attempting to ship logs to a second database on the same instance, but it's failing to run the job to backup the transaction logs due to the authentication error.

There are many community tools for setting up log shipping, e.g. dbatools' [Invoke-DbaDbLogShipping](https://docs.dbatools.io/Invoke-DbaDbLogShipping.html) . However, they assume that both the source and target server have access to a shared network share. In my situation, the only shared storage between my target and source servers (both Amazon EC2s) is an Amazon S3 bucket. Are there any community tools setting up log shipping allows for Amazon S3 to be the shared storage? I could roll my own without much trouble, but I struggle to believe that this isn't a solved problem. Either I'm missing something big or this should be a frequently faced problem among SQL Server users on Amazon EC2.

I am trying to upgrade from SQL 2016 to SQL 2019 (as SQL 2016 will be out of service in July 2016) in a different VM (Virtual Machine - Windows Server 2022), so I want to copy one database to other machine. I have a database (in SQL 2016) that is in “Standby / Read-Only” mode because there is a nightly job that runs to feed 24 hourly log files from a third party's SFPT server. Bottom is screenshot of how it is set up at the beginning when these three databases were created: Recovery state: "RESTORE WITH STANDBY" : I am trying to find a solution to transfer data (mdf) and log (ldf) file from original VM (that has SQL 2016) to a new VM (that has SQL 2019) and create a new database as “Standby / Read-Only” mode. I stopped SQL in SQL 2016, and I was able to move both data and log files. Now, when I tried to attach both data (mdf) and log (log) file in SQL 2019, I got an error saying: "Cannot attach a database that was being restored. (Microsoft SQL Server, Error: 1824) So, I am curious whether I have to change the mode of original database from “Standby / Read-Only” to Regular mode before moving mdf and ldf files. Or is it even possible? Sorry. I called bottom mode as "Regular" mode (I am not sure it is correct though). If that is the only solution (change the mode from “Standby / Read-Only” mode to regular), can I create a database as “Standby / Read-Only” mode in SQL 2019 (on other VM) without any damage to the data or interruption of log file feed?

The scenario: I am retiring a production SQL Server (SQL Server 2008) and relocating to another SQL Server (ver 2019) on a different computer. I want to do it in phases: Steps/Phases: 1. Backup a database on current server and restore onto new server, leaving current database on-line 2. Add and delete logins, and users. This may take several days while developers test their applications' connections to the new server. 3. Update the database on the new server with data only, not affecting new logins/users 4. Shut down old database Step 3 is where I need help, not sure how to refresh the data only, without affecting logins and users. It looks like Log Shipping might be a valid technique but there seems to be an issue: the Primary DB has Recovery Model set to Simple. Questions: What will happen if Recovery Model is changed? 1. Does the server have to be restarted? 2. Will performance be affected? 3. Will it use considerably more disk space? 4. Can Log Shipping be used from SQL Server 2008 to SQL Server 2019 Background: I tried the Database Backup Wizard but it doesn't really satisfy my requirements. TIA

I want to configure log shipping for databases. In sys.databases, I can see that some of my databases are encrypted true and some have is_master_key_encrypted_by_server true. How do I handle the above 2 cases with log shipping.

I've set up log shipping using standby mode （The "Disconnect users in the database when restoring backups" is unchecked). I tested running the following code on secondary server/database. declare @a int while 1=1 begin select @a = count(*) from .... end However, the code stops with the following error when log shipping restore job start to run. > Database cannot be opened. It is in the middle of a restore. How to avoid the issue?