restoring master on sp3 to sp2

I have torestore a master database from sql server 2000 running sp2 onto a
sql server 2000 running sp3. Would it cause any issues? Thanks.
Hi
"sharman" wrote:

> I have torestore a master database from sql server 2000 running sp2 onto a
> sql server 2000 running sp3. Would it cause any issues? Thanks.
Why?
You should have backed up the system database post upgrade (the installation
does tell you to do that!!)
John
|||Hi John,
I think I haven't phrased my question correctly. My question is can I
restore master, model and msdb on sql 2000 sp3 from backups that were backed
up on SQL 2000 SP2?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Why?
> You should have backed up the system database post upgrade (the installation
> does tell you to do that!!)
> John
|||Hi
On Jun 27, 5:14 pm, sharman <shar...@.discussions.microsoft.com> wrote:
> Hi John,
> I think I haven't phrased my question correctly. My question is can I
> restore master, model and msdb on sql 2000 sp3 from backups that were backed
> up on SQL 2000 SP2?
>
> "John Bell" wrote:
>
>
>
> - Show quoted text -
You can try this!! AFAIK there is no guarantee that something has
changed in a service pack or that it will work correctly, therefore it
is best to restore the same level.
John

restoring master on sp3 to sp2

I have torestore a master database from sql server 2000 running sp2 onto a
sql server 2000 running sp3. Would it cause any issues? Thanks.Hi
"sharman" wrote:

> I have torestore a master database from sql server 2000 running sp2 onto a
> sql server 2000 running sp3. Would it cause any issues? Thanks.
Why?
You should have backed up the system database post upgrade (the installation
does tell you to do that!!)
John|||Hi John,
I think I haven't phrased my question correctly. My question is can I
restore master, model and msdb on sql 2000 sp3 from backups that were backed
up on SQL 2000 SP2?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Why?
> You should have backed up the system database post upgrade (the installati
on
> does tell you to do that!!)
> John|||Hi
On Jun 27, 5:14 pm, sharman <shar...@.discussions.microsoft.com> wrote:
> Hi John,
> I think I haven't phrased my question correctly. My question is can I
> restore master, model and msdb on sql 2000 sp3 from backups that were back
ed
> up on SQL 2000 SP2?
>
> "John Bell" wrote:
>
>
>
>
>
> - Show quoted text -
You can try this!! AFAIK there is no guarantee that something has
changed in a service pack or that it will work correctly, therefore it
is best to restore the same level.
John

Restoring Databases with Encrypted Data to Another Server

I use the following code to create encrypted data in a table called TEST in
a
SQL Server 2005 database.
-- Use the AdventureWorks database
USE AdventureWorks;
-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@.ssw0rd';
-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID INT PRIMARY KEY,
FirstName NVARCHAR(200),
MiddleName NVARCHAR(200),
LastName NVARCHAR(200),
eFirstName VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName VARBINARY(200));
-- Create a Test Certificate
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'Adventureworks Test Certificate',
EXPIRY_DATE = '10/31/2009';
-- Create a Symmetric Key
CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
-- EncryptByKey demonstration encrypts 100 names from the Person.Contact tab
le
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID,
EncryptByKey(Key_GUID('TestSymmetricKey'
), FirstName),
EncryptByKey(Key_GUID('TestSymmetricKey'
), MiddleName),
EncryptByKey(Key_GUID('TestSymmetricKey'
), LastName)
FROM Person.Contact
WHERE ContactID <= 100;
-- DecryptByKey demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByKey(eFirstName),
MiddleName = DecryptByKey(eMiddleName),
LastName = DecryptByKey(eLastName);
-- View the results
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM Person.#Temp
--create physical table
select * into TEST from person.#temp
--view results from physical table
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM TEST
----
I backup the database and restore to another server. After the restore
finishes, I run the following commands in the restored database:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@.ssw0rd';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
When I run the following SELECT statement to view my data in the restored
database, I only get NULLs.
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM TEST
From what I have researched, this is all you have to do. I must be missing
a step. Can anyone tell me what I have forgotten or provide me a list of
steps to execute when I am restoring a database with encrypted data to
another server'
Thanks,
DBI think you forgot to open the key - you were doing this step on the
original server:
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"DB" <DB@.discussions.microsoft.com> wrote in message
news:8511E6F6-0912-4274-9CA0-605295981376@.microsoft.com...
>I use the following code to create encrypted data in a table called TEST in
>a
> SQL Server 2005 database.
> -- Use the AdventureWorks database
> USE AdventureWorks;
> -- Create a Database Master Key
> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@.ssw0rd';
> -- Create a Temp Table
> CREATE TABLE Person.#Temp
> (ContactID INT PRIMARY KEY,
> FirstName NVARCHAR(200),
> MiddleName NVARCHAR(200),
> LastName NVARCHAR(200),
> eFirstName VARBINARY(200),
> eMiddleName VARBINARY(200),
> eLastName VARBINARY(200));
> -- Create a Test Certificate
> CREATE CERTIFICATE TestCertificate
> WITH SUBJECT = 'Adventureworks Test Certificate',
> EXPIRY_DATE = '10/31/2009';
> -- Create a Symmetric Key
> CREATE SYMMETRIC KEY TestSymmetricKey
> WITH ALGORITHM = TRIPLE_DES
> ENCRYPTION BY CERTIFICATE TestCertificate;
> OPEN SYMMETRIC KEY TestSymmetricKey
> DECRYPTION BY CERTIFICATE TestCertificate;
> -- EncryptByKey demonstration encrypts 100 names from the Person.Contact
> table
> INSERT
> INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
> SELECT ContactID,
> EncryptByKey(Key_GUID('TestSymmetricKey'
), FirstName),
> EncryptByKey(Key_GUID('TestSymmetricKey'
), MiddleName),
> EncryptByKey(Key_GUID('TestSymmetricKey'
), LastName)
> FROM Person.Contact
> WHERE ContactID <= 100;
> -- DecryptByKey demonstration decrypts the previously encrypted data
> UPDATE Person.#Temp
> SET FirstName = DecryptByKey(eFirstName),
> MiddleName = DecryptByKey(eMiddleName),
> LastName = DecryptByKey(eLastName);
> -- View the results
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM Person.#Temp
> --create physical table
> select * into TEST from person.#temp
> --view results from physical table
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM TEST
> ----
> I backup the database and restore to another server. After the restore
> finishes, I run the following commands in the restored database:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@.ssw0rd';
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
> When I run the following SELECT statement to view my data in the restored
> database, I only get NULLs.
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM TEST
> From what I have researched, this is all you have to do. I must be
> missing
> a step. Can anyone tell me what I have forgotten or provide me a list of
> steps to execute when I am restoring a database with encrypted data to
> another server'
> Thanks,
> --
> DB