Sql server error 927 - Исправление ошибок и поиск оптимальных решений проблем

Remove From My Forums

Question
i have a problem with sharepoint database in SQL 2005.

status of tow database in sql studio mamagment are restoring…

how can i stop that? (change status to normal)

AND SO :

WHEN I GET A PROPERTISE OF THOSE DATABASE THIS ERROR APEAR:

database cannot be openned. it is in middle of restore. microsoft SQL server Error:927

Answers

I SOLVE IT.

I RESTORED AGAIN AND SUCCESSFULL AND COMPELETED.
- Marked as answer by
  Xiao-Min Tan – MSFT
  Monday, June 15, 2009 10:18 AM

All replies

I SOLVE IT.

I RESTORED AGAIN AND SUCCESSFULL AND COMPELETED.
- Marked as answer by
  Xiao-Min Tan – MSFT
  Monday, June 15, 2009 10:18 AM
Jus as an FYI, there was no need to do the restore again. The probles was that the restore you did, you specified NORECOVERY. To get the database out of this state, you would just executin something like:

RESTORE DATABASE dbname WITH RECOVERY

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://sqlblog.com/blogs/tibor_karaszi
I got the same problema related from ABBSA8786,

And I can’t retore my database…. executing RESTORE DATABASE dbname WITH RECOVERY…

any thing else I can do to solve it ????
Do you get an error from executing that command? If so, please post — we need somethign work with with. Also please let us know some background (what happened, what you did etc).

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://sqlblog.com/blogs/tibor_karaszi
TiborK

I got the same thing. What I did was an initial mistake and went like this

had a db called «A», on Server-1
asked to take a db called «Z» on Server-2, back it up, restore it to Server-1

DUMB thing I did was NOT create the new DB first on Server-1. I just restored it overtop another db, in this case «A» on Server-1.
Realizing my mistake, I immediately, went to backups, restored to a point in time recovery on db «A», Server-1 and got the ERROR 927 !!

NOW, Not sure how to resolve the ERROR 927:

any help, please,
So you by mistale overwrote A on server 1 with a backup of Z from server 2? And you took an old backup of A and restored it on top if yout database A (possibly several restores including log restore), and the database A is now in restoring state? If so,

RESTORE DATABAS A WITH RECOVERY

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://sqlblog.com/blogs/tibor_karaszi
Hi Tibor,

I am getting same error but here database is set to standby mode(logshipped), suggestion please..

Error:

database cannot be openned. it is in middle of restore. microsoft
SQL server Error:927

TARAK
Hi Tibor,

I am getting same error but here database is set to standby mode(logshipped), suggestion please..

Error:

database cannot be openned. it is in middle of
restore. microsoft SQL server Error:927

TARAK

Hi Tarak,
Did the restore complete? If there are no further backups to be restored then try below command
```
RESTORE DATABASE <name_here> WITH RECOVERY
```
Balmukund Lakhani | Please mark solved if I’ve answered your question, vote for it as helpful to help other users find a solution quicker

———————————————————————————
This posting is provided «AS IS» with no warranties, and confers no rights.

———————————————————————————
My Blog |
Team Blog | @Twitter

Author: SQL Server 2012 AlwaysOn —
Paperback, Kindle
Dear Friend ,

I will face the same issue so kindly provide SQL server mirroring steps as soon as possible..
- Edited by
  Jitu Parihar
  Thursday, February 26, 2015 8:01 AM

Источник

Mirror — Configuring Endpoints

Error 927 - Database Cannot Be Opened While Mirroring Configuration - SQL Server

Mirror — DB Properties

Error 927 - Database Cannot Be Opened While Mirroring Configuration - SQL Server

Mirror — Error 927

Database dbname cannot be opened. It is in the middle of a restore. (Microsot SQL Server, Error:927)

The error comes when we click on Start Mirroring.

Error 927 - Database Cannot Be Opened While Mirroring Configuration - SQL Server

Steps which you will have followed to get to this point:

Connect the principal server
Take the latest full backup of the database for which mirroring is required
Also take the latest transaction backup of this database
Connect the mirror server (node)
Restore the full and transaction backup on mirror server with «Restore with norecovery» option
After this you come to principal server
In SSMS (SQL server management studio): Connect the instance
In Object explorer, expand the database list
Right click the database (for which we need to configure the mirroring), select the option «Mirror»
On this window, click on configure security
Here you will get the option to configure the witness server, if you have the witness server then click «yes» or if you don’t want to configure the security for witness server click «No» then click «Next»
Here you will see the principal server intance window, Listener port number and Endpoint name , then «Next»
Here you will see the Mirror server instance window, click «Connect». Select the mirror server instance then click «Connect»
Automatically mirror listener port and endpoint will be filled, click «Next»
Here you will see the service account window, no need to fill this, click «Next»
Here you will see the complete the wizard window, click «Finish», then «Next»
Then click «Start Mirroring»
And here you will get the error: 927.

Actually this is not an error, this msg tell us that the database, for which you want to configure the mirror, is not fully restored; i.e. it’s in the middle of the restore.

First Solution

For this we need to restore the latest transaction log backup on mirror server/mirror node. Then click on start mirroring again at principal node, and it will work.

Second Solution

If we have restored the full backup and the latest log backup as well and we are still getting error 927, then we need to execute the below-mentioned command as directed:

On Mirror Server run this

ALTER DATABASE <Database_Name> SET PARTNER = ‘TCP://<Principal Server>:5022’

Exp. : ALTER DATABASE Test SET PARTNER = ‘TCP://Testserver_Principal:5022’

On Principal Server run this

ALTER DATABASE <Database_Name> SET PARTNER = ‘TCP://<Mirror Server>:5022’

Exp. ALTER DATABASE Test SET PARTNER = ‘TCP://Testserver_Mirror:5022’

After execution of the above commands on respective mirror and principal server, mirroring will be configured successfully.

Источник

SQL Server Error : 927 Details

SQL Server Error: 927
Severity: 14
Event Logged or not: No
Description:
Database ‘%.*ls’ cannot be opened. It is in the middle of a restore.
Severity 14 Description:
Indicates security-related errors, such as permission denied.

Steps to recreate or create this error

Create or select Database on primary for mirroring.
Took full backup and log backup.
Restored full and log on secondary in NORECOVERY mode
Tried configuring mirror from primary.
Then click “Start Mirroring”
And here volla…. you may will get the error: 927.

Actually this is not an error, this msg tell us that the database, for which you want to configure the mirror, is not fully restored and it is in the middle of the restore.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master Go xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

1. For this we need to restore the latest transaction log backup on mirror server/mirror node. Then click on start mirroring again at principal node, and it will work.

2. If we have restored the full backup and the latest log backup as well and we are still getting error 927, then we need to execute the command

On Mirror Server we need to run the SQL below

ALTER DATABASE <Database_Name> SET PARTNER = ‘TCP://<Principal Server>:5022’

Exp. : ALTER DATABASE TestDB SET PARTNER = ‘TCP://Testserver_Principal:5022’

On Principal Server we need to run the SQL below

ALTER DATABASE <Database_Name> SET PARTNER = ‘TCP://<Mirror Server>:5022’

Exp. ALTER DATABASE TestDB SET PARTNER = ‘TCP://Testserver_Mirror:5022’

Now, after executing of the above SQL commands on mirror and principal server, mirroring will be configured successfully.

Additional information:

Errors which can be encountered are microsoft sql server error 927 restoring is for mirroring database not ready for starting the mirror configuration.
This error can occur in sql server 2019 mirroring error 927 .

In sql server database mirroring error 927 is very common and can be quickly resolved with two options given above.
ms sql server error 927 or error 927 in sql server is one of simple error encountered while configuring sql mirroring.

SQL Server Error Code and solution summary

SQL Server Error: 927
Severity: 14
Event Logged or not: No
Description:
Database ‘%.*ls’ cannot be opened. It is in the middle of a restore.

Solutions here would be

1. For this we need to restore the latest transaction log backup on mirror server/mirror node. Then click on start mirroring again at principal node, and it will work.

2. If we have restored the full backup and the latest log backup as well and we are still getting error 927, then we need to execute the command

On Mirror Server we need to run the SQL below

ALTER DATABASE <Database_Name> SET PARTNER = ‘TCP://<Principal Server>:5022’

Exp. : ALTER DATABASE TestDB SET PARTNER = ‘TCP://Testserver_Principal:5022’

On Principal Server we need to run the SQL below

ALTER DATABASE <Database_Name> SET PARTNER = ‘TCP://<Mirror Server>:5022’

Exp. ALTER DATABASE TestDB SET PARTNER = ‘TCP://Testserver_Mirror:5022’

Now start mirroring and it will work.

Источник

I came across a bug while testing the setup of Database Mirroring on SQL Server 2016 Standard Edition with SP1 and using the newest version of SQL Server SQL Server Management Studio: v17.8.1

Firstly, the preparation went fine.

Create the database on the Principal and perform both a Full and Transaction Log Backup of the new database.

CREATE DATABASE db1; BACKUP DATABASE db1 TO DISK = 'C:SQLBackupsdb1.bak'; BACKUP LOG db1 TO DISK = 'C:SQLBackupsdb1.trn';

Next restore the backups on the Mirror.

RESTORE DATABASE db1 FROM DISK='\SQL2K16SEP1SQLBackups$db1.bak' WITH NORECOVERY; RESTORE LOG db1 FROM DISK='\SQL2K16SEP1SQLBackups$db1.trn' WITH NORECOVERY;

The firewall Inbound Rules for both SQL / Mirroring were added on the Principal and Mirror.

Good so all the prep work is done, lets proceed to setup Mirroring using the Wizard on Principal.

Go through the Configure Security steps.

Bang you get the following 927 error:
Database ‘db1’ cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

So, what is going on here?

Looking at Profiler during the setup we see that the GUI issues a use [db1] on the Mirror instance and of course that is going to end badly as the database is not accessible and hence you get the error that ‘db1’ cannot be opened. Microsoft needs to update the GUI to use [master].

So how do you resolve this?

Well you need to complete the Database Mirroring setup manually and the steps are:

1.) On the Mirror use the following to set the principal as a partner:
ALTER DATABASE [db1] SET PARTNER = 'TCP://SQL2K16SEP1:5022';

2.) Then finally on the Principal use the following to set the mirror as a partner:

ALTER DATABASE [db1] SET PARTNER = 'TCP://SQL2K16SEDR1:5022';

Database Mirroring should now be working.

I’m sure Microsoft will address this issue in a future release of SSMS, but until then the above should hopefully assist you until then.

Источник

Microsoft SQL Server allows the user to back up their data to protect data from damage and loss, and create a .bak file. SQL users can easily restore the database from the backup file (BAK) using the SSMS or T-SQL command as needed. Sometimes when the restoration is complete and users try to access their SQL data, the following error is displayed on the screen: SQL Database cannot be Opened. It is in the middle of a restore.

This error occurs because the administrator used NORECOVERY mode for restoration and it does not allow the use of the database. Now you should use WITH RECOVERY MODE to restore the database so that you can easily access the data. Before proceeding with the process to fix this error, first, read the user query that has encountered the same problem.

Quick Solution: If you don’t have backup file and want to fix SQL database cannot be opened error instantly, download the professional SysTools SQL Recovery tool. That way, you can easily fix and recover SQL Server Database errors.

Download Now Purchase Now

Real User Query

Hello, I just restored a database without recovery (which I later want to set to read-only). The restore operation resulted in “successfully restored,” but I can not access this database at all, because the error message “Database cannot be opened, it is in the middle of a restore”. It has been in this state for a while. How do I get out of this state?

Fix SQL Database Cannot Be Opened. It is In The Middle of a Restore Issue

To rectify this error, we need to use the WITH RECOVERY option. Follow these steps to do the same:

T-SQL
Script For Restoring Database WITH RECOVERY

RESTORE DATABASE Databasename FROM DISK = ‘C:databasename.BAK’

WITH RECOVERY

GO

Recover
a Database from the ‘Restoring’ State

If the database is in the restoring state and unavailable to users,
run the command to make it accessible to users.

RESTORE DATABASE Databasename WITH RECOVERY

GO

Restore
Multiple Backups using WITH RECOVERY option

The user can use the NORECOVERY option to restore the database in
case if the user has multiple backups except in the last. But for the
last backup, the user must use WITH RECOVERY option to restore all
transaction logs and put the database online.

RESTORE DATABASE databasename FROM DISK = ‘C:Databasename.BAK’

WITH NORECOVERY

GO

RESTORE LOG databasename FROM DISK = ‘C:Databasename.TRN’

WITH RECOVERY

GO

Related Solutions:

How to Fix SQL Server Error 926 Manually?

Solution to Resolve SQL Server Error 945 Instantly

Restore Database Using SSMS

You can also run the SQL Server database recovery process with SQL Server Management Studio. Follow these steps for the same:

Step 1. Start SSMS and select the Databases in the menu list. Next, right-click the Databases and then click Restore Database option

Step 2. Select the “From Device” option and click the Browse button

Step 3. In the “Specify Backup Wizard”, select the .bak file and click OK

Step 4. In the Destination for the restore option, select the database where you want to save the data.

Step 5. In the Restore options, enable the option ‘Overwrite the existing database (WITH REPLACE).

Step 6. In the Recovery state, select the option ‘Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)’

Step
7.
Click
OK to
start the process. After
the restore operation completes, perform the same operation for each
backup (.bak) file that you want to restore.

SQL Database Cannot Be Opened. It is In The Middle of a Restore Issue Not Resolved?

If any of these methods do not work to fix this error, you must use the SQL database recovery software. This is a professional solution to resolve all SQL Server database errors without data modification. It supports any version of SQL Server such as 2019, 2017, 2016, 2014, 2012, 2008, and so on. With this tool, you can easily repair damaged MDF and NDF files and restore the SQL database with just a few clicks. The software is also helpful to recover deleted records from the table.

Tip: If your backup file (.bak) is corrupted, you need to recover it first because there is no way to restore corrupt backup files. You can do this using the SQL Backup Recovery Tool.

Additional Features of the Recovery Tool

The software helps recover SQL MDF and NDF files that are corrupted or inaccessible. It offers multiple features and some of them are-

Recover Corrupt MDF file of any SQL Server version.
Preview and repair all database objects like tables, rules, functions, etc.
Supports ASCII and UNICODE data type.
Option to restore the recovered data directly to the live SQL Server Database.
Able to restore the deleted SQL Server table’s records.

Final Words

Here we have discussed how to deal with SQL Database cannot be opened. It is in the middle of a restore issue. We explained the manual method with SSMS and T-SQL command to resolve this error. If you can not fix this problem manually, you can use the automated solution.

Frequently Asked Questions

How can I solve error 927, SQL Database Cannot Be Opened. It is in the Middle of a Restore?

You can manually rectify this error and restore the database using the WITH RECOVERY MODE.

Can you recover SQL database without backup?

Generally it is not possible to do it manually. To do this, you need automated software such as the SQL Recovery Tool. This way you can restore the database without backup.

Can I restore SQL database with only MDF file?

Using the mentioned automated solution, you can easily restore only MDF file in SQL Server.

Источник

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Question

Answers

All replies

Mirror — Configuring Endpoints

Mirror — DB Properties

Mirror — Error 927

SQL Server Error : 927 Details

Reading sql server error log location from SQL Query

Solution for Resolving the Error

Additional information:

SQL Server Error Code and solution summary

Fix SQL Database Cannot Be Opened. It is In The Middle of a Restore Issue

SQL Database Cannot Be Opened. It is In The Middle of a Restore Issue Not Resolved?

Additional Features of the Recovery Tool

Final Words

Frequently Asked Questions