1

I am doing log shipping failover and failback configuration in SQL Server 2008 R2 environment. During the Failover, I am taking the tail log backup of primary database with query:

Script to backup Log:----------

DECLARE @filepath VARCHAR(200) 
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn' 
BACKUP LOG [DBNAME] 
TO DISK = @filePath WITH NO_TRUNCATE, NOFORMAT, INIT, 
NAME = N'DBNAME-Tail Log Backup', 
SKIP, NOUNLOAD, NORECOVERY, STATS = 10

And script for Log restore is:

Script to restore Log:--------

DECLARE @filepath VARCHAR(200) 
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn' 
RESTORE LOG [DBNAME] 
FROM DISK = @filepath WITH RECOVERY; 
GO 

But after making secondary database writable, my applications are not connecting to the database. While, trying backup and Restore from GUI works well. Please Help!

  • When you run the RESTORE LOG command via SQL, what is the result? Do you get an error message or some sort of output? When your applications try to connect to the database, what error are they receiving? – Micah Nikkel Apr 11 '18 at 17:14
1

If I had to make a guess your database is just fine, but you never created the server principals (logins) associated with the database principals (users) in your database. It's deprecated but try this and see if you get a result.

EXEC sp_change_users_login 'report'

You can also try this piece of code:

USE dbname;
SELECT logins.name as logins, users.name as users, users.type_desc
FROM sys.database_principals users
LEFT OUTER JOIN sys.server_principals logins
    ON users.sid = logins.sid
WHERE users.type IN ('U','S','G')

If there are any NULLs in the logins column then that's probably your problem. Create the login (if it's a SQL ID then make sure you specify the correct SID).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.