Restoring Databases In Sql 2005 Express - Filename Changes?
Mar 22, 2008
Hello,
Does anyone else ever experience this problem, and if so do you know what causes it:
When ever I restore a database called say XXX.mdf, it restores OK and I can view via MSE as XXX.mdf. Then when I visit MSE again there is another database called C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataXXX.mdf - both are linked to the same .mdf file, however if you try to expand XXX.mdf nothing happens as though the database has been detached?
Cheers MArco
View 3 Replies
ADVERTISEMENT
Sep 15, 2006
Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task scheduler on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task scheduler and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
View 5 Replies
View Related
Dec 27, 2007
I have a question regarding moving databases within the same server but to a different drive. Using the 'Alter Database' query transaction procedures within the SQL Server Management Studio, I'm able to take a user database offline and move it. However, when using the Alter transaction to bring the database back online, I get errors. There was also an attempt that seemed to work, but the app that uses the database through ODBC can't see the database that was moved and errors out.
How can databases be moved and connections re-established within the same server?
Thanks...
View 1 Replies
View Related
Mar 21, 2007
As I said in the subject I've a problem trying to restore a backup of a previous db created in sql 2000 server
When I try to do it I recive the following message:
____________________________________________________________________________________
System.Data.SqlClient.SqlError: Il set di backup include il backup di un database diverso dal database 'musica2007' esistente. (Microsoft.SqlServer.Express.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
------------------------------
Program Location:
in Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
in Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
in Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
____________________________________________________________________________________
What should I do? What's the probem? I've already tried to look for the solution in other messages but I didn't find anything..... Thanks for help,,, by Luke
View 1 Replies
View Related
Sep 18, 2006
Hi all.
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
Thanks in advance for your help.
View 1 Replies
View Related
Jun 2, 2008
i'm trying to set up one of the asp.net website starter kits and part of the process is to backup the sql express db and then import it into the sql 2005 at the web hosting account. it's not working and i'm wondering if there's any difference between the backups they make. the sql express makes a file with a .bak extension and the sql 2005 at the host makes a file with a .sqlbak extension. i was assuming that was just a naming thing and not a structural thing but the host's tech support has said:
"This sort of thing happens when the database being imported is not properly formatted. You can try downloading and using sql express from microsoft to see if you can do it manually, but most likely you will need to save your database again properly set for import to an mssql server rather than however you did."
all i had done was rename the .bak file from express to .sqlbak as that was the choice for backup files.
i will note that it also failed importing the .mdf, which works fine locally with the site but i did make some changes to it from the original that came with the starter kit.
should a backup file made with sql express (using sql management studio express, by right clicking on the file and choosing tasks > backup) normally be importable by sql 2005?
or, i suppose, does it make sense that a db that works fine locally with sql express (within a visual web developer express project anyway) would actually be "formatted" in a way that would cause it to fail to import into sql 2005?
thanks much for any assistance.
View 3 Replies
View Related
Apr 3, 2007
Hello Everyone,
I am trying to restore a bak file which came with code for a tutorial. I think it is safe to restore, but sql server 2005 express has the following problem when I try to restore it.
TITLE: Microsoft SQL Server Management Studio Express------------------------------
Restore failed for Server 'BOBSQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDashboard.mdf'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
The links given for help have no information about the problem and I'm not really sure where I should be looking to solve this. I'm a newbie to databases so if you could provide a step-by-step answer that would be best. Thanks in advance.
Robert
View 4 Replies
View Related
Jul 31, 2006
SQL 2005 Express - Database Restore size problem
Was this post helpful ?
Hi,
I'm trying to restore a SQL Server DB Backup from a SQL Server DB Server on to my Laptop (SQL 2005 Express)
When I execute a restore filelistonly command on the backup file, It seems that the Database included is 1GB, but the Log file is 91 GB in size, which exceeds my diskspace.
I can restore the Data on its own without the log file, but the the Database stays in "restoring" mode. I've tried to switch the restore flag off (update sys.databases set state = 0 where name = 'G001'), but I can`t seem to be able to do it, even if I try to allow updates via:
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Any ideas how I can restore the database without restoring the enormous logfile?
Thanks in advance...
View 3 Replies
View Related
Sep 4, 2006
Hello,
I tried to restore a database which I backuped from MS SQL 2000 to SQL Express 2005 but it always failed with the following error message. What could be the problem?
Restore failed for Server 'SERVSQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
Additional information
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'gtp' database.
(Microsoft.SqlServer.Express.Smo)
Thanks
View 23 Replies
View Related
May 16, 2006
Hi,
I have backup of data from SQL Server 7.0 and now when i'm trying to restore it into SQL Express 2005, I'm getting following error......
--------------------
Msg 3154, Level 16, State 2, Line 1
The backup set holds a backup of a database other than the existing 'GOSLDW' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
--------------------
Here is SQL i'm using to restore database,
RESTORE DATABASE GOSLDW
FROM DISK = 'C:sqlserverDataGOSLDW'
WITH MOVE 'GOSLDW' TO 'C:sqlserverDataGOSLDW.mdf',
MOVE 'GOSLDW' TO 'C:sqlserverDataGOSLDW.ldf'
GO
Why i'm getting this error? Am i missing anything here?
Thanks in advance for your HELP.
- R
View 8 Replies
View Related
Jul 13, 2006
Obadiah writes "hello,
I am a newbie to developing .net applications unfortunately my boss and my colleaques think i am a pro (interview went too well) and i would like to get at least this months paycheck before am fired....so i here's my dumb question:
I want to create a SQL database from my vb.net application using stored procedures, however i want to be able to set the Filename attribute ('Filename = C:Program FilesMicrosoft SQL ServerMSSQL$SARDONYXINSTANCEData estdb.mdf') by means of a input parameter to the stored procedure. like so...
Filename = @databasepath.
HERE is what i have:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@Databasepath varchar(100) = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf' , @Databaselogpath varchar(100)= 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]
CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @Databasepath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @Databaselogpath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
The above procedure works fine when Filename = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf'
but bombs out when Filename = @Databaselogpath.
Thank you in advance."
View 2 Replies
View Related
Aug 9, 2006
Help me please!
I've installed SQL Express 2005 on my XP. I'm trying to create a database but I keep getting the error no matter what I do.
Msg 1802, Level 16, State 4, Server MYCOMPUTERSQLSERV, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server MYCOMPUTERSQLSERV, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:Program FilesExos9300DatabaseExos93.mdf'.
View 1 Replies
View Related
Mar 24, 2007
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon
View 1 Replies
View Related
Nov 6, 2006
Two Windows 2003 server,one with SQL 2005 server,another with SQL Express.Is it possible to copy databases from SQL 2005 to SQL Express?Thanks.
View 6 Replies
View Related
Dec 5, 2007
This post is an extension onto the one below....
Please read my question at the bottom... cheers
---------------------------------------------------------------------------------------------------------------------------------------
Sept. 26th 2006
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
- Noble Bell
I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting?
There are two ways to create databases, depending on your goal:
To just create a database on your server, do the following:
Open the Server Explorer
Right-click on Data Connections
Click Create New SQL Server Database
Specify Server Name and Database Name
Your database will be created and you can start working with it.
Embed a database in your project:
On the Project menu, click Add New Item.
Select SQL Database from the list and give it a name.
Click OK
This will run you thorugh a wizard to create the database.
If your having problems doing either of these, you may not have SQL Server installed on your computer or VS might be pointing to the wrong Instance Name. Check out the Option dialog under Database Tools:ata Connection and verify that the correct instance is specified.
- Mike Wachel - MSFT
---------------------------------------------------------------------------------------------------------------------------------------
I am trying to create a similar project and I also recieve an error while trying to create a database...
"An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could Not Open A Connection to SQL Server."
Now let me explain what i am trying to do ...
I am trying to create a web interface for the data that I want to store in the SQL database. I have installed VS2005 & SQL Server Express 2005 on my local machine. I want to develope this project on my local machine and then transfer it to a server once i have finished the project.
If i am doing this all wrong, please let me know !
View 2 Replies
View Related
Aug 23, 2006
I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?
Connection String #1 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:Program FilesMaterial_Management_SystemDATAMain.mdf;Data Source=Steve_Laptopsqlexpress
Connection String #2 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:Program FilesMaterial_Management_SystemDATAItems.mdf;Data Source=Steve_Laptopsqlexpress
Sql Query: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID
Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.PFMS'
Sql Query#2: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.dbo.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID
Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.dbo.PFMS'.
I am running the queries directly from the 2005 Mgt window to take as many variables out of the equasion. I get the same error in Visual basic
How am I supposed to reference a join of 2 databases? Any suggestions
View 1 Replies
View Related
Sep 27, 2006
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
View 1 Replies
View Related
Jan 10, 2007
Hi,
In SQL Server 2005 Express I currently have 2 databases which I€™m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.
View 1 Replies
View Related
Oct 3, 2007
I've got about 2 months worth of daily databases created in SQL Express 2005 for a pilot project, which the client is converting to a purchase. Unfortunately they're using SQL Server 2000 and I can't figure out how to migrate the databases to the older version. All my searching turns up how to do it in the expected direction.
Any pointers to how or where to search appreciated.
Thanks,
Wendell
View 5 Replies
View Related
Mar 25, 2015
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit
2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard
3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server? I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit.
View 3 Replies
View Related
Mar 16, 2007
Help, don't quite understand, or more likely I've missed the point!
Using VWD 2005 Express, I can create, manage/modify and SQL 2005 database aswell as maintain any data I enter into it. Fine, no problem there. However, when it comes to doing the same kind of thing with the database using MSSQL 2005 Management Studio Express, I remain mystified!?
Any database I create above, through VWD 2005 Express, shows as being an attached DB in SQL MSExp and I'm able to detach and re-attach them. However, they are always attached as read only files so are unmodifiable!!!
So... What is it I'm missing? What do I have to do to be able detach, attach, re-attach such databases in a modifiable form, ie NOT read-only
Pulling my hair out over this one, so any advice gratefully rec'd
Thanks in anticipation.......!!!
View 1 Replies
View Related
Nov 28, 2000
I am fairly new to SQL Server and have a question about restoring a database . If a server that contained SQL Server was totally destroyed, and we have backups stored on an external server, how do we restore these backups. I am assuming I would have to install a new instance on the server we buy to replace the destroyed one. Once installed how do I convince or tell SQL Server that there are backups out there. I could move the archived backups back to the new server, but how do I point the new instance of SQL Server to these backups? Thanks in advance for any help.
View 1 Replies
View Related
Jan 10, 2007
Guys,I need to restore a database which is 400 GB plus. Usually a normalrestore from a backup file takes about 40 minutes. I have a databaseCompany on a server A. I need to use the backup of database company torestore as company1 on the same box. At the end, company and company1would reside on the same box. Is there any way to do it faster than anormal restore???Thanks in advance.RegardsJaideep
View 3 Replies
View Related
Nov 15, 2007
I try to restore a database but I receive the message "...the database is compressing. Uncompress the database and try again..."
In the original system (MSQL 2003) the backup is doing by the utility "backup"
In my personal system (MSQL 2005) I use the restore utility
Notice that I don't use winzip, winrar or other compressing software
Can anybody help me?
Thanks in advance
View 3 Replies
View Related
Feb 19, 2007
Hi!
I'm a beginner w ith SQL Express and am having some problems. I am using 2005 SP1 and installed the SQL Server Management Studio Express as well. I have a backup from a SQL Server 7.0 database that I am trying to restore into my database using the management studio. When I try to restore from the file into my database, I get the error,
System.Data.SqlClient.SqlError: The backup of the system database on the device D:GMBeta2Local.bak cannot be restored because it was created by a different version of the server (7.00.1063) than this server (9.00.3033). (Microsoft.SqlServer.Express.Smo)
Is this true? Is there any way for me to get this data into my SQL Server 2005 express on my laptop?
View 1 Replies
View Related
Jun 17, 2002
I would like to know if its OK to restore a SQL 7 user database to SQL 2000.
I have looked into Books On Line, with no success.
Anyone know where I might find some documentation on this topic?
Many thanks in advance.
Gary Andrews
View 1 Replies
View Related
Sep 7, 2007
Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder. If I attempt either method, I get the following dialog box:Required Components MissingConnections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251 The link above just takes you to the download page for Sql Server Express 2005. I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running. The existing database will work properly in my web application, however I cannot access it through Visual Studio.As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error. But I was able to fix that by renaming my SQLEXPRESS folder in C:Documents and SettingsuserLocal SettingsApplication DataMicrosoftMicrosoft SQL Server Data. When the web page ran, it created another SQLEXPRESS folder.However, I cannot figure out why I am having the other issue.Thanks in advancePatrick
View 1 Replies
View Related
Oct 24, 2013
I have a single .bak file containing full backups of 20+ databases.
Whats the best way of creating and restoring these database onto an instance on a separate server.
note that this is to migrate the whole instance of sql server onto a newly build server.
View 1 Replies
View Related
Feb 14, 2006
I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:program filesmicrosoft sql servermssqldata
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
I even tried
sp_attach_single_file_db @dbname = 'SWATraining',
@physname = 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf'
but reason this error
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
View 4 Replies
View Related
Jun 20, 2007
Hello:
I'm working with two sql servers instances, ServerA and ServerB, which run under two different service accounts on different machines. They both have a database, DatabaseA, that has some encrypted fields.
If I take a backup of DatabaseA on ServerA and restore it on ServerB, I need to re-encrypt the Database Master Key (DMK) with the Service Master Key (SMK) as follows:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd used to encrypt DMK'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
However, if I again take a backup on ServerB and then restore it on ServerA, I can use the DMK on ServerA without re-encrypting it with the SMK.
Shouldn't I have to re-encrypt the DMK with the SMK everytime I restore from a backup that was generated from a different server?
Thanks,
Cyndi
View 1 Replies
View Related
Sep 7, 2007
Doesn't appear you can do this.
Am I wrong?
Please tell me I am.
View 11 Replies
View Related
Jul 3, 2006
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.
View 12 Replies
View Related
Jan 16, 2008
I have a backup that contains multiple databases. I am restoring it to a different server. How do I restore each database? Tried to restore under SQL Server Management Studio, but can only restore one of many from the backup (it appears it has only 1 logical file name in the backup) If you can refer me to a web page that would be great. I tried googling the topic but nothing seems to come up.
View 5 Replies
View Related
