I'm having a serious problem in SQL 2000 SP3 Database which is used Business Applications.
Today, I tryied to take backup in service time by the Enterprize Manager, the following
error occured and could not take backup.
--------------------------------------------------------------------------------------------------------------------------------------
Error 926 : Database '[database name]' cannot opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
--------------------------------------------------------------------------------------------------------------------------------------
After that, we tried same operation 3 times. But result is same.
After sometime, end-user report us that they can not log-in to database.
So we tried to dettach this database and attached it.
We success to dettach, but we can not attached it.
Also we tried to restore this database from Yesterday's backup file by using Enterprize Manager,
but the following error occured, and could not complete.
This backup file was generated daily backup task in Maintenance Plan in Enterprize Manager.
--------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQl-DMO(ODBC SQLState:42000)
The media family on device "E:ccd......xxx.bak" is incorrectly formed.
SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally.
--------------------------------------------------------------------------------------------------------------------------------------
We need to recover the database immediately not to stop the end-user business process.
Please help us.
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 have a db ABC and want to restore it as ABCbak, I am taking the backup of the db ABC and restoring it on ABCbak, i am also seeing that the physical names are different but logical names remains the same. Will this cause any kind of problem to db ABC.
Hi Is there a way to restore only one table from a DB backup. I need restore one table not database because I have update others tables in this database. thanks in advance kinds regards Abdel
There is a database located at remote location we wanted the same on the local server..so we backed it up then restored the same on the local server everything went successfull.
When i tried to create user it says the user is already existing, but nither can i see the user nor drop the one..(ofcourse not existing) it gives me an error 15023.
The other problem is that when i check the storedprocedures i can see and open them when i try to look for permissions or assign it by clicking permissions tab it says the specific stored procedure doesnot exist and gives me a sql-dmo error 21770.
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.
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.
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.
I have a 95GB database (dump file is 73 GB) and I want to restore it on a different server. I have created 95GB devices and tried to restore it, but it failed because of reason " page xxx had non-log objectid xxx while checking in syslogs" . I tried locating the object on that page but it showed page is in cache.
I accidentally restore an old database backup to my current database!! The worst is I don't have any new backup for my current database....:eek:
So ... does anybody here know how to solve this problem?? Can I undo the transaction log?? if it is possible.. could you please teach me the way to undo it?? I'm desperate.....
I'd greatly appreciate if you can answer me....
Thanks in advance... __________________ Best Regards, Debbie
We have an order posting stored procedure that's been executing for 14 hours now. This procedure has simple update statements but all the tables updated have triggers which update other tables with triggers and so on. This procedure hasn't commited yet. I have a full database backup from last night and transaction log backup from this morning while it was running. I want to kill this job and restore database from last backup. Only thing I am afraid of is that it will take too long to roll back which I have no estimate of time. Is there any faster way to get rid of all the uncommited transactions? Has anybody rolled back such a massive transaction? Any idea on how long it can take? Can it affect the overall server performance as this is the main production ERP server. I can see in profiler that this procedure is still running and not hung.
I am having difficulty restoring a database (DB_1) with 2 datafiles and one log file, DB Structure is as following:
Filegroup PRIMARY with file name 'fnm_data' with physical file name and location D:dbfile_1.mdf, Filegroup 'FG1' with file name 'fgnm1_data1' with physical file name and location F:dbfile_FG1.mdf, One log file with file name 'fnm_log' with physical file name and location (E:loglog_db.ldf)
{Note} Logfile resides on E drive whereas Datafiles reside on several other drives.
I need to restore the DB on another machine. And I need to move the log file to a drive other than E.In this case it is K drive. Hence I have used the following code,
restore log DB_1 from disk='M:kupccciclog.txt'
WITH RESTRICTED_USER , MOVE 'fnm_log'
TO 'K:DBlog_db.ldf', recovery, replace,RESTART
go
When I try to restore from backup files, I keep error message saying "Physical file name E:loglog_db.ldf may be incorrect." 'fnm_log' cannot be restored. Use Move command to identify a valid location for the file.
Even though I am using Move command to move the log file.
Then I found that if I create a drive with E: and a folder named 'log' then restore program runs alright. That is, when E:log exists then code does run smoothly. All the restore code is looking for the presence of path of the log file from where it is been backed up. In this case it is E:log folder. Once the database is restored I could kill that directory and nothing happens. Also log file has been restored on K:DB only.
Why the restore code is looking for the initial drive letter and path even though I have used Move command? Is there an issue in my code?
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
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.
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.
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....
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
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.
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.
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?
We have received a backup of a database from an external company for a project that we are taking over. We have attempted to restore the database and it fails giving us the error: "Internal consistency error occurred."
We have run the restore verifyonly command with the results: "The backup set is valid." However, I don't have confidence that the verify is telling the complete story.
It appears that the database is restored and the overall restore is failing on the transaction log file.
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.
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!
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?
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.
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?
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.
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.
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?
