常用脚本

Qxw大约 4 分钟SqlServerSqlServer脚本

数据库备份与恢复

备份数据库

-- 备份数据库
BACKUP DATABASE [数据库名] TO DISK = N'路径\文件名.bak'
WITH FORMAT, INIT, NAME = N'数据库名-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

-- 备份数据库指定文件
BACKUP DATABASE [数据库名] TO DISK = N'路径\文件名.bak'
WITH DIFFERENTIAL, FORMAT, INIT, NAME = N'数据库名-差异数据库备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

-- 完整备份
BACKUP DATABASE [YourDatabaseName]
TO DISK = N'C:\Backup\YourDatabaseName_Full.bak'
WITH NOFORMAT, NOINIT,
NAME = N'YourDatabaseName-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 差异备份
BACKUP DATABASE [YourDatabaseName]
TO DISK = N'C:\Backup\YourDatabaseName_Diff.bak'
WITH DIFFERENTIAL,
NOFORMAT, NOINIT,
NAME = N'YourDatabaseName-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 日志备份
BACKUP LOG [YourDatabaseName]
TO DISK = N'C:\Backup\YourDatabaseName_Log.trn'
WITH NOFORMAT, NOINIT,
NAME = N'YourDatabaseName-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

恢复数据库

-- 恢复数据库
RESTORE DATABASE [数据库名] FROM DISK = N'路径\文件名.bak'
WITH REPLACE, STATS = 10;

-- 恢复数据库指定文件
RESTORE DATABASE [数据库名] FROM DISK = N'路径\文件名.bak'
WITH FILE = 1, REPLACE, STATS = 10;

-- 恢复数据库（完整备份）
RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Full.bak'
WITH FILE = 1,
NOUNLOAD, REPLACE, STATS = 10;

-- 恢复数据库（差异备份）
RESTORE DATABASE [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Diff.bak'
WITH FILE = 1,
NOUNLOAD, REPLACE, STATS = 10;

-- 恢复事务日志
RESTORE LOG [YourDatabaseName]
FROM DISK = N'C:\Backup\YourDatabaseName_Log.trn'
WITH FILE = 1,
NOUNLOAD, STATS = 10;

用户和权限管理

创建登录名和用户

-- 创建 SQL Server 登录名
CREATE LOGIN [YourLoginName] WITH PASSWORD = 'YourStrongPassword';

-- 为数据库创建用户
USE [YourDatabaseName];
CREATE USER [YourUserName] FOR LOGIN [YourLoginName];

授予用户权限

-- 授予用户权限
USE [YourDatabaseName];
GRANT SELECT ON [YourTableName] TO [YourUserName];

删除用户和登录名

-- 删除用户
USE [YourDatabaseName];
DROP USER [YourUserName];

-- 删除登录名
DROP LOGIN [YourLoginName];

修改用户密码

-- 修改用户密码

ALTER LOGIN [YourLoginName] WITH PASSWORD = 'NewStrongPassword';

修改用户权限

-- 修改用户权限
USE [YourDatabaseName];
REVOKE SELECT ON [YourTableName] FROM [YourUserName];

数据库对象管理

创建表

-- 创建表
CREATE TABLE [YourTableName]
(
    [YourColumnName1] [YourColumnType1],
    [YourColumnName2] [YourColumnType2],
    [YourColumnName3] [YourColumnType3]
);

创建视图

-- 创建视图
CREATE VIEW [YourViewName]
AS
SELECT [YourColumnName1], [YourColumnName2]
FROM [YourTableName]
WHERE [YourColumnName3] = 'SomeValue';

创建存储过程

-- 创建存储过程
CREATE PROCEDURE [YourProcedureName]
AS
BEGIN
    -- 存储过程逻辑
END;

创建函数

-- 创建函数
CREATE FUNCTION [YourFunctionName]
(
    @param1 [YourDataType1],
    @param2 [YourDataType2]
)
RETURNS [YourDataType3]
AS
BEGIN
    -- 函数逻辑
    RETURN @param1 + @param2;
END;

创建索引

-- 创建索引
CREATE INDEX [YourIndexName]
ON [YourTableName] ([YourColumnName]);

创建约束

-- 创建约束
ALTER TABLE [YourTableName]
ADD CONSTRAINT [YourConstraintName]
PRIMARY KEY ([YourColumnName]);

删除表

-- 删除表
DROP TABLE [YourTableName];

删除视图

    -- 删除视图
    DROP VIEW [YourViewName];

删除存储过程

-- 删除存储过程
DROP PROCEDURE [YourProcedureName];

删除函数

-- 删除函数
DROP FUNCTION [YourFunctionName];

删除索引

-- 删除索引
DROP INDEX [YourIndexName]
ON [YourTableName];

删除约束

      -- 删除约束
      ALTER TABLE [YourTableName]
      DROP CONSTRAINT [YourConstraintName];

性能监控与调优

查询执行计划

-- 查询执行计划
-- 查看查询的执行计划
SET SHOWPLAN_XML ON;
GO
-- 在此处编写您的查询
SELECT * FROM [YourDatabaseName].[YourSchema].[YourTable];
GO
SET SHOWPLAN_XML OFF;
GO

查找慢查询

-- 查找慢查询
SELECT
    qs.execution_count AS [Execution Count],
    qs.total_worker_time AS [Total Worker Time],
    qs.total_elapsed_time AS [Total Elapsed Time],
    qs.total_logical_reads AS [Total Logical Reads],
    qs.total_logical_writes AS [Total Logical Writes],
    qs.creation_time AS [Creation Time],
    st.text AS [Query Text],
    qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_elapsed_time DESC;
GO

-- 查找执行时间最长的查询
SELECT TOP 10
    total_elapsed_time / execution_count AS avg_elapsed_time,
    execution_count,
    statement_start_offset,
    statement_end_offset,
    plan_handle,
    query_hash,
    sql_handle,
    *
FROM sys.dm_exec_query_stats
ORDER BY avg_elapsed_time DESC;

作业管理

查找正在运行的作业

USE msdb;
GO

-- 查找正在运行的作业
SELECT 
    sj.job_id, 
    sj.name 
FROM 
    msdb.dbo.sysjobs_view sj
JOIN 
    msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
WHERE 
    sja.stop_execution_date IS NULL
    AND sja.start_execution_date IS NOT NULL;

-- 停止正在运行的作业
DECLARE @job_id UNIQUEIDENTIFIER;
SET @job_id = (SELECT TOP 1 sj.job_id
               FROM msdb.dbo.sysjobs_view sj
               JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
               WHERE sja.stop_execution_date IS NULL
               AND sja.start_execution_date IS NOT NULL);

IF @job_id IS NOT NULL
BEGIN
    EXEC msdb.dbo.sp_stop_job @job_id = @job_id;
END;

-- 创建一个新的作业
USE msdb;
GO

EXEC sp_add_job
    @job_name = N'YourJobName';

-- 添加作业步骤
EXEC sp_add_jobstep
    @job_name = N'YourJobName',
    @step_name = N'Step1',
    @subsystem = N'TSQL',
    @command = N'SELECT * FROM YourDatabase.YourSchema.YourTable',
    @retry_attempts = 5,
    @retry_interval = 5;

-- 添加作业计划
EXEC sp_add_schedule
    @schedule_name = N'YourJobSchedule',
    @freq_type = 4, -- 每日
    @freq_interval = 1,
    @active_start_time = 233000; -- 23:30:00

-- 将作业与计划关联
EXEC sp_attach_schedule
   @job_name = N'YourJobName',
   @schedule_name = N'YourJobSchedule';

-- 启动作业
EXEC sp_start_job
    @job_name = N'YourJobName';

停止作业

-- 停止作业
EXEC sp_stop_job
    @job_name = N'YourJobName';

-- 删除作业
EXEC sp_delete_job
    @job_name = N'YourJobName';

-- 删除作业步骤
EXEC sp_delete_jobstep
    @job_name = N'YourJobName',
    @step_name = N'Step1';

-- 删除作业计划
EXEC sp_detach_schedule
    @job_name = N'YourJobName',
    @schedule_name = N'YourJobSchedule';

EXEC sp_delete_schedule
    @schedule_name = N'YourJobSchedule';

常用脚本

目录