SQL-Server log management: Restore Log Point: The following example reduces the status to its state at 10:00 am on July 1, 1998, and examples of the restore operations involving multiple logs and multiple backup devices.
- Restore the database backup.RESTORE DATABASE MyNwind FROM MyNwind_1, MyNwind_2 WITH NORECOVERYGORESTORE LOG MyNwind FROM MyNwind_log1 WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'GORESTORE LOG MyNwind FROM MyNwind_log2 WITH RECOVERY, STOPAT =' Jul 1, 1998 10:00 Am'go
Log operation:
- Compressed log and database file size
/ * - Special pay attention Please follow the steps, not in front of the steps, please do not do the following steps may not damage your database. - * /
1. Clear Log Dump Transaction Database name with no_log
2. Truncate the transaction log: Backup log database name with no_log
3. Shrinkage database files (if not compressed, database files do not reduce Enterprise Manager - Right-click on the database - All Tasks - Shrink Database - Shrink File - Select Log File - In Shrink Mode The choice to shrink to XXM, here will give a minimum M number that is allowed to shrink, enter this number directly, determine it - select data file - Select shrinkage to XXM in the shrink mode, here will give a permission The minimum number of shrinks is contracted, enter this number directly, it is ok.
You can also use SQL statements to complete - shrink database DBCC ShrinkDatabase (customer information)
- Spencer specified data file, 1 is the file number, you can query this statement: SELECT * from sysfiles dbcc shrinkfile (1)
4. In order to maximize the log file (if it is SQL 7.0, this step can only be performed in the query analyzer) a. Separate Database: Enterprise Manager - Server - Database - Right - Separate Database
b. Delete log files in my computer
c. Additional Database: Enterprise Manager - Server - Database - Right - Right - Additional Database
This method will generate new logs, only more than 500 k
Or use code: The following example separates the PUBS and attached one of the files in the public to the current server.
a. Separate EXEC SP_DETACH_DB @dbname = 'PUBS'
b. Delete log files
c. Additional EXEC SP_ATTACH_SINGLE_FILE_DB @dbname = 'PUBS', @physname = 'c: / program files / microsoft sql server / mssql / data / pubs.mdf'
5. In order to automatically shrink in the future, do the following settings: Enterprise Manager - Server - Right-click Database - Properties - Option - Select "Auto Shrink"
--SQL statement setting mode: exec sp_dboption 'Database name,' autoshrink ',' True '
6. If you want to get the log growth too large, you don't want to grow too large enterprise manager - Server - Right-click Database - Property - Transaction Log - Limit the file growth to XM (x is your maximum data file size allowed)
--SQL statement setting mode: Alter Database Database name modify file (name = logical file name, maxsize = 20)
Attachment of the database:
The following example attaches two files in the public to the current server.
