SQL database backup and restoration operations Frequently Asked Questions

DEPT is used as an example to illustrate the problems encountered during database backup and Restoration:

Backup database dept to disk = 'd: \ deptbackup. Bak --- no problem with Data Backup
Restore database dept from disk = 'd: \ deptbackup. Bak '---- The following prompt appears when restoring data:

Because the database is in use, you cannot obtain exclusive access to the database !!
Solution:

In this case, you must first kill the threads that are using the database before restoring the database.

This solution uses sysprocesses in the system table. To access sysprocesses, you must locate the master database.
Use master
Declare @ dbname varchar (20)
Set @ dbname = 'dept'
Declare @ SQL nvarchar (500)
Declare @ spid int -- The spid value is a unique integer assigned to the connection when the user connects.
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 -- if the fetch statement does not fail to be executed or this row is not in the result set.
Begin
Exec ('Kill '+ @ spid) -- terminate a normal connection
Fetch next from getspid into @ spid
End
Close getspid
Deallocate getspid

However, if you execute Restore database dept from disk = 'd: \ deptbackup. Bak to restore, an error will be prompted:

Message 3159, level 16, state 1, 1st rows
The log tail of the Database "Dept" 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.

Cause Analysis
This is because the online restored database generates new logs after the last backup, so according to the default backup options, the system will prompt the backup log tail to avoid transaction interruption.
Solution
Back up tail logs if necessary. If you do not need it, you can select the overwrite existing database check box on the Restore database tab.

If you want to use the command to perform the operation, enter the following command:

Restore database [dept] from disk = 'd:/deptbackup. Bak 'with file = 1, nounload, replace, stats = 10
Go

If you enter the preceding command, an error similar to the following is displayed:

Message 4038, level 16, state 1, 1st rows
File ID 1 cannot be found on device 'd:/deptbackup. Bak.
Message 3013, level 16, state 1, 1st rows
The Restore database is terminating abnormally.

Cause analysis:

The id value specified by file is incorrect.

Solution:

In the SQL object Resource Manager-> Management-> SQL server log, you can view the error log. You should be able to find the correct File ID in the log, change the ID and then execute it!