Hello,
I wish to restore a database from another SQL 7 server. The database
restores OK. The original server had a login called "LoginA", which was
enrolled as a user in the database. When I try and add the Login to my
server and enrol it in the restored database it says User already exists.
So, I tried setting us the Login before I restored the database, but the
users are not then enrolled in the restored database. (using enterprise
manager).
I know I can use sp_DropUser to remove it from the restored database and
then re-add it.
However, is there a correct way to restore databases from another Server and
keep the Login / User setting from the original.
Thanks
Tim,Tim
I use this script
Create.LoginsTable.sql
-- Creates table Logins in Northwind
-- containing all SQL Logins on the server
-- (but not 'sa', 'guest', or 'distributor_admin')
USE Northwind
GO
-- Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
-- Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
ORDER BY name
GO
-- Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN ('distributor_admin', 'guest', 'sa')
OR loginname LIKE '%\%'
GO
-- Look at the results.
SELECT name, defaultdb FROM logins
Create.logins.sql
-- Create source-server logins on target-server
USE Master
Go
DECLARE logincur CURSOR
FAST_FORWARD
FOR
SELECT [name], encryptedpassword, defaultdb,
deflanguage, sid, encryptopt
FROM Northwind..logins
DECLARE @.loginame varchar(30),
@.passwd nvarchar(128),
@.defdb nvarchar(128),
@.deflang nvarchar(128),
@.sid varbinary(85),
@.encryptopt varchar(30)
OPEN logincur
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
WHILE (@.@.fetch_status = 0)
BEGIN
EXEC master..sp_addlogin
@.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
END
CLOSE logincur
DEALLOCATE logincur
GO
----
--
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I wish to restore a database from another SQL 7 server. The database
> restores OK. The original server had a login called "LoginA", which was
> enrolled as a user in the database. When I try and add the Login to my
> server and enrol it in the restored database it says User already exists.
> So, I tried setting us the Login before I restored the database, but the
> users are not then enrolled in the restored database. (using enterprise
> manager).
> I know I can use sp_DropUser to remove it from the restored database and
> then re-add it.
> However, is there a correct way to restore databases from another Server
and
> keep the Login / User setting from the original.
> Thanks
> Tim,
>|||Many Thanks
I will try it out.
Tim
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MjE$Qb5DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Tim
> I use this script
> Create.LoginsTable.sql
> -- Creates table Logins in Northwind
> -- containing all SQL Logins on the server
> -- (but not 'sa', 'guest', or 'distributor_admin')
>
> USE Northwind
> GO
> -- Create table Logins in Nortwind
> CREATE TABLE [dbo].[Logins] (
> [Name] [varchar] (30) NULL ,
> [EncryptedPassword] [nvarchar] (128) NULL ,
> [DefaultDB] [nvarchar] (128) NULL ,
> [DefLanguage] [nvarchar] (128) NULL ,
> [sid] [varbinary] (85) NULL ,
> [EncryptOpt] [varchar] (30) NULL ,
> [LoginName] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
> -- Insert information about the logins into table logins.
> INSERT logins
> SELECT name, [password], dbname, language, sid,
> 'skip_encryption', loginname
> FROM master..syslogins
> ORDER BY name
> GO
> -- Remove special SQL logins and all Windows logins.
> dDELETE logins
> WHERE loginname IN ('distributor_admin', 'guest', 'sa')
> OR loginname LIKE '%\%'
> GO
> -- Look at the results.
> SELECT name, defaultdb FROM logins
>
>
> Create.logins.sql
> -- Create source-server logins on target-server
> USE Master
> Go
> DECLARE logincur CURSOR
> FAST_FORWARD
> FOR
> SELECT [name], encryptedpassword, defaultdb,
> deflanguage, sid, encryptopt
> FROM Northwind..logins
> DECLARE @.loginame varchar(30),
> @.passwd nvarchar(128),
> @.defdb nvarchar(128),
> @.deflang nvarchar(128),
> @.sid varbinary(85),
> @.encryptopt varchar(30)
> OPEN logincur
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> WHILE (@.@.fetch_status = 0)
> BEGIN
> EXEC master..sp_addlogin
> @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> END
> CLOSE logincur
> DEALLOCATE logincur
> GO
> ----
--
> --
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
>
>
>
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I wish to restore a database from another SQL 7 server. The database
> > restores OK. The original server had a login called "LoginA", which was
> > enrolled as a user in the database. When I try and add the Login to my
> > server and enrol it in the restored database it says User already
exists.
> > So, I tried setting us the Login before I restored the database, but the
> > users are not then enrolled in the restored database. (using enterprise
> > manager).
> > I know I can use sp_DropUser to remove it from the restored database and
> > then re-add it.
> > However, is there a correct way to restore databases from another Server
> and
> > keep the Login / User setting from the original.
> >
> > Thanks
> > Tim,
> >
> >
>|||Hi Tim,
Thank you for using the Newsgroup and it is my pleasure to help you with
you issue.
From my experience ,after restore a user database from one SQL Server to
another, the database user IDs need to be mapped to the existing sql logins
on the new server. You could use sp_change_users_login to link the security
account for a user in the current database to an existing login on the new
host SQL Server without losing the user permission.
Please look for 'sp_change_user_login (T-SQL) " in the SQL Server Books
Online for detailed information.
You could also run the following script to add the old logins into the new
one ( replace testdb with the name of your database)
use testdb --Change to your database name
go
declare @.UserName nvarchar(255)
declare orphanuser_cur cursor for select
UserName = name from sysusers where issqluser = 1 and
(sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name
open orphanuser_cur fetch next from orphanuser_cur into @.UserName while
(@.@.fetch_status = 0)
begin
if not exists (select * from master..sysxlogins where name = @.UserName)
Begin
print @.UserName + ' does not exist in syslogins'
print @.UserName + ' being added to syslogins (reset password and default
database)'
EXEC sp_addlogin @.UserName, 'password', 'testdb' --Change to your
databasename
end
print @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
fetch next from orphanuser_cur into @.UserName
end
close orphanuser_cur
deallocate orphanuser_cur
Master database has the logins for the user database and they have been
resynced.
Hope this helps and if you still have questions, please feel free to post
new message here and I am glad to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql
Showing posts with label restoring. Show all posts
Showing posts with label restoring. Show all posts
Wednesday, March 28, 2012
Question regarding restoring a Database
Hello,
I wish to restore a database from another SQL 7 server. The database
restores OK. The original server had a login called "LoginA", which was
enrolled as a user in the database. When I try and add the Login to my
server and enrol it in the restored database it says User already exists.
So, I tried setting us the Login before I restored the database, but the
users are not then enrolled in the restored database. (using enterprise
manager).
I know I can use sp_DropUser to remove it from the restored database and
then re-add it.
However, is there a correct way to restore databases from another Server and
keep the Login / User setting from the original.
Thanks
Tim,Tim
I use this script
Create.LoginsTable.sql
-- Creates table Logins in Northwind
-- containing all SQL Logins on the server
-- (but not 'sa', 'guest', or 'distributor_admin')
USE Northwind
GO
-- Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
-- Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
ORDER BY name
GO
-- Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN ('distributor_admin', 'guest', 'sa')
OR loginname LIKE '%\%'
GO
-- Look at the results.
SELECT name, defaultdb FROM logins
Create.logins.sql
-- Create source-server logins on target-server
USE Master
Go
DECLARE logincur CURSOR
FAST_FORWARD
FOR
SELECT [name], encryptedpassword, defaultdb,
deflanguage, sid, encryptopt
FROM Northwind..logins
DECLARE @.loginame varchar(30),
@.passwd nvarchar(128),
@.defdb nvarchar(128),
@.deflang nvarchar(128),
@.sid varbinary(85),
@.encryptopt varchar(30)
OPEN logincur
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
WHILE (@.@.fetch_status = 0)
BEGIN
EXEC master..sp_addlogin
@.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
END
CLOSE logincur
DEALLOCATE logincur
GO
----
--
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
and
I will try it out.
Tim
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MjE$Qb5DHA.1428@.TK2MSFTNGP12.phx.gbl...
--
Thank you for using the Newsgroup and it is my pleasure to help you with
you issue.
From my experience ,after restore a user database from one SQL Server to
another, the database user IDs need to be mapped to the existing sql logins
on the new server. You could use sp_change_users_login to link the security
account for a user in the current database to an existing login on the new
host SQL Server without losing the user permission.
Please look for 'sp_change_user_login (T-SQL) " in the SQL Server Books
Online for detailed information.
You could also run the following script to add the old logins into the new
one ( replace testdb with the name of your database)
use testdb --Change to your database name
go
declare @.UserName nvarchar(255)
declare orphanuser_cur cursor for select
UserName = name from sysusers where issqluser = 1 and
(sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name
open orphanuser_cur fetch next from orphanuser_cur into @.UserName while
(@.@.fetch_status = 0)
begin
if not exists (select * from master..sysxlogins where name = @.UserName)
Begin
print @.UserName + ' does not exist in syslogins'
print @.UserName + ' being added to syslogins (reset password and default
database)'
EXEC sp_addlogin @.UserName, 'password', 'testdb' --Change to your
databasename
end
print @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
fetch next from orphanuser_cur into @.UserName
end
close orphanuser_cur
deallocate orphanuser_cur
Master database has the logins for the user database and they have been
resynced.
Hope this helps and if you still have questions, please feel free to post
new message here and I am glad to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
I wish to restore a database from another SQL 7 server. The database
restores OK. The original server had a login called "LoginA", which was
enrolled as a user in the database. When I try and add the Login to my
server and enrol it in the restored database it says User already exists.
So, I tried setting us the Login before I restored the database, but the
users are not then enrolled in the restored database. (using enterprise
manager).
I know I can use sp_DropUser to remove it from the restored database and
then re-add it.
However, is there a correct way to restore databases from another Server and
keep the Login / User setting from the original.
Thanks
Tim,Tim
I use this script
Create.LoginsTable.sql
-- Creates table Logins in Northwind
-- containing all SQL Logins on the server
-- (but not 'sa', 'guest', or 'distributor_admin')
USE Northwind
GO
-- Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
-- Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
ORDER BY name
GO
-- Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN ('distributor_admin', 'guest', 'sa')
OR loginname LIKE '%\%'
GO
-- Look at the results.
SELECT name, defaultdb FROM logins
Create.logins.sql
-- Create source-server logins on target-server
USE Master
Go
DECLARE logincur CURSOR
FAST_FORWARD
FOR
SELECT [name], encryptedpassword, defaultdb,
deflanguage, sid, encryptopt
FROM Northwind..logins
DECLARE @.loginame varchar(30),
@.passwd nvarchar(128),
@.defdb nvarchar(128),
@.deflang nvarchar(128),
@.sid varbinary(85),
@.encryptopt varchar(30)
OPEN logincur
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
WHILE (@.@.fetch_status = 0)
BEGIN
EXEC master..sp_addlogin
@.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
END
CLOSE logincur
DEALLOCATE logincur
GO
----
--
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
quote:
> Hello,
> I wish to restore a database from another SQL 7 server. The database
> restores OK. The original server had a login called "LoginA", which was
> enrolled as a user in the database. When I try and add the Login to my
> server and enrol it in the restored database it says User already exists.
> So, I tried setting us the Login before I restored the database, but the
> users are not then enrolled in the restored database. (using enterprise
> manager).
> I know I can use sp_DropUser to remove it from the restored database and
> then re-add it.
> However, is there a correct way to restore databases from another Server
and
quote:|||Many Thanks
> keep the Login / User setting from the original.
> Thanks
> Tim,
>
I will try it out.
Tim
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MjE$Qb5DHA.1428@.TK2MSFTNGP12.phx.gbl...
quote:
> Tim
> I use this script
> Create.LoginsTable.sql
> -- Creates table Logins in Northwind
> -- containing all SQL Logins on the server
> -- (but not 'sa', 'guest', or 'distributor_admin')
>
> USE Northwind
> GO
> -- Create table Logins in Nortwind
> CREATE TABLE [dbo].[Logins] (
> [Name] [varchar] (30) NULL ,
> [EncryptedPassword] [nvarchar] (128) NULL ,
> [DefaultDB] [nvarchar] (128) NULL ,
> [DefLanguage] [nvarchar] (128) NULL ,
> [sid] [varbinary] (85) NULL ,
> [EncryptOpt] [varchar] (30) NULL ,
> [LoginName] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
> -- Insert information about the logins into table logins.
> INSERT logins
> SELECT name, [password], dbname, language, sid,
> 'skip_encryption', loginname
> FROM master..syslogins
> ORDER BY name
> GO
> -- Remove special SQL logins and all Windows logins.
> dDELETE logins
> WHERE loginname IN ('distributor_admin', 'guest', 'sa')
> OR loginname LIKE '%\%'
> GO
> -- Look at the results.
> SELECT name, defaultdb FROM logins
>
>
> Create.logins.sql
> -- Create source-server logins on target-server
> USE Master
> Go
> DECLARE logincur CURSOR
> FAST_FORWARD
> FOR
> SELECT [name], encryptedpassword, defaultdb,
> deflanguage, sid, encryptopt
> FROM Northwind..logins
> DECLARE @.loginame varchar(30),
> @.passwd nvarchar(128),
> @.defdb nvarchar(128),
> @.deflang nvarchar(128),
> @.sid varbinary(85),
> @.encryptopt varchar(30)
> OPEN logincur
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> WHILE (@.@.fetch_status = 0)
> BEGIN
> EXEC master..sp_addlogin
> @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> END
> CLOSE logincur
> DEALLOCATE logincur
> GO
> ----
--
quote:|||Hi Tim,
> --
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
>
>
>
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
exists.[QUOTE]
> and
>
Thank you for using the Newsgroup and it is my pleasure to help you with
you issue.
From my experience ,after restore a user database from one SQL Server to
another, the database user IDs need to be mapped to the existing sql logins
on the new server. You could use sp_change_users_login to link the security
account for a user in the current database to an existing login on the new
host SQL Server without losing the user permission.
Please look for 'sp_change_user_login (T-SQL) " in the SQL Server Books
Online for detailed information.
You could also run the following script to add the old logins into the new
one ( replace testdb with the name of your database)
use testdb --Change to your database name
go
declare @.UserName nvarchar(255)
declare orphanuser_cur cursor for select
UserName = name from sysusers where issqluser = 1 and
(sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name
open orphanuser_cur fetch next from orphanuser_cur into @.UserName while
(@.@.fetch_status = 0)
begin
if not exists (select * from master..sysxlogins where name = @.UserName)
Begin
print @.UserName + ' does not exist in syslogins'
print @.UserName + ' being added to syslogins (reset password and default
database)'
EXEC sp_addlogin @.UserName, 'password', 'testdb' --Change to your
databasename
end
print @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
fetch next from orphanuser_cur into @.UserName
end
close orphanuser_cur
deallocate orphanuser_cur
Master database has the logins for the user database and they have been
resynced.
Hope this helps and if you still have questions, please feel free to post
new message here and I am glad to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Tuesday, March 20, 2012
Question on restoring encrypted databases on different servers
I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'
CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.
All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.
I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.
I then try the following commands, which I used when I brought the development database over to the test box the first time:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This caused the following error:
"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."
So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.
From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.
I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:
1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)
2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0
Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?
Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Also, the commands that you need to execute when moving a database to another server are:
OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
The REGENERATE option should be used only if you want to have a new master key in the database.
Thanks
Laurentiu
This solves my problem. Thank you for your help.
However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).
So, how do you make the original database require the Open Master Key Statement?
|||You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.
Thanks
Laurentiu
You can use the following statement:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
Just to verify it:
-- Should fail with error 15581:
-- Please create a master key in the database or open the master key in the session before performing this operation.
create certificate foo with subject = 'foo'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'
go
-- Will succeed
create certificate foo with subject = 'foo'
go
CLOSE MASTER KEY
Go
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi,
I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.
I execute this command and get:
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'
Msg 15313, Level 16, State 1, Line 1
The key is not encrypted using the specified decryptor.
I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.
|||
Hi Sam,
I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?
thanks...
|||I just answered a similar problem in the following thread of this forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1
Hopefully this information will help, but if you still have any problems let us know.
-Raul Garcia
SDE/T
SQL Server Engine
Question on restoring encrypted databases on different servers
I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'
CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.
All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.
I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.
I then try the following commands, which I used when I brought the development database over to the test box the first time:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This caused the following error:
"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."
So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.
From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.
I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:
1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)
2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0
Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?
Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Also, the commands that you need to execute when moving a database to another server are:
OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
The REGENERATE option should be used only if you want to have a new master key in the database.
Thanks
Laurentiu
This solves my problem. Thank you for your help.
However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).
So, how do you make the original database require the Open Master Key Statement?
|||You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.
Thanks
Laurentiu
You can use the following statement:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
Just to verify it:
-- Should fail with error 15581:
-- Please create a master key in the database or open the master key in the session before performing this operation.
create certificate foo with subject = 'foo'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'
go
-- Will succeed
create certificate foo with subject = 'foo'
go
CLOSE MASTER KEY
Go
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi,
I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.
I execute this command and get:
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'
Msg 15313, Level 16, State 1, Line 1
The key is not encrypted using the specified decryptor.
I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.
|||
Hi Sam,
I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?
thanks...
|||I just answered a similar problem in the following thread of this forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1
Hopefully this information will help, but if you still have any problems let us know.
-Raul Garcia
SDE/T
SQL Server Engine
Question on restoring encrypted databases on different servers
I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'
CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.
All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.
I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.
I then try the following commands, which I used when I brought the development database over to the test box the first time:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This caused the following error:
"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."
So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.
From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.
I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:
1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)
2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0
Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?
Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Also, the commands that you need to execute when moving a database to another server are:
OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
The REGENERATE option should be used only if you want to have a new master key in the database.
Thanks
Laurentiu
This solves my problem. Thank you for your help.
However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).
So, how do you make the original database require the Open Master Key Statement?
|||You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.
Thanks
Laurentiu
You can use the following statement:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
Just to verify it:
-- Should fail with error 15581:
-- Please create a master key in the database or open the master key in the session before performing this operation.
create certificate foo with subject = 'foo'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'
go
-- Will succeed
create certificate foo with subject = 'foo'
go
CLOSE MASTER KEY
Go
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi,
I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.
I execute this command and get:
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'
Msg 15313, Level 16, State 1, Line 1
The key is not encrypted using the specified decryptor.
I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.
|||
Hi Sam,
I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?
thanks...
|||I just answered a similar problem in the following thread of this forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1
Hopefully this information will help, but if you still have any problems let us know.
-Raul Garcia
SDE/T
SQL Server Engine
Question on restoring encrypted databases on different servers
I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'
CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.
All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.
I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.
I then try the following commands, which I used when I brought the development database over to the test box the first time:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This caused the following error:
"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."
So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.
From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.
I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:
1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)
2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0
Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?
Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Also, the commands that you need to execute when moving a database to another server are:
OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
The REGENERATE option should be used only if you want to have a new master key in the database.
Thanks
Laurentiu
This solves my problem. Thank you for your help.
However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).
So, how do you make the original database require the Open Master Key Statement?
|||You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.
Thanks
Laurentiu
You can use the following statement:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
Just to verify it:
-- Should fail with error 15581:
-- Please create a master key in the database or open the master key in the session before performing this operation.
create certificate foo with subject = 'foo'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'
go
-- Will succeed
create certificate foo with subject = 'foo'
go
CLOSE MASTER KEY
Go
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hi,
I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.
I execute this command and get:
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'
Msg 15313, Level 16, State 1, Line 1
The key is not encrypted using the specified decryptor.
I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.
|||
Hi Sam,
I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?
thanks...
|||I just answered a similar problem in the following thread of this forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1
Hopefully this information will help, but if you still have any problems let us know.
-Raul Garcia
SDE/T
SQL Server Engine
Friday, March 9, 2012
Question on Log Backup and NORECOVERY
Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>
Subscribe to:
Posts (Atom)