Tail log backup query in log shipping reversal
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'\ClusterNameSQL_Tail_BackupDBNAME_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'\ClusterNameSQL_Tail_BackupDBNAME_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!
sql-server transaction-log log-shipping
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
asked Apr 11 '18 at 5:01
Rohit Rohit
61
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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'\ClusterNameSQL_Tail_BackupDBNAME_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'\ClusterNameSQL_Tail_BackupDBNAME_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!
sql-server transaction-log log-shipping
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
asked Apr 11 '18 at 5:01
Rohit Rohit
61
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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'\ClusterNameSQL_Tail_BackupDBNAME_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'\ClusterNameSQL_Tail_BackupDBNAME_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!
sql-server transaction-log log-shipping
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
asked Apr 11 '18 at 5:01
Rohit Rohit
61
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'\ClusterNameSQL_Tail_BackupDBNAME_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'\ClusterNameSQL_Tail_BackupDBNAME_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!
sql-server transaction-log log-shipping
sql-server transaction-log log-shipping
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
asked Apr 11 '18 at 5:01
Rohit Rohit
61
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
asked Apr 11 '18 at 5:01
Rohit Rohit
61
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
edited Apr 11 '18 at 8:13
tinlyx
1,25031130
1,25031130
asked Apr 11 '18 at 5:01
Rohit Rohit
61
asked Apr 11 '18 at 5:01
Rohit Rohit
61
asked Apr 11 '18 at 5:01
Rohit Rohit
61
61
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 27 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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?
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
add a comment |
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).
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f203568%2ftail-log-backup-query-in-log-shipping-reversal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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?
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
add a comment |
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?
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
add a comment |
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?
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
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?
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
answered Apr 11 '18 at 17:14
Micah NikkelMicah Nikkel
17413
17413
add a comment |
add a comment |
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).
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
add a comment |
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).
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
add a comment |
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).
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
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).
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
answered Apr 11 '18 at 19:03
Kenneth FisherKenneth Fisher
19.6k74391
19.6k74391
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
add a comment |
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
If you found the answer correct please mark it that way :) That way it doesn't get bumped up again later as un-answered. Thanks.
– Kenneth Fisher
Apr 12 '18 at 16:35
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f203568%2ftail-log-backup-query-in-log-shipping-reversal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
