How to restore an SQL Server database log and a database without logs

I. Methods for deleting Database Log Files

When you run an SQL statement, the database reports that the transaction log is full and then reports an error. How to delete the database logs and try to delete the logs for half a day? Now we provide two ways to delete log files. I hope this will help you!

Method 1: manual operation
1. Choose "Database"> "right-click" properties ">" options ">" recovery mode ">" switch from complete to simple ".
2. Choose "Database"> "right-click" task ">" contract ">" file ">" switch from "done" to "simple"> "file type"> "log"> contract the file

Method 2: Save the stored procedure instead of manual operation

Copy codeThe Code is as follows:
-- How many MB does the log file contract?
DECLARE @ DBLogSise AS INT
SET @ DBLogSise = 0
-- Query the name of the log file corresponding to the database
DECLARE @ strDBName as nvarchar (500) DECLARE @ strLogName as nvarchar (500) DECLARE @ strSQL as varchar (1000)
SELECT
@ StrLogName = B. name, @ strDBName =. name FROM master. sys. databases as a inner join sys. master_files as B ON. database_id = B. database_id where. database_id = DB_ID ()

SET @ strSQL ='
-- Set the database recovery mode to simple
Alter database ['+ @ strDBName +'] set recovery simple;
-- Shrink log files
Dbcc shrinkfile (''' + @ strLogName + ''', '+ CONVERT (VARCHAR (20), @ DBLogSise) + ');
-- Restore the database to full Mode
Alter database ['+ @ strDBName +'] set recovery full'
Exec (@ strSQL)

1. Execute the above stored procedure in the database
2. Then execute EXEC dbo. usp_p_delDBLog @ DBLogSise = 0 (the number of MB to be reduced)

2. Database restoration methods without log files

Today, when the client executes an SQL statement, an error is reported. It is found that all the database disks on the client server have been used up, and the log file reaches GB. Later, due to my incorrect operation, the log file (. ldf) is deleted and later appended. failure to append the mdf file without the log file in the old man's workshop. After some tossing, I finally solved the problem. Let's share it with you!

Procedure

1. Create a database file with the same name
2. Suspend the SQLSetver Service
3. overwrite the original mdf file to the new database and delete the ldf file of the new database.
4. Restart the SQLSetver service. In this case, the database is like this and cannot be opened.

5. Execute the following SQL statement
Copy codeThe Code is as follows:
-- 1. Set to emergency
Alter database name set emergency
-- 2. Set to single-user mode
Alter database name set single_user
-- 3. Check and recreate the log file
Dbcc checkdb ('database name', REPAIR_ALLOW_DATA_LOSS)
-- 4. If the error message is displayed in step 1, run Step 2. If no error exists, skip this step.
Dbcc checkdb ('database name', REPAIR_REBUILD)
-- 5. Restore to multi-user mode
Alter database name set multi_user

6. Now the log file for Database Change will be generated again, and the whole process is completed.

Alternatively, you can manually attach it. (For more information about this method, see @ QR code)