Sqlserver数据库

Sqlserver命令合集(缓慢更新)

1.查询Sqlserver除系统库外的数据全部数据库

代码1:
SELECT name FROM sys.databases 
WHERE name NOT IN ('master','tempdb','model','msdb')
AND source_database_id IS NULL
ORDER BY name;
代码2:
SELECT name FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb');  

2. 数据库备份命令

*<database_name>更改为数据库名，<Backup Path>更改为备份路径
完整备份:
backup database <database_name> to disk ='<Backup Path>'
差异备份:
backup database <database_name> to disk ='<Backup Path>' with differential

3. 还原数据库命令

*<database_name>更改为数据库名，<Backup Path>更改为备份路径，replace覆盖现有的数据库
 norecovery可以与许再还原更多的备份之前继续事务日志恢复
完整恢复:
restore database <database_name> from disk ='<Backup Path>' with replace
差异恢复:
restore database <database_name> from disk ='<Backup Path>' with norecovery 

4. 如何查看SQL server数据库版本

select @@version

Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)   Aug  7 2021 01:20:37   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)