Hi,
I restored a database 2 months ago. After the restore, I lost all my
"users" for that database. It has been a while, so I don't remember
the detail of what I did.
How can I reserve all the users when restoring a database from a file?
Did I do something wrong?
Thank you in advance,
Eddy
I backed up and restored a database successfully except for the users. The only user that shows is dbo, but I can't add a user with the proper user name from my old server; I get the error that the user or role already exists in the current database.
When I go to role, name of public, there is the user I need to add. I can't remove it as it's a table owner, but I need to add it to the list of users, so I need to remove it then re-add it.
I tried using EXEC sp_changeobjectowner to no avail.
Thanks for any help/guidance.
Hi, I'm a noob who sucks at programming, and sucks even more when it comes to database.
I'm operating a small website with an SQL Server database that drives my shopping cart. I want to close this website down, but I don't want to lose all the data in the database in case I want to do something in the future.
How do you backup the entire database into a file so i might be able to import it at later times? Do I have to do this with SQL, T-SQL, or the manager?
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
View 3 Replies View RelatedHi,
I have a few db's that I am backing up from an old sql2000 system and moving them to the new sql2005 server. The first db's restored without any problems but now I am getting some problems with users and logins.
I am restoring in the following order
1.) Creating empty db "123"
2.) Restoring database from file to database "123"
3.) Creating Login to this database (error happens when linking it to the database, "user exists")
When connecting thru QA I get "Cannot open user default database login failed"
I only have 1 login per database and I don't mind manually deleting and recreating them all but it doesnt seem to be working :S
Any suggestions much appreciated!! :)
Thanks!
mike123
I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?
Thanks.
I am restoring a production copy of db XYZ to the development server db XYZ. I want to retain the original users on the development server for db XYZ. How do I create a script that will reset the users on the development server with the same permissions and access they had before the production copy was restored on development? I have searched and searched and cannot find the answer. I can use a third party tool - Embarcadero - to script out the create and update stmts but it is not 100% correct. I end up having to do alot of manual work.
any assistance would be greatly appreciated.
thanks
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedHi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
Hey there - I have a textbox (C# 1.1 OR 2.0) that I want to take it's contents and post to a datatype of some sort in SQL2k5 and have it preserve the breaks (return keystrokes) so when the data is presented, it can hold a paragraph form. Is this possible?something like this: <asp:TextBox id="txtContent" runat="server" TextMode="MultiLine" Rows="10"></asp:TextBox>Right now, I'm using nvarchar(4000) as the datatype but if I were to type, say 3 paragraphs, the data returned in the datagrid I bind that data to shows only one long string of data. For the time being, I have been puttting HTML formatting tags in the content I'm posting to the DB, but ultimately don't want to have to do that. I don't want to have to use a Text Editor (like FTB or FCKeditor) if necessary because they seem very much involved for the simple need I have which is to preserve paragraph formatting.So, is there another datatype in SQL2k5 that I should be using? Or are there simple code snippets I can utilize that will recognize a carriage return as a </p><p> tag?I'm trying to migrate to 2.0 as much as possible, so any C# code would be greatly appreciated in 2.0!Thanks for your help!iSheahan
View 1 Replies View RelatedNot exactly a TSQL question. Query Editor in Management Studio keeps reformatting my sql code every time I hit save, making the code much harder to read. Is there a way to preserve line breaks and indentation in the SQL when creating a view? Thanks.
View 1 Replies View RelatedHow do I move data from one server and append to the same table structure on a second server and preserve the foreign key relationships. In other words TableB.TableA_ID references TableA.ID. The IDENTITY ID numbers themselves will change but they must continue to match up on the destination server the way they did on the source server.
I can do this using procedural programming but that is usually not the way to go.
(btw, this is a cleaned up version of a question I tacked on to another thread)
I have a default installation of SQLServer2000 on a host runningWin2003. I want to install SQLServer2005 on the same machine, butpreserve the original SQLServer2000 installation. How do I do this --install into new directory? new directory and named instance? justnew named instance? What stumbling blocks could I run into?
View 2 Replies View RelatedHello,
I am looking to preserve the ROWGUID column that is created automatically when a table is added to a publication as I have noticed that the column is removed and readded (with new values) each time the publication is deleted and recreated. We are planning on using the values in the rowguid column for a non-replication purpose, and don't wish the column to be removed or the values to be reset in the event the 'owning' publication is dropped for whatever reason. I've done some research and have identified the preserve_rowguidcol column of the sysmergearticles table as a candidate for achieving the desired goal. Before moving forward, I wanted to ask for input regarding this. Are there any best practices I've overlooked on this topic? Neither Google nor this forum's search results yield any real discussion on this topic.
Thanks!
DanD
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
I have following XQuery:
declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
I spent huge amount of time figuring out how to preserve lading and trailing white spaces on report display without success. Can anyone help me here?
My problem is I have data with leading and or trailing white spaces and I need to show it as is. In designer preview it shows correct values. As soon as report is published and accessed on web, it truncates the whitespaces . I had a look at source, it shows values are correctly fetched(with spaces) but are ignored while rendering. I also tried replacing blank space with , however it reads this as &nbsp;.
I am using asp.net 2.0 and SQL serer 2005 reporting services.
I have the following code tha imports the contents of a text file into a varchar(max) field.
Unfortunately the CR/LF are stripped out when I look at the field.
How can I preserve them?
Code Snippet
DECLARE @obj VARCHAR(MAX)
SELECT @obj=BulkColumn
FROM
OPENROWSET(BULK 'C:qsiObjectCreation.sql',SINGLE_CLOB) AS ExternalFile
insert into scriptor (script) values (@obj)
I have a Subscriber database updated using transactional
replication and want to create a copy for development & testing. The Distribution database is running on the
same 2005 instance as the Subscriber and the Publication database on another
server running SQL Server 2005.
If I use a TSQL script to run the restore, the database is
restored to a usable state but I get the following messages:
Msg 15247, Level 16, State 1, Procedure
sp_MSrepl_check_job_access, Line 112
User does not have permission to perform this action.
The replication agent job [job name] was not removed because
it has a non-standard name; manually remove the job when it is no longer in use.
(I€™ve removed the job name from the message)
RESTORE DATABASE successfully processed 46219 pages in
935.413 seconds (0.404 MB/sec).
It looks like an attempt is being made to restore
replication information too. I note that
the RESTORE statement has a KEEP_REPLICATION options which implies (I would have
thought) that by default the replication information is not be restored.
Incidentally, before implementing replication, backing up
the live database and restoring the backup to a new database took < 7
minutes but running the same process with a Subscriber database backup takes
about 15 minutes.
I€™d be interested to know if anyone has any idea what I€™m
overlooking or doing wrong.
ThanksTim
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT Â A.CID,Â
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
1) We have got the database dump (.bak) file in one machine and we need to restore this to another machine where the SQL server (version 7) is running. But in the restore option there is no way to choose any mapped or shared drives in remote machine. Any pointer to resolve this problem?
2) Can you set up a database if you have the .mdf and .ldf files?
I am running SQL 7 on two seperate NT4 servers, a production server and a standby server. Both servers are configured for Mixed Mode authentication. If I back up a database on the production server, do I also need to back up the master database and restore it. My reasoning behind this is that users' login details will be held in the sysxlogins tbl in the master, while their access rights will be held in the sysusers tbl in the database I am backing up and restoring. If I back up and restore a database without backing up and restoring the master db as well, will the connection between the system login and the database user be broken.
Any comments/suggestions would be appreciated.
Hi there...
can any one help in following situation.
Suppose in a network I have two sql server ABC and XYZ and XXX (NT server) on same sub net.
1) I take a backup from server ABC on XXX (NT).(ie.. backup on network)
2) Can I restore database in XYZ which was backed up by ABC? How???
3) Suppose My ABC machines fails and I have to again install SQL Server 7 on it, will I be able to restore database from XXX(NT)? how???
regards,
zak
Currently have large database in three devices, named Cabinet cab2 and cab3. Cab3 device has been expanded twice. We are upgrading the hardware and would like to get the database into one device named cabinet and clean the mess up our vendor initially created for us. Any Ideas/steps on how this could be accomplished??? TKS Jerry
View 2 Replies View RelatedUsing SQL 7.0, has anyone backed up a database and restored on to a different database without the device files being moved around.
For example, With SQL 6.5 we used to backup PROD (source) database and restore to TRAIN (target) database. It worked just great regardless whether the databases are located on the same drive or not. We loved it and used it to refresh TRAIN from PROD on a weekly basis.(using sql Enterprise Mgr)
However, in SQL 7.0 when we attempt this procedure the database and log files of the TRAIN (target) are getting relocated to the drive location of PROD (source) database. Very strange.
Though thru object transfer i.e. import ,we can import data from PROD(source)
to TRAIN (target) database, but it is very slow and takes lot of time and performance degrades for source database (PROD)
Any input on above will be appreciated if backup / restore thru
enterprise mgr or TSQL procedure works in SQL 7.0 for restoring another database which is also on different drive i.e PROD (source) database on
drive C and TRIAN (target) database on drive E.
Thanks
Rajiv
While this may be in the wrong forum, I thought that I should try here first.
One of the developers that works for this company would like to restore a sample database to the SQL server after installing our software.
Is there a command line command that can be issued to restore/create a database with a file (.bak) of an SQL database (sample)? If this can't be done, can it be done with VBScript or something similar?
SQL is not my strong point, and neither is development. Any help would be appreciated.
TIA.
Joe
Hi,
Two of my hard drives went down yesterday. and with it went the Sql server database.The data recovery guys recovered the back up database (.bak) file.I have since reinstalled SQL Server 2000 and now when i try and restore the .bak file it says the Microsoft tape back up file set is not valid when i try and open the .bak file.
Whats the right way to add my databse to the SQl Server2000 using my .bak file and if anyone has come across the error they could help.
Thanks
L
hey...
when i try to restore my database... they gave an error. the database is still in use.. is there a way to kill all the connections to that database ?
Hi,
A while back, I used SQL Server 2000 to back up a database. Now, I am wondering if there is a way to restore this backup into a SQL Server 2005 environment as I no longer use SQL Server 2000?
Thank you
Hi all,
Our Application is made to work with different database. I have a few query
1. There is option of restoring for only table, the tables can be mention during runtime, how to acheive same in sql
2. During restoring if already same data exists, it throws an exception , based on the exception we display the message data already exist. In sql any option is available to check the data exist or not
3. Can sql create the column during runtime.
Thanks