Mssqlserver database attaching failure solution sharing

During our development, we often back up the database, but sometimes the database will not be attached due to false reasons. Let's take a look at a friend's solution.

During our development, we often back up the database, but sometimes the database will not be attached due to false reasons. Let's take a look at a friend's solution.

Error 15105: I found some solutions from the Internet, which generally refer to insufficient file permissions,

Of course, you must have the permission to attach the database to perform operations.

Solution 1: grant the Full Control permission to the MDF file. If you do not know which user can go to the configuration center of SQL Server

However, I cannot use the above method.

Solution 2: Try another user. My original database is logged on with sa. I tried to log on with sa and the append was successful !~

If you log on to the server using sa repeatedly but fail to log on, you may not have the permission.

You can set the permission in the logon user option to Allow logon.

You can set the Authentication Mode on the server. You need to select the hybrid logon mode.

In addition, you can try the above method to attach a database with a lower version to a later version.

In fact, I personally prefer to generate scripts to export structures and data.

Execute sp_attach_db or sp_attach_single_file_db in QA. The following message is displayed: Error 1813: failed to open the new database 'dbname'. create database will be terminated. Device activation error. The physical file name 'd: \ SQL server \ mssql \ data \ dbname _ log. ldf' may be incorrect!
Follow these steps:
1. Create a database with the same name
2. Stop the sqlserver service again (do not detach the database)
3. overwrite the new database with the data file of the original database.
4. Restart the sqlserver service.
5. When you open the Enterprise Manager, there will be a doubt. Ignore it and execute the following statement (note that you must modify the database name)
6. After completion, you can access the data in the database. At this time, the database itself generally has a problem. The solution is: use the database script to create a new database, and then import the data through DTS.
The Code is as follows:
Use master go sp_configure 'Allow updates', 1 reconfigure with override go update sysdatabases set status = 32768 where name = 'questionable database name' go sp_dbobtion 'questionable database name ', 'Single user', 'true' go dbcc checkdb ('questionable database name ') go update sysdatabases set status = 28 where name = 'questionable database name' go sp_configure 'Allow updates', 0 reconfigure with override go sp_dboption 'questionable database name ', 'Single user', 'false' go


How to solve the 2000 error when an SQL Server 1813 attached database is attached

An error occurred today.

Database xxzx_discuz, because a large number of logs are generated, the log file 'xxzx _ discuz_log.ldf' occupies all the disk space, making the website unable to use the database normally.

After the xxzx_discuz database is detached, you can directly Delete 'xxzx _ discuz_log.ldf' (because the file is too large to be stored in the recycle bin). There are two prompts when you re-attach the file to SQL Server:
Bytes ------------------------------------------------------------------------------------------------------------------------------
SQL Server Enterprise Manager
---------------------------
The file name specified for the log file is incorrect.
A new log file is created.
Do you want to continue?
---------------------------
Yes (Y) No (N)
Bytes ------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 1813: the new database 'xxzx _ discuz' cannot be opened '. Create database will be terminated.
Device activation error. The physical file name's: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ xxzx_discuz_Log.LDF 'may be incorrect.
---------------------------
OK
Bytes ------------------------------------------------------------------------------------------------------------------------------

Therefore, the database cannot be normally attached to SQL Server 2000. Under normal circumstances, this operation is normal. SQL Server will re-generate the log file, but due to the lack of disk space, the log file is incomplete, causing the append to fail.

The solution is as follows (obtained from the Internet ):

0. Back up the data file 'xxzx _ discuz_Log.MDF'
1. Create a database named 'xxzx _ discuz'
2. Stop the sqlserver service again (do not detach the database)
3. Use the data file 'xxzx _ discuz_Log.MDF 'of the original database to overwrite the new database.

4. Restart the sqlserver service.
5. When you open the Enterprise Manager, there will be doubts. Ignore it first and execute the following statement (note that you must modify the database name). 6. After the statement is complete, you can access the data in the database. At this time, the database itself generally has a problem. Solution: use the database script to create a new database, and then import the data through DTS. SQL code
The Code is as follows:
Use master
Go
Sp_configure 'Allow updates', 1 reconfigure with override
Go
Update sysdatabases set status = 32768 where name = 'suspicious database name'
Go
Sp_dboption 'questionable database name', 'single user', 'true'
Go
Dbcc checkdb ('questionable database name ')
Go
Update sysdatabases set status = 28 where name = 'questionable database name'
Go
Sp_configure 'Allow updates', 0 reconfigure with override
Go
Sp_dboption 'questionable database name', 'single user', 'false'
Go

Pay special attention to the description in the last step. "At this time, there are still some problems with the database itself. The solution is to use the database script to create a new database, then, use DTS to import the data. "reference article: how to solve the 1813 error when attaching a database to SQL Server