Solution for restoring SQL Server database after backup

Sometimes it is a full backup. When restoring, the database files will not be restored from time to time. solution:
You can directly copy database files, xxx. mdf and xxx. ldf (you need to stop the SQL Server service before the actual replication process)

Use the sp_attach_db stored procedure.

Example
In the following example, two files in pubs are appended 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 indicates the meaning of Unicode, just as there are varchar and nvarchar types, a Unicode Character occupies two bytes. N 'is mainly used to force the system to interpret each character in the dual-byte system environment, otherwise if your database. if the mdf file is a Chinese name without n', the name may become garbled after the database is loaded, because the system processes characters in single byte!


........................................ ....................


If SQL SERVER cannot be started, follow these steps:
1. reinstall SQL SERVER (be sure to keep the database files of the original database, but not log files)
2. Use sp_attach_db in SQL Analysis to add the database to the server

Sp_attach_db usage:
Sp_attach_db
Attach the database to the server.

Syntax
Sp_attach_db [@ dbname =] 'dbname'
, [@ Filename1 =] 'filename _ n' [,... 16]

Parameters
[@ Dbname =] 'dbname'

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

[@ Filename1 =] 'filename _ N'

The physical name of the database file, including the path. The data type of filename_n is nvarchar (260). The default value is NULL. A maximum of 16 file names can be specified. The parameter name starts with @ filename1 and increments to @ filename16. The file name list must contain at least the primary file. The primary file contains the system tables pointing to other files in the database. The list must also contain all the files that are moved after the database is detached.

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

Result set
None

Note
Execute sp_attach_db only for databases that previously separated from the database server using the explicit sp_detach_db operation. If you must specify more than 16 files, use the create database with the for attach clause.

If the server to which the database is appended is not the server from which the database is detached and the detached database is enabled for replication, run sp_removedbreplication to delete the replication from the database.

Permission
Only members of sysadmin and dbcreator fixed server roles can perform this process.