SQL 7.0 Restore Fails- Says Use "with No Recovery " Option
Aug 1, 2001
Hello,
I have a file backup of a database from machine 1. I create an empty database with the same name on machine 2 and copied the backup file to machine 2. When I run a restore database, I get "Preceeding restore operation did not specify WITH NORECOVERY OR WITH STANDBY.Restart tehe restore sequence, specify the options for all but the final step. Restore failed"
Database : SQL 7.0 Trying to restore from the Enterprise manager
ANy clues as to what might be wrong.
Thanks, MMS
View 3 Replies
ADVERTISEMENT
Jun 5, 2015
I am set up log shipping as backup and copy every one hour and restore every ( disconnect users and stand by) one hour on servers in same domain. Set-up completes successfully.Copy job works as expected. but the restore job fails intermittently to restore the .trn file saying ' could not apply log, data invalid' .
when i delete and copy the files for which restore failed manually using CTRL+C and CTRL+V across network and re run the restore job it does restore successfully . Through out the day i get about 5-6 problematic .trn files on secondary server which are copied from primary server and when restore fails i have to manually delete the log file which had problem restoring and then copy that file again using CTRL+C and CTRL+V then run the restore and it gets restored successfully.when running restore filelistonly on the logfile in issue on secondary server it gives below error
Cmd:
RESTORE verifyonly
FROM disk='G:xxxx_Copyxxx_20150604010501.trn'
Error:
Msg 3203, Level 16, State 1, Line 2
Read on "G:xxxxxxx_20150604010501.trn" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.
and when i run restore filelistonly using netwok path then is seem s good and i can restore the logs as well
CMD:
RESTORE verifyonly
from disk='1.2.3.4xxx_Backupsxxx_20150604010501.trn'
Output: The backup set on file 1 is valid.I can assure there is no corruption on the log back up, no lsn mismatch, no log files missing.Same version and edition on both servers. Both servers are SQL server 2012 Enterprise edition on window server 2012
View 15 Replies
View Related
May 26, 2008
Hi,
I run 2 steps viz with NORECOVERY and with RECOVERY in sequence as SQP jobs. The 2nd step fails some times. When it starts failing, I run full backup of the production server again and the 2nd step starts succeding. Unless I run the full backup the 2nd step keeps on failing.
What are the conditions which generate for [SQLSTATE 42000] (Error 3013) and how to tackle this problem.?
The 2 steps are :-
1.RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH
Replace ,
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
NORECOVERY
GO
2.RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionSQL daily diff back up'
WITH
Recovery
The status of the 2 steps is :
1 Executed as user: BPOTESTREMEDY
emedybackup. Processed 104480 pages for database 'ARSYSTEM', file 'ARSystem' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'ARSYSTEM', file 'ARSystem_log' on file 1. [SQLSTATE 01000] (Message 4035) RESTORE DATABASE successfully processed 104481 pages in 49.452 seconds (17.307 MB/sec). [SQLSTATE 01000] (Message 3014)
2. Cannot apply the backup on device 'E:SQL backup from productionSQL daily diff back up' to database 'ARSYSTEM'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
View 3 Replies
View Related
May 5, 2015
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
View 6 Replies
View Related
Jan 23, 2003
Is there a Stored Procedure or something else that will tell me if a DB is set to Full or Simple recovery?
Thanks,
Ken Nicholson
View 5 Replies
View Related
Mar 31, 2008
Hello,
Do I need to restart service after I set recovery option
"ALTER DATABASE SET RECOVERY XXXX"
can i specify RECOVERY OPTION in CREATE DATABASE?
Thanks
View 4 Replies
View Related
Mar 15, 2002
Hi,
How can I use the NORECOVERY or RECOVERY option to restore the database?
THANKS
View 1 Replies
View Related
May 12, 2015
I'm trying to execute backup job on secondary replica on clustered server(with HA) and I getting the error:execution failed see the maintenance plan and sql server agent job history logs for details.When I going to see the log, it says :configuration option 'user options' changed from 0 to 0.
View 3 Replies
View Related
Nov 1, 2005
Hi,
I am facing a problem with permissions while creating a database using script (i am using SQL Express database).
I am excuting a script using following command
osql -E -S <servername><instancename> -i <scriptpath> -o <log file name>
It is throwing the below error (copied from log file)
"1> 2> 3> 4> 5> 6> 7> Msg 1802, Level 16, State 4, Server DEV2SMSPUBTOOL, Line 3
CREATE DATABASE failed. Some file names listed could not be created.
Check related errors.
Msg 5123, Level 16, State 1, Server DEV2SMSPUBTOOL, Line 3
CREATE FILE encountered operating system error 5(Access is denied.)
while attempting to open or create the physical file 'C:Program
FilesMicrosoft Publishing ToolDATApubDB_Data.MDF'.
1> 2> 3> Msg 15010, Level 16, State 1, Server DEV2SMSPUBTOOL, Procedure sp_dboption, Line 64
The database 'pubDB' does not exist. Use sp_helpdb to show available
databases."
This problem occurs only if i give "program files" path for creating mdf and ldf files.
But i have admin previliges on the machine.
Can some doby suggest me if any other approach for creating database.
Thanks a lot for help
Krishna.s
View 1 Replies
View Related
Jul 22, 2014
What the BUFFERCOUNT option does in a RESTORE DATABASE command?
View 5 Replies
View Related
Aug 21, 2003
hello everybody
I have custom log shipping .
it run every 15 min
DB1 located on Server1
DB1 backup logs to server 2
DB2 located on server 3(300 km away from server1 and server 2)
Db2 restore logs from server 2
If server1 and server2 become unavailable
1.
Would enough login to server3
and
run
RESTORE DATABASE Db2
WITH RECOVERY
to bring db from read only to production mode up last restored log?
Thank you
Alex
View 1 Replies
View Related
Nov 15, 2006
I'm trying to RESTORE LOG with a different recovery path. Is there a way to do this? I'm getting the below error. I know I could RESTORE from the beginning, but need to RESTORE the log file to the same database schema but different database and server. What steps do I need to do?
This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
View 5 Replies
View Related
Jul 16, 2015
I have 3 synchronous AlwaysON replicas: A, B and C. A is primary, B and C are secondary and both are set to Automatic recovery. How can I understand, which of them (B or C) will become primary when A goes offline? Well, Actually my final DB system should support following configuration:
1) Normally - A is primary B and C are sync secondary.
2) if A fails, B automatically becomes primary, C remains Sync secondary.
3) if A goes online, it becomes primary again
4) C becomes primary only after A and B fail (and there still should be cluster quorum!)
As I understood, first of all i should configure quorum the following way: A-0, B-1, C-1, folder-witness-1.
The problem, again, is: I cannot understand how to configure which replica becomes primary when AG fails over.
View 3 Replies
View Related
Jan 17, 2001
Hello all,
I just restored a SQL 7 db using the Enterprise Manager GUI interface. I
selected the "Leave database nonoperational, but able to restore additional transaction logs" option. I should not have selected this option as I do not have any transaction logs to apply.
Anyway, my question is... Is there a way to mark the database as opertational without running the restore again?
Thanks,
Corey
View 2 Replies
View Related
Nov 23, 2015
SQL Server 2008 R2
I don't see a "general discussion" thread and this is the closest i think.
I just have a general question: if my backup window is from 8am to 10am, and i do a restore within that backup window, what will happen? assuming we're talking only of a single database, ACME.
I'm comparing because with Oracle RMAN, it pukes when i do a restore while the backup is going on. it would complain of unable to find some archive logs.
View 2 Replies
View Related
Sep 20, 2007
I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following
RESTORE DATABASE database_name WITH RECOVERY
but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?
View 10 Replies
View Related
Oct 18, 2007
Given the follwoing scenario: You create a snapshot of a database with full recovery model, change it's recovery model to simple, then perform several updates/modifications on the database, before you finally (due to an error) restore the database from the snapshot.
Will the log chain be broken or not? The resteore sets the recovery model back to full, but I'm still a bit "curious" about the transaction logs.
View 7 Replies
View Related
Oct 23, 2015
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. Restore the server with Oct 1 full backup with NORECOVERY option.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
View 12 Replies
View Related
Oct 21, 2015
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
View 2 Replies
View Related
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 manager 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 manager 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 8 Replies
View Related
Aug 19, 2015
We are using SQL Server 2012 for both production and development server. Currently, i have plan to transfer the full backup of SQL server database to the development server and then restore it on the development server for testing purpose, so we don't want to disturb on the production.
I have successfully backup the database from the production server and transfer to the development server. however, i encounter some difficulties when trying to do restore. my question is:
1. do i need to firstly create an empty database on the development server and then restore it into this empty database? say i create database call "Test2", then i perform a restore into this database.
2. currently, there is already an existing database being restored previously in the development server and this database is actually the older version of the backup that being restored by the previous engineer. should i remove this database first and restore a new one or both of the database can coexist as long as we put different name for the database?
View 6 Replies
View Related
Jul 9, 2015
I am setting up a 3 node cluster as part of an availability group.Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries. I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed. I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed.how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done? I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route.
View 4 Replies
View Related
Feb 24, 2000
Hi ,
While running the load cmd - 'Load database dbname1 from disk='c:empdump1.dat'
through ISQL I am getting the following error
1 transaction rolledback dbid error 7.But when I tried restoring using enterprice manager UI it is working fine.Any body knows why it is happening like this .
Thanks in advance
Jiji
View 1 Replies
View Related
Apr 22, 1999
I'm new to SQL server, so bear with me please.
I'm running NT4 SP3 SQL 6.5 SP5. I have one major database (son_db) which is being backed up (along with others) using this command:
DUMP DATABASE son_db TO DBTape WITH NOUNLOAD , STATS = 10, NOINIT , NOSKIP
The task is completing successfully each night. However, we want to test the restore of this database, so we created a test table (test_table) which resides in the son_db database, added some data, and then let the backup run.
Next day we alter some data and try to restore the single table, but consistantly get the following message after clicking on the restore button:
[SQL Server] invalid source table "test_table" specified in LOAD TABLE. Could not find table in SYSOBJECTS in dump.
After this error, the table (test_table) is now completely empty.
MS website is no help, so I would really appreciate a bit of guidance on this,
Many thanks,
Joe
MCSE (but not including SQL!)
View 2 Replies
View Related
Jul 13, 2000
Hello, I have a SERIOUS problem where I am restoring my SQL server (6.5) dump to my standby server everyday.......however, it often fails with the following msg:
'Unexpected end-of-dump while loading database, attempting to continue. Please verify database integrity. (Message 3103)'
It does also succeed, but too often fails and my standby server depends on it. The DB that gets restored is placed in single-user mode, so no other process or connection is allowed.
The backup to PROD runs at 12AM, for about 3 hours and succeeds always; the restore runs at 4:00...i've tried later times as well.
Can anyone tell me how to get near 100% success rate? What is the problem and how can I fix it? Thank you for the help.
View 3 Replies
View Related
Aug 4, 2015
I'm working on SQL 2012 Box, which is having Logshipping failed on secondary database, the secondary database was in stand by mode right now but no more restore operation performed on this database since 2 weeks! We checked in the SQL error log and found the error code 14421, severity 16, stat: 1
How to reset the logship back to normally, do I need to disable the jobs before proceed any operation!
View 7 Replies
View Related
Jul 12, 2000
To restore the Master DB, BOL says to start SQL Server in single user mode.
I do this using: sqlserver.exe -m
The server appears to startup (Recovery complete message, etc.).
Then I take care to minimize the command window. The command
window now appears on my task bar.
I start EM and navigate my way to the Restore Database menu pick.
I fill out the appropriate boxes, etc. I get a window telling me that
the restore has been started.
Very shortly I get a popup window telling me the connection has been
lost. When I examine my taskbar, I no longer see the Icon for the
command line/window. Thus it appears to me that the server
running in single user mode has gone away and this explains
the reason for the window saying that the connection has been
lost.
I then start SQL Server, per normal.
I run DBCC CHECKDB. This tells me there are 0 allocation errors
and 0 consistency errors. Several thousand rows of data present.
Thus the master DB appears to be OK.
However I am unable to restore it.
Any ideas or suggestions would be appreciated.
View 6 Replies
View Related
Jun 4, 2007
I'm trying to use ADO to restore a SQL 7 database using a backup file. Thedatabase already exists on the target computer, and is named the same as onthe source computer, and the MDF and LDF files are named the same as well.I am running the below in a stored procedure:RESTORE DATABASE DB1FROM DISK = 'c:mssql7ackupDB1.bak'WITH MOVE 'DB1' TO 'c:mssql7dataDB1.mdf',MOVE 'DB1_log' TO 'c:mssql7dataDB1_log.ldf'And I get an error message "File 'DB1' is not a database file for database'DB1' ".I also tried it without the LDF file in the expression. Same results.Thanks.
View 5 Replies
View Related
May 9, 2008
Get the following error when trying to restore from one SQL 2005 server (64-bit, standard edition) to another (32-bit, enterprise edition).
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 3 Replies
View Related
May 30, 2002
I am trying to restore a production database backup to a new location on a development workstation, and it fails with "An internal consistency error occurred, Contact Support". When I tried this on a more powerful workstation it ran without a hitch. The db is about 1.5GB, and I think the dev workstation may be running out of resources during the restore. I stopped non-essential services and ran from TSQL, and it still fails. Any suggestions other than getting a better workstation?
Thanks,
Rob
View 1 Replies
View Related
Jul 19, 2007
We are doing the following steps:-
1. Weekly Full Bckup
2.Daily Differential backup
3.hr Log back up
The following command works fine for all the Backups. it means backup file is fine.
RESTORE FILELISTONLY from DISK = 'D:BackupewDB_ full.BAK'
RESTORE HEADERONLY FROM DISK = 'D:BackupewDB_ ull.BAK'
RESTORE LABELONLY FROM DISK = 'D:BackupewTest_full.BAK'
RESTORE VERIFYONLY FROM DISK = 'D:BackupewTest_full.BAK'
Also The full back up restoration works fine:
RESTORE DATABASE Test3 FROM DISK = 'D:BackupewTest_full.BAK'
WITH MOVE 'Test2_Data' TO 'F:MSSQL2KMSSQLdataTest2Net_Data.MDF',
MOVE 'Test2_Log' TO 'F:MSSQL2KMSSQLdataTest2Net_Log.LDF',
NORECOVERY
GO
/* RESULT :
Processed 1032 pages for database 'Test3', file 'test2_Data' on file 1.
Processed 1 pages for database 'Test3', file 'test2_Log' on file 1.
RESTORE DATABASE successfully processed 1033 pages in 1.907 seconds (4.433 MB/sec).
*/
But While restoring the Differential file it throws error:
RESTORE DATABASE Test3 from DISK = 'D:Backupew est2_Diff1.bak'
WITH NORECOVERY
GO
Msg 3136, Level 16, State 0, Line 1
Cannot apply the backup on device 'D:Backupew est2_Diff1.bak' to database 'Test3'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I checked the SQL Server Log no error msg corresponding Differential backup restore.
I don't know how to proceed further.Can any one guide me how to over come this..
Thanks !
View 2 Replies
View Related
May 24, 2006
We are running SQL 2000 sp4, only one user database (SAP) the database is 63 GB.
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.
Help this has gone on for three days!!!
View 11 Replies
View Related
Feb 17, 2014
1. Created a database with a couple of tables with no data.
2. Taken a full database backup - db.bak.
3. Deleted the database.
4. Restored the database db.bak and filled the tables with some data.
5. Taken log backup - dblog.trn
BACKUP LOG <dbname> to DISK='D:Demodblog.trn' WITH NO_TRUNCATE, INIT
6. Dropped the database again.
7. Restored the database again from db.bak.
8. But when I am trying to restore log file dblog.trn on this database, i keep getting this error :
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 2 RESTORE LOG is terminating abnormally.
View 2 Replies
View Related
