SQL Server database backup and restoration (. NET Project)

In my spare time, I came to the blog Park. This is my first essay. I hope you will have a lot of support. I am most interested in databases and want to find my ideal job when I graduate, I hope to go further in the database field in my life! Next I will share my database knowledge with you. I hope you can continue to learn new technologies on this platform. I believe we can make common progress! O (distinct _ distinct) O ~

-- There are two types of backup devices (temporary devices and permanent devices). Note: The default backup type is full backup.
-- First:
Backup database company to disk = 'd: \ backup \ 1. Bak -- temporary Device
/* If the path is not specified here (for example, backup database company to disk = 'backup \ 1. Bak '),
The backed up database will be automatically backed up to the directory specified by the system:
C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ backup */

-- Type 2:
/* The first step is to create a permanent backup device (the stored procedure of the system). 1 is found in the master database:
*/
-- Execute the statement as follows:
Exec sp_addumpdevice 'disk', 'disk _ company', 'd: \ 2. Bak' -- Permanent Device

-- The execution result shows 2:
-- A backup device is added: disk_company
-- Step 2:
Backup database company to disk_company with noinit -- append by default (not overwrite)

-- The backup is complete!
-- Now I want to restore the database (I used the first method for backup, so I want the first method for restoration ),
-- Original data 3:
-- Database 4 after I manually delete several tables:

-- Execute the statement:
Restore database company from disk = 'd: \ backup \ 1. Bak -- pay attention to where the backup is going and where it will be restored
-- What will happen after execution? Please refer to the error message:
/* Message 3159, level 16, status 1, 1st rows
The log tail of the Database "company" has not been backed up. If the log contains jobs you do not want to lose, use backup log with norecovery to back up the log. Use the with replace or with stopat clause of the restore statement to only overwrite the log Content.
Message 3013, level 16, state 1, 1st rows
The Restore database is terminating abnormally. */
-- Why is this error? We can find a solution from the error message!
-- Let's take a look at the recovery mode of this database 5:

-- Because the recovery mode is complete, its function is to write all transactions into logs and restore all database files.
-- Solution 1: I only restored the database file and did not back up the log file. So I will back up the log file again.
Backup log company to disk = 'd: \ backup \ 2. Bak -- backup log file
Restore database company from disk = 'd: \ backup \ 1. Bak -- restore the database
Restore log company from disk = 'd: \ backup \ 2. Bak -- this step is optional

-- The execution result is: 6:

-- Solution 2: As prompted in the error message: Use the with replace or with stopat clause of the restore statement to only overwrite the log Content.
--- Message 3013, level 16, status 1, 1st rows. So I thought it would be okay to overwrite the log file. Although the recovery mode is complete, it is also possible to overwrite it.
-- It is only possible that no logs are completely restored for database operations.
-- The execution statement is as follows:
Restore database company from disk = 'd: \ backup \ 1. Bak 'with replace

-- Execution successful
/* Page 224 is processed for the database 'Company 'and the file 'Company _ data' (located on file 1.
The database 'Company ', file 'Company _ log' (located on file 1) has processed 5 pages.
The Restore database successfully processes 229 pages and takes 0.225 seconds (8.319 MB/second ). */

-- Solution 3: I just backed up the database, but there is no backup log file. Based on the backup recovery principle
/*
Recovery mode description
Simple restoration without backing up transaction logs
Used for small databases and infrequently changed Databases
All transactions are recorded in logs.
Keep all logs until transaction log backup
Production database
Supplement the full recovery mode of large-capacity logs
Do not write large-capacity log operations into logs
*/

-- So I changed the recovery mode in the database attribute to "simple"
-- 7:

-- I directly execute the restored code
Restore database company from disk = 'd: \ backup \ 1. Bak'
/* Execution result:
Page 224 is processed for the database 'Company ', file 'Company _ data' (located on file 1.
The database 'Company ', file 'Company _ log' (located on file 1) has processed 5 pages.
The Restore database successfully processes 229 pages and takes 0.224 seconds (8.356 MB/second ). */

-- Three restoration solutions are successfully created.

-- However, if the database in the project is being used, it cannot be used, and it is sort-out!
-- So I wrote a stored procedure to solve the problem, which also took a long time for many programmers to solve.
-- The code usage is as follows: Download the example that is included
-- Create a stored procedure killspid

Create proc killspid (@ dbname varchar (20 ))
As
Begin
Declare @ SQL nvarchar (500)
Declare @ spid int
Set @ SQL = 'Clare getspid cursor
Select spid from sysprocesses where dbid = db_id (''' + @ dbname + ''')'
Exec (@ SQL)
Open getspid
Fetch next from getspid into @ spid
While @ fetch_status <>-1
Begin
Exec ('Kill '+ @ spid)
Fetch next from getspid into @ spid
End
Close getspid
Deallocate getspid
End

Go

-- Note:
-- 1. This stored procedure should be written in the master;
-- 2. The above code is used to solve the problem that the database is being used, so it cannot obtain access to the database, otherwise the system sometimes reports an error;

Here is an example of simple Backup Recovery (ASP. NET + SQL Server 2005 runtime environment)

If repeated attempts (including disabling all database connections, restarting SQL Server, and restarting the computer) fail, the key SQL statement to solve the problem is as follows:

Alter database [datebase] Set offline with rollback immediate

If I have time, I will write an example about how to restore to a specified time point. I will use the full backup differential backup log to back up the original database file. I will also damage the original database file and restore it again! !!!!!!!!!!