Database Not In Full Recovery Mode On Both Partners Error:1465
Nov 15, 2006
I have two instances (Source and Destination) on the same server, i did a full backup of a database on the source server, i did a restore on the destination using the NORECOVERY mode, now the database on the destination as green arrow saying "restoring". From the source database i set up the mirroring, i'm using port 5022 on source and 5023 on destination, everything seems to connect fine but when i start mirroring i get the error message "Database mirroring cannot be enabled because the database is not full recovery error: 1465" but when i look at the property of the db on the source the recovery model shows full as so the destination?
What are the pros and cons of putting the system databases in full recovery mode I am asked by my manager to keep everything simple and uniform.We have all the user databases in Full recovery mode and he wants all the system databases as well to be full recovery mode. I would like to get the points where in i can argue that, there is no point in having the system databases in full recovery mode.Unfortunately i dont see anything related to this in BOL or internet.
When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
1st question I have to ask is: Is Emergency Mode the same as Bypass Recovery Mode?
2nd: I have a SQL2005 Database that was suspect this morning. I put the database in Emergency Mode: alter database {dbname} set emergency.
I did a dbcc checkdb on the database and found some corrupt indexes. I attempted to do a dbreindex on the table and got the error: Could not run BEGIN TRANSACTION in database '{dbname}' because the database is in bypass recovery mode.
How can I put the database in single_user mode and to a point where I can reindex the 1 table that is causing the problems. I would even be willing to drop the indexes and re-create.
Any suggestions or help would be appreciated! Thanks in advance.
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.
The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).
The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.
I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.
I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)
I tried running a checkpoint, but that did not allow any resize or shrink to work.
I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.
The following is what I was using for the transactions to get the log used.
BEGIN TRAN select a.* into testtable from sysobjects a, sysobjects b, sysobjects c ROLLBACK TRAN
Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.
I had my database in 6.5 which i upgraded to 7.0 using SQL Server upgrade wizard.Then I created full text catalog. When I say incremental population It gives me warning that You can create full text indexes but can not execute queries against it as the database is still in SQL server 6.5 mode.What is the reason behind this?
We are trying to restore from a backup (.bak) of a sql 2005 database with full text catologs to sql 2012.
We are getting errors saying that the backup file context are different from what we are trying to restore to, even if we try to create a new database from the backup.The new database is just a test database. The source is a production database with full cataologs.
The normal restore from a bak file does not seem to work.
We dont need the full text catalogs in the new database, but cannot change settings in the source production database.
Is there a way to take a backup of the sql 2005 database without the full text cataologs being included ? , without changing setting in the source database beforehand ?
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
I have un problem with the mirroring, when I set the IP of the partners, I get an error saying that the server will not exists or it's impossible to join it. I try to make a mirroring without witness between two servers.
request on first : ALTER DATABASE [panorama2] SET PARTNER = N'TCP://192.168.1.71:5050'
I get :
Msg1418, Niveau16, tat1, Ligne1L'adresse reseau du serveur "TCP://192.168.1.71:5050" est impossible atteindre ou elle n'existe pas. Verifiez le nom de l'adresse reseau et executez la commande de nouveau.
And it's the same problem with the second server.
I work with the sql server evaluation (I put the parameter T 1400 for). Endpoints are created (I get them with a select on systcp_endpoints) : Mirroring 65541 1 2 TCP 4 DATABASE_MIRRORING 0 STARTED 0 5050 0 NULL
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
On SQL 2000, if I have a large transaction log for my database and I manage to fill it with a single large transaction but it is not set to autogrow, what happens?
We have 300+ databases on one sinlge server (SQL 2005). If I need to change recovery mode from full to simple for all of them, is there any way to do so? Please advice.
I have a db running under simple recovery mode. I am really pressed at disk space, and we have a long running script that keeps failing with "no space left on the device" error. I noticed the log file is very large. I plan to set the max log file size but at the same time I want to know that the database is in the log-truncate mode - perhaps from the database itself. Is there any metadata that could tell me this?
I have several databases that perform daily backups. Ever since I installed SP2, the backup jobs are failing. The logs state that it's because the database recovery mode cannot be SIMPLE, but need to be either FULL or Bulk-Logged.
Can anyone tell me if this is true, and that I do have to change my recovery mode?
Here's the error:
NEW COMPONENT OUTPUT Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042 Report was generated on "ICIS-SQL-SERVER". Maintenance Plan: DB Backup Duration: 00:08:05 Status: Warning: One or more tasks failed.. Details: Back Up Database (Transaction Log) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-03-10T05:08:09. Task end: 2007-03-10T05:08:09. Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db_SamirTesting] TO DISK = N'D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_SamirTesting_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:mssqlackuphl7 db_SamirTestinghl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [dg_efilm_153] TO DISK = N''D:mssqlackupdg_efilm_153dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [hl7 db] TO DISK = N''D:mssqlackuphl7 dbhl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
Back Up Database (Transaction Log) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-03-10T05:08:09. Task end: 2007-03-10T05:08:09. Failed:(-1073548784) Executing the query "BACKUP LOG [dg_efilm_153] TO DISK = N'D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'dg_efilm_153_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:mssqlackuphl7 db_SamirTestinghl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [dg_efilm_153] TO DISK = N''D:mssqlackupdg_efilm_153dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [hl7 db] TO DISK = N''D:mssqlackuphl7 dbhl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
Back Up Database (Transaction Log) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-03-10T05:08:09. Task end: 2007-03-10T05:08:09. Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:mssqlackuphl7 db_SamirTestinghl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [dg_efilm_153] TO DISK = N''D:mssqlackupdg_efilm_153dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [hl7 db] TO DISK = N''D:mssqlackuphl7 dbhl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
Back Up Database (Transaction Log) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-03-10T05:08:08. Task end: 2007-03-10T05:08:09. Failed:(0) Database 'hl7 db_SamirTesting' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Transaction Log) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-03-10T05:08:08. Task end: 2007-03-10T05:08:09. Failed:(0) Database 'dg_efilm_153' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Transaction Log) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2007-03-10T05:08:08. Task end: 2007-03-10T05:08:09. Failed:(0) Database 'hl7 db' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Back Up Database (Full) (ICIS-SQL-SERVER) Backup Database on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All databases Type: Full Append existing Task start: 2007-03-10T05:00:06. Task end: 2007-03-10T05:08:08. Success Command:EXECUTE master.dbo.xp_create_subdir N''D:mssqlackupmaster'' GO EXECUTE master.dbo.xp_create_subdir N''D:mssqlackupmodel'' GO EXECUTE master.dbo.xp_create_subdir N''D:mssqlackupmsdb'' GO EXECUTE master.dbo.xp_create_subdir N''D:mssqlackuphl7 db_SamirTesting'' GO EXECUTE master.dbo.xp_create_subdir N''D:mssqlackupdg_efilm_153'' GO EXECUTE master.dbo.xp_create_subdir N''D:mssqlackuphl7 db'' GO BACKUP DATABASE [master] TO DISK = N''D:mssqlackupmastermaster_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP DATABASE [model] TO DISK = N''D:mssqlackupmodelmodel_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP DATABASE [msdb] TO DISK = N''D:mssqlackupmsdbmsdb_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP DATABASE [hl7 db_SamirTesting] TO DISK = N''D:mssqlackuphl7 db_SamirTestinghl7 db_SamirTesting_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP DATABASE [dg_efilm_153] TO DISK = N''D:mssqlackupdg_efilm_153dg_efilm_153_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10 GO BACKUP DATABASE [hl7 db] TO DISK = N''D:mssqlackuphl7 dbhl7 db_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
I am applying hourly differential backup to the backup server from production with the following command. This command makes the database on standby server into read only mode.
RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup' WITH MOVE 'arsystem' TO 'd:ardataarsystem.mdf' , MOVE 'arsystem_log' TO 'D:ARLOGARsystem' , STANDBY = 'E:SQL backup from productionSQL daily diff back up'
Now I want to run a command which will put the database in write mode. I have created a job which would make the datbase Write mode. This job runs successfully sometimes and fails sometimes. I need to ensure that the job always succeeds. When it fails, how do I troubleshoot and what is the possible fix?
Thanks in advance.
The error message is
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.
The steps for the job are as follows with the failing step highlighted in bold.
copy /y "\172.31.9.12Remedy BackupackupSQL backupsql_full_backup" "E:SQL backup from productionsql_full_backup"
copy /y "\172.31.9.12Remedy BackupackupSQL backupSQL daily diff back up" "E:SQL backup from productionSQL daily diff back up"
xp_cmdshell 'net stop "bmc remedy action request system server"'
exec rp_kill_db_processes 'ARSYSTEM'
RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionsql_full_backup'
WITH
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
NORECOVERY
Failing step
RESTORE DATABASE ARSYSTEM
FROM DISK = 'E:SQL backup from productionSQL daily diff back up'
WITH
MOVE 'arsystem' TO 'd:ardataarsystem.mdf' ,
MOVE 'arsystem_log' TO 'D:ARLOGARsystem' ,
RECOVERY
xp_cmdshell 'del /f "E:SQL backup from productionsql_full_backup"'
xp_cmdshell 'del /f "E:SQL backup from productionsql daily diff back up"'
xp_cmdshell 'net start "bmc remedy action request system server"'
I have scheduled the following hourly diffential restore job too which never fails.
RESTORE DATABASE ARSYSTEM FROM DISK = 'E:SQL backup from productionsql_full_backup' WITH MOVE 'arsystem' TO 'd:ardataarsystem.mdf' , MOVE 'arsystem_log' TO 'D:ARLOGARsystem' , STANDBY = 'E:SQL backup from productionSQL daily diff back up' EXEC MASTER..XP_CMDSHELL 'del /f "E:SQL backup from productionSQL daily diff back up"'
What is the impact of changing the recovery mode of a database from Full to simple? The client I am at has set their database to full recovery mode, set their log files to grow automatically. But I don't think they have ever done a backup of their transaction log (it has grown to over 19Gig, where the data portion of the database is only around 400M).
What is the impact of truncating the transaction log now? After truncating it, i would like to shrink the file to a managable number and change the recovery mode to simple (they don't need transaction log backups)
By default, distribution database is set to simple recovery mode. Why ? Should this be changed to full ? how often should transaction logs be taken ? What is the recommendation ?
hi, we have a SQL Server 2000 database which we set to 'SIMPLE' recovery mode at 6pm (due to nightly large data loads). We revert back to 'FULL' recovery mode at 6am.
My understanding was that in 'SIMPLE' recovery mode, the transaction log would not grow because it would automatically be truncated after a checkpoint. However this is not the case. I thought perhaps it could be due to a long running uncomitted transaction, but when I ran 'dbcc opentran', the oldest running transactions doesn't last for more than a couple minutes. I manually run a 'checkpoint' command as well in the hope of forcing the transaction log truncation. I repeat this a couple of times to no avail. When I run 'dbcc sqlperf(logspace)' , I can still see the transaction log growing.
It is not until I run 'backup log db with truncate_only' that the transaction log gets truncated. I do not understand, why the transaction log does not get automatically truncated in SIMPLE recovery mode?
My understanding is that the log file is not supposed to grow if the database is under simple recovery mode.I am in a situation where the log grows if do any inserts that involve millions of rows.How do i make sure that it does not grow?
I have a small, ~10GB SQL 2008 R2 database, that was setup with simple recovery. We do full backup each night at midnight when no one is using the database. Is there any problems with doing differential backups during the day when users may be writing to the database? Could I even do hourly differential backups while users are using the database? I'm conflicted about switching over to full recovery mode and using transaction logs to have the ability restore data between backups. If I can do a couple daily differential backups while users are using the database during the day, in addition to our nightly full backups, than I live with simple recovery mode.
Wondering whether I can have following included in a script file:
1. set the current single user db from Full to Simple recovery mode. 2. Checkpoint and shrink the transaction log file (release all unused space) 3. set the current single user db from Simple recovery to Full recover mode.
