sql server 学习笔记 ( backup 备份和复原方案, clear log file )
2023-09-27 14:23:55 时间
更新: 2020-11-15
log file 是会一直长大的, 直到你 backup log file, backup full database log file 还是会长大.
一般的做法是 full back -> log backup (通常需要 2 次, 才可以 shrink, 不知道原因) 然后 shrink log file.
refer :
https://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log
https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/
做个记入就好
USE [master] SELECT bs.database_name AS 'Database Name', bs.backup_start_date AS 'Backup Start', bs.backup_finish_date AS 'Backup Finished', DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)', bmf.physical_device_name AS 'Backup File', CASE WHEN bs.[type] = 'D' THEN 'Full Backup' WHEN bs.[type] = 'I' THEN 'Differential Database' WHEN bs.[type] = 'L' THEN 'Log' WHEN bs.[type] = 'F' THEN 'File/Filegroup' WHEN bs.[type] = 'G' THEN 'Differential File' WHEN bs.[type] = 'P' THEN 'Partial' WHEN bs.[type] = 'Q' THEN 'Differential partial' END AS 'Backup Type' FROM msdb.dbo.backupmediafamily bmf WITH(NOLOCK) INNER JOIN msdb..backupset bs WITH(NOLOCK) ON bmf.media_set_id = bs.media_set_id WHERE bs.database_name = 'test-recovery' ORDER BY bs.backup_start_date ASC; -- note for 下次看 -- backup 有 3 种, full, log, differential -- 通常是 full -> log -> log -> differential -> log -> log -> full -- 比如 1 天 1 个 full, 半天一个 differential, 每小时一个 log -- 备份的关键是, 1.恢复的时间(尽可能短) 2. 恢复的层度 (尽可能完整) -- express, web edition 不支持 backup compression -- norecovery 意思是还有下一个 restore 要跑, 最后一个 restore 才 recovery -- refer http://mysql.taobao.org/monthly/2017/11/03/ -- refer http://mysql.taobao.org/monthly/2017/12/05/ -- stats 是显示 progress % stats 10 = 完成 10% 就显示一下 -- init and replace 用于 full 的情况, 另外 2 个 用 noint 和不需要放 replace -- STOPAT 是只恢复到某事时间点停止 use [master]; use [test-recovery]; alter database [test-recovery] set recovery full with no_wait declare @now nvarchar(50) = REPLACE(convert(nvarchar(20),GetDate(),120),':','-'); set @now = '2018-09-02 18-16-27'; declare @type nvarchar(50) = 'full'; declare @path nvarchar(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_'+ @now +'_' + @type +'.bak'; --backup log [test-recovery] to disk = @path with noinit,stats=10; --backup database [test-recovery] to disk = @path with differential,init,stats=10; --backup database [test-recovery] to disk = @path with init,stats=10; restore database [test-recovery] from disk = @path with norecovery, replace; INSERT INTO Products(name) VALUES ('dada'); INSERT INTO Products(name) VALUES ('yyyy'); INSERT INTO Products(name) VALUES ('zz'); INSERT INTO Products(name) VALUES ('gg'); RESTORE DATABASE [test-recovery] FROM DISK =N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_2018_09_02-16_17_differential.bak' WITH NORECOVERY; RESTORE LOG [test-recovery] FROM DISK =N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_2018_09_02_16_18_log.bak' WITH RECOVERY; RESTORE LOG [test-recovery] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\test-recovery_2018_09_02_16_18_log.bak' WITH STOPAT = '2017-12-17 23:04:46.130', RECOVERY
相关文章
- 让Windows Server 2008 + IIS 7+ ASP.NET 支持10万并发请求
- MS SQL Server递归查询
- SQL Server 数据恢复到指点时间点(完整恢复)
- SVN Server配置详解 及备份
- sql server的备份
- 使用Management Studio的本地副本访问Azure VM上的SQL Server实例
- ArcGIS Server开发教程系列(1)Arcgis server 10.1 的安装
- ubuntu-15.10-server-i386.iso 安装 Oracle 11gR2 数据库
- SQL Server 触发器
- Windows 不能在 本地计算机 启动 SQL Server(MSSQLSERVER)。错误码126
- Cypress系列(70)- server() 命令详解
- Microsoft SQL Server 2008 R2出现索引超出数组界限
- SQL Server表分区
- sql server 生成连续日期和数字
- 使用SQL-Server分区表功能提高数据库的读写性能
- windows server 2003下搭建amp环境
- The last packet sent successfully to the server was 0 milliseconds ago.[nutch---mysql ]
- 在Linux(ubuntu server)上面安装NodeJS的正确姿势
- sql server数据库保存图片或者其他小文件