The Stack Overflow podcast is back! Listen to an interview with our new CEO.
24

RESTORE DATABASE dbname FROM DISK = 'dbname .bak' WITH REPLACE, RECOVERY --force restore or just RESTORE DATABASE dbname WITH RECOVERY the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it RESTORE WITH RECOVERY is the default behavior which leaves ...


20

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind. Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have ...


16

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time. Edits: I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them. The process was very ...


13

Two options: On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and ...


10

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012) This is a ONE WAY situation. When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012. The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after ...


10

It just seems so fragile. Logshipping is tested and proved since sql server 2000 (and even older) days. Its not fragile. Look at the errors ... Last Restored File: \server\folder\db_20160105060002.trn, Logshipping is trying to restore Destination: '\server\folder\db_20160105080001.trn' This means you have a gap in the log sequence. There might ...


9

If I understand you correctly, your major problem is the log files during the several hours of backup. From your opening statement I understand that the 1.5 TB database normally runs in SIMPLE recovery, and thus no log backups to do. Disclaimer: I have never done log shipping on this scale. Of course, you should ask whether you can get more space allocated ...


9

You are facing what is called orphaned entries in logshipping tables in msdb. You can use below tsql to just manually clean up logshipping orphan entries. For your case, since you don't have the primary database available, you should just run the secondary part or you can try running sp_delete_log_shipping_secondary_database N'secondary_database'. It will ...


9

The recovery model for the secondary will be the same as the primary database, because you're backing up the primary, restoring it on the secondary node using NORECOVERY/STANDBY, and then restoring subsequent log files to it. In a log shipping configuration, you cannot make changes to the secondary database. If you could, then the databases would be out of ...


8

There are a few possible things going on here, here's a non-exhaustive list: the execution plan cache is cleared by the log restore, so plans will need to be recompiled the first time. If your plans have long compile times, this could explain the difference. You didn't mention exactly how long the delay is on the first run compared to the subsequent runs ...


7

Log Shipping essentially involves three jobs. The first job backs up the log on the primary server and stores those backups in a local folder. The second job copies those files across the network to the secondary server. The third jobs restores those backups using the WITH STANDBY option. Log Shipping is set up on an entire database. The database on the ...


7

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be: ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE([logfilename], 1) ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT Changing the database to simple recovery may cause ...


7

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY. This will allow you to query the secondary db for any reporting, etc. When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it ...


7

Yes, but only manually - Azure Blob Storage isn't integrated into the log shipping wizards. You would need to do your own backups to Azure Blob Storage, and then have the secondary server(s) restore from ABS using the same certificates. When other shops have done similar projects with AWS's S3 storage (writing their SQL Server backups there using 3rd party ...


7

There is no point in HA if anyhow DR technologies are allowed in simple recovery. Transaction log is a must for point-in-time recovery. The transaction log is what a DR technology uses to replay transactions that are done on Logshipped/mirrored databases, and since a log backup is not possible in simple recovery, you cannot configure HA/DR in simple recovery ...


7

It's because these methods rely on the transaction log, which itself is only reliable in the full recovery model. Please read the following page in its entirety, as there are several hints there about the differences between simple and full recovery, and why full is so important for DR: Why Does the Transaction Log Keep Growing or Run Out of Space? If you ...


7

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE. Rather set up log shipping properly in the presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the ...


7

SQL Server always seems to set snapshot isolation (SI) on when a database is made read only. I have no idea why it does this, but it does. You can try to turn it off, and the ALTER DATABASE statement will succeed, but snapshot isolation remains enabled while the database is read only. One theory is that setting SI prevents unexpected errors for people ...


6

You could be utilizing the transaction log backups that are taken by the log shipping process. In fact, you can't double-up on transaction log backups. So, as long as you have the actual transaction log backups and the ability to copy them to other storage (in order for your custom backup preservation and retention for Disaster Recovery), you should be ...


6

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though... Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary ...


6

You just need to run two stored procedures to remove the setup of log shipping from the primary database How to: Remove Log Shipping (Transact-SQL). As the secondary doesn't exist you shouldn't need to perform step 2 & 4.


6

In my opinion, log shipping has those advantages: I understand the direction is only one-way (master to slave). Replication is a complex solution better suited to two-way data replication or replication to multiple sources. Log shipping doesn't need a special design of the database (e.g. primary keys as GUID's and not as autoincrement integers). Log ...


6

WITH STANDBY is only supported when both SQL Servers are the same version. You can only use WITH NORECOVERY An alternative if you need read access to the destination databases, is to use replication.


6

You could do log shipping with the standby option rather than norecovery. The database would be read-only, and the users would get kicked off during each restore, but it might suit your needs better.


6

If you are running sp_change_users_login 'Report' to get that list of orphaned users it will return the SID expected. Just take that SID and create your login with the same SID: CREATE LOGIN [mylogin] WITH PASSWORD='', SID = <SID for orphaned user> This should allow your login to be mapped to that user in the database and allow you to access the ...


6

From this page in the MS documentation: There are two options for configuration when you place the secondary database in standby mode: You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore ...


6

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL): STANDBY is also not allowed when a ...


6

Yes you can, I remember a shop where we had this kind of setup in that case even the copy job was disabled because there were no need to copy the files. You just need to be sure your file retention is ok and the Share is available at all the times. You don't want your share folder go offline during a long weekend or something and your transaction log on ...


5

The log file on the replica follows exactly the file on the master. They can't be different. You can shrink the master and the replica will automatically change to match.


5

By "log shipping" I'm guessing you're meaning log backup? Log shipping is a high availability strategy. If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit. But that all depends on a lot of environment specific parameters. For instance, do you have a big window on the weekend where you will be able to ...


Only top voted, non community-wiki answers of a minimum length are eligible