Overview of common scripts for routine database maintenance and some tests

Some database statements may not be used much at ordinary times. Therefore, it is difficult to query the database over the Internet when using the statements. On the CSDN forum today, I saw these statements, and I copied them, I tried to copy the file and it ended up. But like some friends, sometimes I find that errors are inevitable when I actually use them. Therefore, it is better to hit the day by day, today, we will spend some time running it in SQL SERVER 2008. I didn't pay much attention to the difference between the syntax of SQL2008 and the previous version. Because the commonly used select statements are the same, I always thought that 2008 should be almost the same as 2005. Unexpectedly, a problem occurred, and we found that the backup and dump statements we have seen in books have disappeared in 2008 ......

Not to mention nonsense. paste the included statements and remarks to facilitate future query ~~

1. database backup operations:
Copy codeThe Code is as follows:
Declare @ SQL varchar (8000)
Set @ SQL = 'backup database smallerp to disk = 'd: \ '+ RTRIM (CONVERT (varchar, getdate (), 112) +'. Bak '''
Exec (@ SQL );

(Note: either of the quotation marks is required. Otherwise, an error is reported)
2. Delete the backup file five days ago:
Copy codeThe Code is as follows:
Declare @ SQL varchar (8000)
Set @ SQL = 'del d: \ '+ RTRIM (CONVERT (varchar, getdate ()-5,112) +'. bak '''
Exec master.. xp_mongoshell @ SQL;

(Note: by default, xp_cndshell blocks the component. You must enable it before you can operate it properly. Use the following statement to enable EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp _ Your shell', 1; RECONFIGURE ;)
3. Database shrinking statement:
Copy codeThe Code is as follows:
Dump transaction smallerp with no_log;

(Note: A syntax error occurs near transaction. This method is no longer used to contract the database in SQL2008. Use alter as follows:
Copy codeThe Code is as follows:
Alter database smallerp set recovery simple
Dbcc shrinkdatabase (smallerp, 0 );)
Backup log smallerp with no_log;

(Note: This statement does not support one or more options (no_log), because SQL2008 no longer supports this method .)
4. View information about active users and processes in the database:
Copy codeThe Code is as follows:
Sp_who 'active ';

(Note: If this is the case, a syntax error will be prompted near sp_who, but the data can be found during execution. If an exec is added before, no error will be prompted. Process number 1-50
For internal use in the SQL SERVER system, only process numbers greater than 50 are users' connection processes. Spid is the process number, dbid is the database number, and objid is the data object number)
5. View user and process information in the database:
Copy codeThe Code is as follows:
Exec sp_who;

6. view the locks in the database:
Copy codeThe Code is as follows:
Exec sp_lock;

7. How to analyze SQL statements in SQL SERVER:
Copy codeThe Code is as follows:
Set statistics time {on | off} (you only need to specify on or off when writing a statement)
Set statistics io {on | off}

8. display the query execution plan in text mode:
Copy codeThe Code is as follows:
Set showplan_all {on | off}
Set showplan_text {on | off}
Set statistics profile {on | off}

9. Differential backup:
Copy codeThe Code is as follows:
Declare @ str varchar (100)
Set @ str = 'd: \ '+ replace (convert (varchar, getdate (), 20 ),'-',''),'', ''), ':','') + '. diff'
Backup database smallerp to disk = @ str
With differential, RETAINDAYS = 8, NOFORMAT, NOINIT,
NAME = n' Demo differential backup ', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO

10. Delete the expired backup file and set it to twice a day:
Copy codeThe Code is as follows:
Declare @ str varchar (100), @ dir varchar (100), @ fileName varchar (30)
Set @ dir = 'del D :\'
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

First of all, it seems that this is generally not necessary and relatively common ~