Restore Of Database Backup To Another Database Makes Users DBO
May 7, 1999
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?
View 1 Replies
ADVERTISEMENT
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
View 8 Replies
View Related
Jul 3, 2007
I am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download.
When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end.
Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time?
View 6 Replies
View Related
Nov 25, 2005
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
Manolis
View 1 Replies
View Related
Aug 10, 2007
Hi,
I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.
I am using VB.Net to do the backup and restore.
Is there any way of doing it?
Thanks!
View 21 Replies
View Related
Apr 1, 2008
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
View 3 Replies
View Related
Oct 17, 2006
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
View 3 Replies
View Related
Apr 29, 2015
What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
View 1 Replies
View Related
Oct 14, 2007
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
View 16 Replies
View Related
Jan 24, 2002
My DBA does a backup of the database and then it gets encrypted and compressed using PGP. This file is sent via ftp to an outside vendor. When they receive it, decrypt it and try to restore, they get the following error:
The media family on device c:dynaxoto1927131153 is incorrectly formed. Sql Server cannot process this media family”
We have tried the process in reverse where we have the vendor send us a file and it works fine.
Please provide some insight.
Thanks,
Robin Uffer
Technical Project Manager
robin.uffer@bankofamerica.com
View 1 Replies
View Related
Apr 8, 2004
Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
I can ftp to the webserver in question to download the backup file and upload a file which needs restoring but i need a web tool to carry out the operations.
Or if there is another method that would work please let me know.
Thanks
View 4 Replies
View Related
Apr 28, 2005
Hi all,
I want to restore My database from backup
restore database HKData from disk ='H:HKData.DAT'
go
i got error at
Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'HKData' database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 8 Replies
View Related
Oct 28, 2004
hi all,
i just learned mssql server and need help form u guys the experts. i made a backup of database but i couldnt restore it into another machine.
the original database name is "employee" and running in mssql 7. the target database is named "employee" as well and running in mssql 2000.
it gave me this message:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Cannot find file ID 2 on the device 'C:db_bakemployee.dat'.
RESTORE DATABASE is terminating abnormally.
i tried to restore it to another mssql 7 but it gave me the same error message. i dont know how to fix it. in the sql server log, nothing refers to the restore activity. please help....
thanx in advance,
tonjaw
View 1 Replies
View Related
Sep 21, 2005
I mean, how can I do that in code. I know how to do it in EM but this not what I need.
Thank you!
View 1 Replies
View Related
Apr 21, 2008
Hi Gurus,
I want to know(get ) more information regarding DATABASE BACKUPS & RESTORE in sqlserver 2005.
Can you pls post few sites regarding this.
Thanks in Advance.
Thanks,
ServerTeam
View 2 Replies
View Related
Oct 2, 2007
i want to create application for backup and restore my database.
example
yesterday i backup a database into disk name = fileA
Today i have something wrong with my currently database. I need to use fileA. what should i do?
Now i try 2 ways
1. use sp_detach_db + sp_attach_db but they still have problem cause fileA and currently database it's a same data because they update all file that use same database name
2. use backup and restore sql. i think, it should be better way but i don't know how to use it.
please give me some suggestions thank you for coming
View 4 Replies
View Related
Mar 21, 2007
Hi,
I've got a following problem: I have a VB 2005 application that uses SQL Server 2005 Express as DB engine. This application has it's own database stored in mdf file, e.g. invoices.mdf with corresponding log file.
I'd like to give users opportunity to backup and restore this DB file from this application (there won't be a system administrator, only end-users). Unfortunatelly after I connect to this database (using user instance of SQL Server), I'm unable to read or copy it, despite the fact, that I close all connections. And I'm sure that all connections are closed.
Database files become accessible for reading after I close the whole application.
I've tried to detach this database using sp_detach_db prcedure, but it can't be done with database I'm connected to.
I will be grateful for any help.
Tomasz
View 6 Replies
View Related
May 19, 2000
I need to restore a database (that was backed up thru enterprise manager
and then burned to a CD) to a new computer that I just installed SQL
server 7.0 desktop version (NT workstation 4.0). I created a backup set
of a bogus database on the new computer, then copied the real database
from CD to that location. It won't let me restore it that way, does anyone
know how else to approach it? Thanks.
View 2 Replies
View Related
Feb 2, 2000
Hi all,
I want to transfer a database from one MS SQL Server 7.0 to another.
When I create a new database (SQL Server 7.0) and then try to load a backup, I always end up with a message that the backup database is not identical with the destination database. That is correct and I know it, so what!? Is there any other possibility to transfer a database other than Export/Import. Using this method took me about 48 hours for 6 GByte database!
All suggestions are welcome
Thank you
Karsten
View 3 Replies
View Related
Jun 9, 2004
I want to backup an SQL Server database through a client application written in VB 6.0, and not through the Enterprize Manager.
I use the SQLDMO Objects Library to backup the database to the SQL Server machine through my application,
but there are the following problems:
a. The backup can be done only locally to the SQL Server machine. Is there any way to bypass this restriction?
b. To get the Directories Structure of this machine i use the xp_FixedDrives, xp_Subdirs stored procedures.
That's ok for the Backup operation but i do not have a way to view the FILES to perform Restore operation.
Of cource VB function Dir() cannot be used to view files on the server because of access restrictions.
Is there any workaround for that?
View 2 Replies
View Related
Apr 22, 2006
Hi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same
name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
View 3 Replies
View Related
Jul 5, 2006
Hi, I have setup a merged replication on two server. Server A contains the Publisher and the Distributor database and Server B contains Subscriber.
If the database at the Subscriber got corrupted. I am thinking maybe i can 'restore' the subscriber database by resynchronising the data from the publisher to subscriber.
But what happen if the Publisher database failed? How should one restore the publisher database and then restore the replication setup?
What will be a good backup/restore strategy for such a setup?
View 1 Replies
View Related
Jun 30, 2004
Can somebody please tell me how to go about creating a backup file of a MS SQL database, then import it into another server?
View 2 Replies
View Related
Apr 23, 2006
Hi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
View 1 Replies
View Related
Feb 24, 2004
I wish to restore master database which was located on "e:" on my server.
the restored copy is residing on "f:"
when i restore the backup and restart, the server does not start.
is there any way to avoid this and perform the restoration succesfully.
or the process followed by me is wrong/incomplete.
do let me know...TIA
View 1 Replies
View Related
Jul 22, 2013
I have a .bak full database backup from a computer. Now I installed new SQL Server 2005 in another computer and would like to restore the .bak database here. How it can be done?
When I right click database and select restore database, it opens a window where it asks to put "destination for restore". This is a new SQL Server, how can I restore then?
View 3 Replies
View Related
May 4, 2015
Now i must restore a database from a backup file of MS SQL Sever 6.5,but this file is bad. When i restore database from this file, SQL Server tell me that the format of this file is not file format of SQL Server 6.5.So that I must repair this backup file firstly. I don't know database file format of SQL Server 6.5.
View 2 Replies
View Related
Mar 4, 2008
Hello ,
can anybody tell me how can we make DATABASE BACKUP AND RESTORE SCRIPT through t-sql .And thn i like to make a schedular that everymonth database backup and restore query run automaticaaly according to schedular.
regards
sql dba
jagpalsingh
View 1 Replies
View Related
Sep 21, 2007
I am trying to restore a SQL Server 2005 database backup file.The size of the database backup is 5.51 GB. I specify the source as a device and then provide the path of the .bak file and press OK i get the error as follows
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The media family on device 'G:ITWeb DB BkupDB BkupNew BkupWSS_Content_ITWEB.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Please let me know how we can solve this issue.
Satish
View 5 Replies
View Related
Sep 25, 2006
Im trying to backup the databse by selecting the database source, backup type = full, name the backup set, and leave the rest as default. The backup seems to be successful.
The problem comes when I need to restore the backup database, I 've tried many times and a few different kinds of selection, all fails. I've selected the source for restore from device, that is the .bak file from backup folder and remains the rest of selection as default. Here is the error message I get:
System.Data.SqlClient.SqlError: The backup set holds a backup of the database other than the existing 'testing' database. (Microsoft.SqlServer.Express.Smo)
Anyone can help to solve this problem??!
View 9 Replies
View Related
May 13, 2008
Hello,
My name is Elizabeth and I'm a SQL newbie.
So we do SQL 2005 Database backup daily. At one point, we have to restore the backup. However, we cannot restore it whatsoever; it keeps saying that the database is in use.
It is actually a SQL Database for a CRM Server. I stop the internet service, so CRM or any other instances cannot pull up information from the SQL Database. I try running sp_who and killing the processes that are still using the database, but those processes keep coming back again.
Here is the error that I keep getting.
Could anyone please help me on this one? Thanks in advanced!
-Elizabeth K.
View 4 Replies
View Related
May 2, 2013
I was wondering if there is a way to take backup or restore the database using SSDT.
View 6 Replies
View Related
Mar 19, 2001
Hello,
I have a user database which has 1 data file and 1 logfile.
I did a complete backup of the database to a file on the disk drive, using Enterprise manager. Due to some reason, I had to drop the database. The only way I could restore it was by,
1. Create the database with the same name.
2. Restore the complete backup from the file device.
While doing so,
I get the message "Backup set holds the backup of daatabase other than the existing 'userdbname' database. BAckup terminating."
Is it because the database was dropped and recreated.
If I choose the option to overwrite, it restores successfully.
Is this normal and right way to do it? Is there any thing else that I need to take care of, while backup or restore?
Any suggestions welcome.
Thanks,
MMS
View 3 Replies
View Related
