BackUpLogView 系列 - 生成日志数据库脚本（MS Sql Server）

 

 

在企业管理器中执行脚本

CREATE DATABASE [BackupLogview] ON PRIMARY
( NAME = N'BackupLogview', FILENAME = N'C:\DATA\BackupLogview.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'BackupLogview_log', FILENAME = N'C:\DATA\BackupLogview_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
use BackupLogview
Go
CREATE TABLE [dbo].[LogFiles](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](255) NULL,
[FileContent] [nvarchar](max) NULL,
[Adddate] [smalldatetime] NULL,
[BTree] [varchar](50) NULL,
[Tree] [varchar](50) NULL,
[memo] [varchar](150) NULL,
CONSTRAINT [PK__BackupFi__3214EC2762049C14] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE VIEW [dbo].[View_Num]
AS
SELECT COUNT(ID) AS Num, BTree + '-' + Tree AS Class
FROM dbo.LogFiles
WHERE (DATEDIFF(month, Adddate, GETDATE()) < 6) AND (NOT (BTree + '-' + Tree IS NULL))
GROUP BY BTree + '-' + Tree
GO
CREATE VIEW [dbo].[View_Num_Day]
AS
SELECT BTree + ' ' + Tree AS Item, CASE WHEN datediff(day, adddate, getdate()) = 0 THEN '今日' WHEN datediff(day, adddate,
getdate()) = 1 THEN '昨日' WHEN datediff(day, adddate, getdate()) = 2 THEN '前日' END AS Dayof, COUNT(ID)
AS Num
FROM dbo.LogFiles
WHERE (DATEDIFF(day, Adddate, GETDATE()) < 3)
GROUP BY BTree + ' ' + Tree, CASE WHEN datediff(day, adddate, getdate()) = 0 THEN '今日' WHEN datediff(day, adddate, getdate())
= 1 THEN '昨日' WHEN datediff(day, adddate, getdate()) = 2 THEN '前日' END
GO
ALTER TABLE [dbo].[LogFiles] ADD CONSTRAINT [DF_LogFiles_Adddate] DEFAULT (getdate()) FOR [Adddate]

 


-- 创建用户并分配权限
USE [master]
GO
CREATE LOGIN [backuser] WITH PASSWORD = 'backusr'
GO
USE [BackupLogview]
GO
CREATE USER [backuser] FOR LOGIN [backuser]
GO
EXEC sp_addrolemember 'db_datareader', 'backuser';
EXEC sp_addrolemember 'db_datawriter', 'backuser';

 

posted on 2023-07-02 21:10  程序员肯定不是好牙医  阅读(134)  评论(0)  编辑  收藏  举报