MSSQL backup and related core system stored procedures

------ The following are extracted from different master blogs. I was planning to write one piece by myself. I found that all the experts have completed the writing. So here we use it directly.

 

-- Backup
Declare @ SQL varchar (8000)
Set @ SQL = 'backup database MIS to disk = 'd: \ databack \ MIS'
+ Rtrim (convert (varchar, getdate (), 112) + '. Bak '''
Exec (@ SQL)

-- Delete the backup file 15 days ago
Set @ SQL = 'del D: \ databack \ MIS'
+ Rtrim (convert (varchar, getdate ()-15,112) + '. Bak '''
Exec master .. xp_mongoshell @ SQL

-- Clear logs
Dump transaction databasename with no_log
Backup log databasename with no_log

-- View user and process information in the database
Sp_who

-- View information about active users and processes in the SQL Server database
Sp_who 'active'

-- View the locks in the SQL Server database
Sp_lock
-- Process number -- 50 is used inside the SQL Server System, and the process number is greater than the user's connection process.
-- Spid indicates the process number, dbid indicates the database number, and objid indicates the data object number.

-- Method for shrinking Database Log Files
-- Shrink database logs in simple recovery mode. The size unit of @ database_name_log after the contraction is M.
Backup log @ database_name with no_log
DBCC shrinkfile (@ database_name_log, 5)

-- SQL Server SQL statement analysis method:
Set statistics time {on | off}
Set statistics Io {on | off}

-- Display query execution plan in graphical mode
-- In the query analyzer-> query-> display the estimated evaluation plan (d)-Ctrl-l or click the graph in the toolbar

-- Display query execution plans in text mode
Set showplan_all {on | off}
Set showplan_text {on | off}
Set statistics profile {on | off}

/******************** Complete backup job ***************** **/
-- Full backup, once a week
Use master
Go
Declare @ STR varchar (100)
Set @ STR = 'd: \ dbtext \ jgj \ dbabak \ fullbak '+ Replace (replace (convert (varchar, getdate (), 20 ),'-', ''),'', ''), ':','') + '. bak'
Backup database [demo] to disk = @ Str
With retaindays = 15, noformat, noinit,
Name = n' demo full backup ', Skip, norewind,
Nounload, stats = 10
Go

 

/******************* Differential backup job ***************** **/
-- Truncate logs
Use master
Go
Backup log demo with no_log
Go
-- Shrink log files
Use demo
Go
DBCC shrinkfile (n'demo _ log', 0, truncateonly)
Go
-- Differential backup, once a day
Use master
Go
Declare @ STR varchar (100)
Set @ STR = 'd: \ dbtext \ jgj \ dbabak \ diffbak '+ Replace (replace (convert (varchar, getdate (), 20 ),'-', ''),'', ''), ':','') + '. diff'
Backup database [demo] to disk = @ Str
With differential, retaindays = 8, noformat, noinit,
Name = n' demo differential backup ', Skip, norewind,
Nounload, stats = 10
Go

 

/******************* Log backup job ****************** */
-- Log backup, once every hour
Use demo
Go
Declare @ STR varchar (100)
Set @ STR = 'd: \ dbtext \ jgj \ dbabak \ logbak '+ Replace (replace (convert (varchar, getdate (), 20 ),'-', ''),'', ''), ':','') + '. trn'
Backup log [demo] to disk = @ Str
With retaindays = 3, noformat, noinit,
Name = n'demo log backup ', Skip, norewind,
Nounload, stats = 10
Go

 

-- Delete an expired backup file twice a day
Declare @ STR varchar (100), @ dir varchar (100), @ filename varchar (30)
Set @ dir = 'del D: \ dbtext \ jgj \ dbabak \'
Set @ filename = left (replace (convert (varchar, getdate ()-15, 20 ),'-',''),'',''),': ', ''), 8)
Set @ STR = @ dir + 'fullbak' + @ filename + '*. Bak'
Exec xp_cmdshell @ Str
Set @ filename = left (replace (convert (varchar, getdate ()-8, 20 ),'-',''),'',''),': ', ''), 8)
Set @ STR = @ dir + 'diffbak' + @ filename + '*. Diff'
Exec xp_cmdshell @ Str
Set @ filename = left (replace (convert (varchar, getdate ()-8, 20 ),'-',''),'',''),': ', ''), 8)
Set @ STR = @ dir + 'lobak' + @ filename + '*. trn'
Exec xp_cmdshell @ Str
-- It is best to back up logs to restore data in the future...
The following describes how to process logs:
Generally, steps 4 and 6 are not recommended.
Step 3 is not safe, and may damage the database or lose data
If the log reaches the upper limit in step 1, the subsequent database processing will fail and the log can be restored after being cleared.
--*/

-- All the following database names refer to the names of the databases you want to process

1. Clear logs
Dump transaction database name with no_log

2. truncate transaction logs:
Backup log library name with no_log

3. Compress database files (if not compressed, the database files will not be reduced
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file
-- Select log file -- select to shrink to xxm in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to xxm in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.

You can also use SQL statements to complete
-- Shrink Database
DBCC shrinkdatabase (database name)

-- Contract the specified data file. 1 is the file number. You can use this statement to query: Select * From sysfiles
DBCC shrinkfile (1)

4. To minimize log files (for SQL 7.0, this step can only be performed in the query analyzer)
A. Separate the database:
Enterprise Manager -- server -- database -- Right-click -- detach Database

B. Delete log files in my computer

C. Additional database:
Enterprise Manager -- server -- database -- Right-click -- attach Database

This method generates a new log with a size of more than 500 K.

Or use the code:
The following example separates pubs and attaches a file in pubs to the current server.

A. Separation
Exec sp_detach_db @ dbname = 'database name'

B. Delete log files

C. Attach
Exec sp_attach_single_file_db @ dbname = 'database name ',
@ Physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ database name. MDF'

5. In order to automatically contract in the future, make the following settings:
Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

-- SQL statement setting method:
Exec sp_dboption 'database name', 'autowrite', 'true'

6. If you want to prevent the log from increasing too much in the future
Enterprise Manager -- server -- Right-click Database -- properties -- transaction log
-- Limit file growth to xm (X is the maximum data file size you allow)

-- SQL statement settings:
Alter database name Modify file (name = logical file name, maxsize = 20)

--- Query the index operation information
Select * From SYS. dm_db_index_usage_stats

-- View the index information of a table
Exec sp_helpindex TB

-- Combine SYS. Indexes and SYS. index_columns, SYS. Objects, and SYS. columns to query the information of the table or view to which the index belongs.
Select
O. Name as table name,
I. Name as index name,
C. Name as column name,
I. type_desc as type description,
Is_primary_key as primary key constraint,
Is_unique_constraint as unique constraint,
Is_disabled as Disabled
From
SYS. Objects o
Inner join
SYS. Indexes I
On
I. object_id = O. object_id
Inner join
SYS. index_columns IC
On
IC. index_id = I. index_id and IC. object_id = I. object_id
Inner join
SYS. Columns C
On
IC. column_id = C. column_id and IC. object_id = C. object_id
Go

-- Query the key and column information of an index
Select
O. Name as table name,
I. Name as index name,
C. Name as field number,
From
Sysindexes I inner join sysobjects o
On
I. ID = O. ID
Inner join
Sysindexkeys K
On
O. ID = K. ID and I. indid = K. indid
Inner join
Syscolumns C
On
C. ID = I. ID and K. colid = C. colid
Where
O. Name = 'table name'
-- View detailed statistics
DBCC show_statistics

-- View the statistics automatically created by the index
Exec sp_autostats 'object name'

-- Disable the database option for automatically generating statistics
Alter datebase database name set auto_create_statistics off

-- Create statistics
Create Statistics statistical information name on table name (column name)
[
[[Fullscan
Sample number {percent | rows}]
[Norecompute]
]
Go
The preceding parameters are described as follows:
Fullscan: Specify to collect statistics for all rows in a table or view.
Sample number {percent | rows}: specifies the number of data rows or percentage of data to be read by random sampling. The sample option cannot be used together with the fullscan option.
Norecompute: the specified database engine does not automatically recalculate statistics.

-- Calculate random sampling statistics
Create Statistics statistical information name on table name (column name)
With sample 5 percent --- create statistical information, calculate the random sampling statistics by 5%
Go

-- Create statistics
Exec sp_createstats -- check the parameters for help. Here we will not list them one by one.

-- Modify statistics
Update statistics table name | view name
Index name | statistical information name, index name | statistical information name ,.....
[
[[Fullscan
Sample number {percent | rows}]
[Norecompute]
]
--- The parameters are similar to the create statistics statement. The following describes several common applications.
1. Update all statistics of a specified table
Update statistics table name

2. Update the statistics of a single index of a specified table
Update statistics table name index name

3. perform a full scan of the table to update statistics
Update statistics table name (column name) with fullscan
--- Query the index operation information
Select * From SYS. dm_db_index_usage_stats

-- Query the statistical information of a specified table (Combined Query by sys. Stats and sysobjects)
Select
O. Name, -- table name
S. Name, -- Name of the statistics
Auto_created, -- whether the statistics are automatically created by the query Processor
User_created -- whether the statistics are displayed and created by the user
From
SYS. Stats
Inner join
Sysobjects o
On
S. object_id = O. ID
Where
O. Name = 'table name'
Go

-- View the column information in Statistics
Select
O. Name, -- table name
S. Name, -- Name of the statistics
SC. stats_column_id,
C. Name --- column name
From
SYS. stats_columns SC
Inner join
Sysobjects o
On
SC. object_id = O. ID
Inner join
SYS. Stats s
On
SC. stats_id = S. stats_id and SC. object_id = S. object_id
Inner join
SYS. Columns C
On
SC. column_id = C. column_id and SC. object_id = C. object_id
Where
O. Name = 'table name'

-- View detailed statistics
DBCC show_statistics

-- View the statistics automatically created by the index
Exec sp_autostats 'object name'

-- Disable the database option for automatically generating statistics
Alter datebase database name set auto_create_statistics off

-- Create statistics
Create Statistics statistical information name on table name (column name)
[
[[Fullscan
Sample number {percent | rows}]
[Norecompute]
]
Go
The preceding parameters are described as follows:
Fullscan: Specify to collect statistics for all rows in a table or view.
Sample number {percent | rows}: specifies the number of data rows or percentage of data to be read by random sampling. The sample option cannot be used together with the fullscan option.
Norecompute: the specified database engine does not automatically recalculate statistics.

-- Calculate random sampling statistics
Create Statistics statistical information name on table name (column name)
With sample 5 percent --- create statistical information, calculate the random sampling statistics by 5%
Go

-- Create statistics
Exec sp_createstats -- check the parameters for help. Here we will not list them one by one.

-- Modify statistics
Update statistics table name | view name
Index name | statistical information name, index name | statistical information name ,.....
[
[[Fullscan
Sample number {percent | rows}]
[Norecompute]
]
--- The parameters are similar to the create statistics statement. The following describes several common applications.
1. Update all statistics of a specified table
Update statistics table name

2. Update the statistics of a single index of a specified table
Update statistics table name index name

3. perform a full scan of the table to update statistics
Update statistics table name (column name) with fullscan

-- Obtain disk read/write status
Select
@ Total_read as 'number of disk reads ',
@ Total_write as 'number of disk writes ',
@ Total_error as 'number of disk write errors ',
Getdate () as 'current time'

-- Obtain the I/O statistics of database files
Select * From fn_virtualfilestats (null, null)
-- Two Parameters
Database_id -- specifies the database number. If it is null, I/O statistics are returned for All database instances.
File_id -- the number of the file. If it is null, information is returned for all files.

-- Obtain the I/O operation status
Select
@ Id_busy, -- the time when SQL is used to execute input and output operations since the last time it was started
@ Timeticks: the number of microseconds corresponding to each clock cycle
@ Id_busy * @ timeticks as 'I/O operation milliseconds ',
Getdate () as 'current time'

-- View SQL Server CPU activity and work conditions
Select
@ Cpu_busy, -- working time since the last startup
@ Timeticks: the number of microseconds corresponding to each clock cycle
@ Cpu_busy * cast (@ timeticks as float)/1000 as 'cpu working time (seconds )',
@ Idie * cast (@ timeticks as float)/1000 as 'cpu idle time (seconds )'
Getdate () as 'current time'

-- Get network packet statistics
Select
Getdate () as 'current time ',
@ Pack_received as 'number of input packets ',
@ Pack_sent as 'number of output packets ',
@ Packet_error as 'number of error packages'

Server Configuration Options
-- Start awe
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'awe enable', 1 -- enable awe option. For more information about how to use the 4G memory, see note 3.
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go

-- Specify the number of rows in the cursor set
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'cursor threshold '-- specify the number of rows in the cursor set. If the number of rows exceeds this value, the cursor key set is generated asynchronously.
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go
/* The 'show advanced options' configuration option has been changed from 0 to 1. Run the reconfigure statement for installation.
Name minimum maximum config_value run_value
--------------------------------------------------------------------------------
Cursor threshold-1 2147483647-1-1

The 'show advanced options' configuration option has been changed from 1 to 0. Run the reconfigure statement for installation. */

-- Specify the default language value of the full-text index Column
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'default full-text language' -- 2052 indicates Simplified Chinese.
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go
/* Name minimum maximum config_value run_value
--------------------------------------------------------------------------------
Default full-text language 0 2147483647 2052 2052 */

-- Control whether to allow the trigger to return the result set
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'disallow results from trigger', 1-1 indicates on
Go
Sp_configure 'disallow results from trigger', 0--0 indicates off
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go
/* The configuration option 'disallow results from trigger' has been changed from 1 to 1. Run the reconfigure statement for installation.
The configuration option 'disallow results from trigger' has been changed from 1 to 0. Run the reconfigure statement for installation. */

-- Control the maximum memory size initially allocated for index creation
Sp_configure 'index Create memory ', 4096
Go

-- Set the maximum number of available locks
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'locks' --- add ', number' to the end'
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go
/* Name minimum maximum config_value run_value
--------------------------------------------------------------------------------
Locks 5000 2147483647 0 0
*/

-- Set the number of worker threads that SQL processes can use
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'max worker Threads' -- add ', number' to the end of the Configuration'
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go
/* Name minimum maximum config_value run_value
--------------------------------------------------------------------------------
Max worker threads 128 32767 0 0
*/

-- Specify the time for a query to wait for the required resources before timeout.
Sp_configure 'query wait', number
Go

-- Specifies the duration for remote operations before SQL Server times out.
Sp_configure 'remote query timeout', number
Go

-- Whether to allow running of the system stored procedure xp_mongoshell
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'xp _ Your shell', 1
Reconfigure
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go

-- Control the execution of stored procedures from the local or remote server running the SQL server instance
Sp_configure 'show advanced options', 1
Reconfigure
Go
Sp_configure 'remote access', 1 -- 1 indicates allow
Reconfigure
Go
Sp_configure 'remote access', 0 -- 0 indicates disabled
Reconfigure
Go
Sp_configure 'show advanced options', 0
Reconfigure
Go

--- View more books online

-- Start, pause, and stop the local SQL Server Service
Net start MSSQLServer -- start
Net pause MSSQLServer -- Pause
Net continue MSSQLServer --- continue the stopped service
Net stop MSSQLServer -- stop

-- Query server configuration options
Select * From SYS. tolerations
Go
-- The result is
Configuration_id -- Unique ID of the configuration option
Name -- Name of the configuration option
Value -- Value of the configuration option
Minimum -- minimum value of configuration options
Maximum -- maximum value of configuration options
Value_in_use -- current running value of the configuration option
Description -- Description of configuration options
Is_dynamic -- variable that takes effect only when the value is equal to 1.
Is_anvanced -- a variable equal to 1 indicates that the show advanced statement must be executed to take effect.

-- You can also use sp_configure to query the server configuration options, but the parameters are different. For more information, see books online.