Solutions that cannot be restored after a SQL Server database backup _mssql

Source: Internet
Author: User
Tags microsoft sql server mssql create database
Sometimes a full backup, when the restore time said the database file does not allow restore, the solution:
You can copy the database files directly, Xxx.mdf and XXX.LDF (you will need to stop the SQL Server service in the actual copy process)

It can be done with the sp_attach_db stored procedure.

The following example attaches two files in pubs to the current server.

EXEC sp_attach_db @dbname = N ' pubs ',
@filename1 = N ' c:\Program files\microsoft SQL Server\mssql\data\pubs.mdf ',
@filename2 = N ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf '

n represents the meaning of Unicode, as in the case of a type with varchar and nvarchar, one Unicode character is two bytes. Using n ' is a major way to force the system to interpret each character in a double-byte system environment with a Unicode standard. Otherwise, if your database. mdf file is the Chinese name without adding n ', the database load name may become garbled, because the system in a single byte processing characters caused!


If you are sure that SQL Server cannot start, follow these steps:
1. Reload SQL SERVER (note that you want to keep the database files of the original database, log files can not)
2. Add a database to a server using sp_attach_db in SQL analysis

sp_attach_db usage:
Attach the database to the server.

sp_attach_db [@dbname =] ' dbname '
, [@filename1 =] ' Filename_n ' [,... 16]

[@dbname =] ' DBName '

The name of the database to attach to the server. The name must be unique. The dbname data type is sysname and the default value is NULL.

[@filename1 =] ' Filename_n '

The physical name of the database file, including the path. The Filename_n data type is nvarchar (260) and the default value is NULL. You can specify up to 16 file names. The parameter names are incremented to the @filename16, starting with the @filename1. The file name list must include at least the primary file, which contains system tables that point to other files in the database. The list must also include all files that were moved after the database was detached.

Return code value
0 (Success) or 1 (failed)

Result set

You should only perform sp_attach_db on databases that were previously detached from the database server using explicit sp_detach_db operations. If you must specify more than 16 files, use the CREATE DATABASE with the for ATTACH clause.

If the server to which you are attaching the database is not the server from which the database is detached, and the detached database is enabled for replication, you should run sp_removedbreplication to remove replication from the database.

Only members of the sysadmin and dbcreator fixed server roles can perform this procedure.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.