Hi,
I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?
I really apreciate you help,
Regards,
SasIt seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.
http://www.databasejournal.com/features/mssql/article.php/1477441|||UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
It would disable the table in the publication.
When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.
Regards!!
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Problem with "Point in time"- restore of database on new server
In short we are trying to restore a production database backup on a
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
Jan
Your RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
Jan
Your RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>
Problem with "Point in time"- restore of database on new server
In short we are trying to restore a production database backup on a
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
JanYour RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>
nonproduction sql server to a specific point in time. We use the following
type T-sql code:
RESTORE DATABASE XX
FROM DISK = 'F:\XX_Folder\XX'
WITH NORECOVERY,
MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
REPLACE
RESTORE LOG XX_Log
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
We have tried this on two different servers with same OS / DB and
servicepacks (Sql SP3) as the production server.
We get this error:
Server: Msg 913, Level 16, State 8, Line 1
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We are NOT using linked servers on the production server. But both of the
servers we are restoring on previously had databases with the same name as
the one in the backup file (renaming it makes no difference either).
Does anyone have a solution / suggestion to the cause this problem.
Regards, and thank you in advance
JanYour RESTORE LOG statement needs to specify the database name your just
restored rather than the logical log file name. Try:
RESTORE LOG XX
FROM DISK = 'F:\XX_Folder\log'
WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Poulsen" <Jan Poulsen@.discussions.microsoft.com> wrote in message
news:08AE0A2D-A1C1-4923-93F8-CA943AECC2C5@.microsoft.com...
> In short we are trying to restore a production database backup on a
> nonproduction sql server to a specific point in time. We use the following
> type T-sql code:
> RESTORE DATABASE XX
> FROM DISK = 'F:\XX_Folder\XX'
> WITH NORECOVERY,
> MOVE 'XX_1_Data' TO 'F:\MSSQL\Data\XX_1.mdf',
> MOVE 'XX_Data' TO 'F:\MSSQL\Data\XX.mdf',
> MOVE 'XX_Log' TO 'F:\MSSQL\Data\XX_log.ldf',
> REPLACE
> RESTORE LOG XX_Log
> FROM DISK = 'F:\XX_Folder\log'
> WITH RECOVERY, STOPAT='2004-06-28 07:00:00'
> We have tried this on two different servers with same OS / DB and
> servicepacks (Sql SP3) as the production server.
> We get this error:
> Server: Msg 913, Level 16, State 8, Line 1
> Could not find database ID 65535. Database may not be activated yet or may
> be in transition.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> We are NOT using linked servers on the production server. But both of the
> servers we are restoring on previously had databases with the same name as
> the one in the backup file (renaming it makes no difference either).
> Does anyone have a solution / suggestion to the cause this problem.
> Regards, and thank you in advance
> Jan
>
>
Saturday, February 25, 2012
Problem using sp_attach_db with an encypted file system
I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.com/ck_database_encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.com/ck_database_encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
> > I want to run a copy of our Sql2000 production database on my WinXP
> > laptop for development. Because this database contains sensitive
> > information and the laptop cannot be physically secured, I have
> > enabled File Encryption on the project directory to protect the
> > database in the event that someone steals the laptop. To set up a
> > development environment I installed Sql2000 personal edition, and
> > disconnected the production database with:
> >
> > EXEC sp_detach_db @.dbname ='myDB'
> >
> > I then copied the mdf and log files to the encrypted directory on the
> > laptop and attempted to attach with:
> >
> > EXEC sp_attach_db @.dbname = N'myDB',
> > @.filename1 = N'C:\Projects\Data.mdf',
> > @.filename2 = N'C:\Projects\Log.ldf'
> >
> > This failed with the error message: "Device activation error. The
> > physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> > freaking me out, because the exact same command worked fine on the
> > production server to reattach the database. After some trial and
> > error, I removed file encryption on the mdf and ldf file and the
> > database attached without any problem.
> >
> > So my questions is, is this a known problem and is it possible to have
> > file encryption on an SQL database?
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.com/ck_database_encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.com/ck_database_encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
> > I want to run a copy of our Sql2000 production database on my WinXP
> > laptop for development. Because this database contains sensitive
> > information and the laptop cannot be physically secured, I have
> > enabled File Encryption on the project directory to protect the
> > database in the event that someone steals the laptop. To set up a
> > development environment I installed Sql2000 personal edition, and
> > disconnected the production database with:
> >
> > EXEC sp_detach_db @.dbname ='myDB'
> >
> > I then copied the mdf and log files to the encrypted directory on the
> > laptop and attempted to attach with:
> >
> > EXEC sp_attach_db @.dbname = N'myDB',
> > @.filename1 = N'C:\Projects\Data.mdf',
> > @.filename2 = N'C:\Projects\Log.ldf'
> >
> > This failed with the error message: "Device activation error. The
> > physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> > freaking me out, because the exact same command worked fine on the
> > production server to reattach the database. After some trial and
> > error, I removed file encryption on the mdf and ldf file and the
> > database attached without any problem.
> >
> > So my questions is, is this a known problem and is it possible to have
> > file encryption on an SQL database?
Problem using sp_attach_db with an encypted file system
I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?
Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.co...encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.co m...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?
|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.co...encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.co m...
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?
Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.co...encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.co m...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?
|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files reside
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected via
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database will
> not start.
> See the below link:-
> http://www.sql-server-performance.co...encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.co m...
Problem using sp_attach_db with an encypted file system
I want to run a copy of our Sql2000 production database on my WinXP
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.c..._encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files resi
de
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected v
ia
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database wi
ll
> not start.
> See the below link:-
> http://www.sql-server-performance.c..._encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
laptop for development. Because this database contains sensitive
information and the laptop cannot be physically secured, I have
enabled File Encryption on the project directory to protect the
database in the event that someone steals the laptop. To set up a
development environment I installed Sql2000 personal edition, and
disconnected the production database with:
EXEC sp_detach_db @.dbname ='myDB'
I then copied the mdf and log files to the encrypted directory on the
laptop and attempted to attach with:
EXEC sp_attach_db @.dbname = N'myDB',
@.filename1 = N'C:\Projects\Data.mdf',
@.filename2 = N'C:\Projects\Log.ldf'
This failed with the error message: "Device activation error. The
physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
freaking me out, because the exact same command worked fine on the
production server to reattach the database. After some trial and
error, I removed file encryption on the mdf and ldf file and the
database attached without any problem.
So my questions is, is this a known problem and is it possible to have
file encryption on an SQL database?Hi
Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.
1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.
6. Select the advanced option button and follow the prompts to encrypt the
files/folders.
7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).
-- By any chance if you change the service startup account the database will
not start.
See the below link:-
http://www.sql-server-performance.c..._encryption.asp
Thanks
Hari
MCDBA
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@.posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
> EXEC sp_detach_db @.dbname ='myDB'
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
> EXEC sp_attach_db @.dbname = N'myDB',
> @.filename1 = N'C:\Projects\Data.mdf',
> @.filename2 = N'C:\Projects\Log.ldf'
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP
12.phx.gbl>...[vbcol=seagreen]
> Hi
> Are you using "Windows 2000 Encrypted File System option". If yes then you
> have to follow this way.
> 1. Uncheck the File encryption
> 2. Attach the database using SP_ATTACH_DB
> 3. Stop SQL Server service
> 4. Login as the user SQL server service starts
> 5. Select the properties of the folder(s) in which the database files resi
de
> using Windows Explorer.
> 6. Select the advanced option button and follow the prompts to encrypt the
> files/folders.
> 7. Change the service startup account to he user you logged in (Control
> panel -- services - mSSQL Server -- logon option)
> 7. Re-start the SQL Server service.
> 8. Verify the successful start-up of the instance and databases affected v
ia
> the encryption (or create databases after the fact over the encrypted
> directories).
> -- By any chance if you change the service startup account the database wi
ll
> not start.
> See the below link:-
> http://www.sql-server-performance.c..._encryption.asp
> Thanks
> Hari
> MCDBA
>
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0407212013.acd74aa@.posting.google.com...
Subscribe to:
Posts (Atom)