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 it and it ended up. But like some friends, sometimes I find that errors are inevitable when I actually use them. So, it's better to hit the day, just today
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 it and it ended up. But like some friends, sometimes I find that errors are inevitable when I actually use them. So, it's better to hit the day, just today
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 errors when using the website space. 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 ......
Speaking of nonsense, the Hong Kong Server adds the included statements and remarks to facilitate future query ~~
1. database backup operations:
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:
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:
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:
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:
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, Hong Kong server lease, objid is the data object number)
5. View user and process information in the database:
Exec sp_who;
6. view the locks in the database:
Exec sp_lock;
7. How to analyze SQL statements in SQL SERVER:
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:
Set showplan_all {on | off}
Set showplan_text {on | off}
Set statistics profile {on | off}
9. Differential backup:
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:
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 ~
