Common SQL function statements

1. Adjust Memory
Sp_configure 'show advanced options', 1
GO
RECONFIGURE
Go
Sp_configure 'awe enabled', 1
GO
RECONFIGURE
Go
Sp_configure 'min server memory ', 1024
Go
Sp_configure 'max server memory ', 3072
GO
RECONFIGURE
Go
PS: OS needs to open AWE and add/3G or/PAE (Enterprise Edition) in boot. ini to support 4 GB memory or above!
2. Disable enabling xp_cmdshell.
Sp_configure 'show advanced options', 1
GO
-- Reconfigure
RECONFIGURE
GO
-- Enable xp_cmdshell
Sp_configure 'xp _ Your shell', 1
GO
-- Reconfigure
RECONFIGURE
GO
3. shrink the database
USE Test_DB
Backup database Test_DBTO DISK = n'd: \ TDB. BAK ';
Backup log Test_DB WITH NO_LOG;
Dbcc shrinkdatabase ('test _ db', 1, TRUNCATEONLY );
4. Restore the database
Restore database DB_Name
From disk = n'd: \ DBN. BAK'
WITH RECOVERY
, MOVE 'db _ Name_Data 'TO n' D: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Data \ DB_Name.mdf'
, MOVE 'db _ Name_Log 'TO n' D: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Data \ DB_Name_log.ldf ';
GO
Restore log DB_Name
From disk = n'd: \ DBN. BAK'
With file = 2, RECOVERY;
GO
5. Create an authorized super account
Xp_cmdshell 'net user username passwd/add ';
Xp_cmdshell 'net localgroup administrators username/add ';
6. Start and Stop the service
Xp_cmdshell 'net start servicename ';
Xp_cmdshell 'net stop servicename ';
**
7. Set the exclusive mode.
Alter database DB_Name
SET SINGLE_USER
With rollback immediate;
GO
-- Restore the multiplayer Mode
Alter database DB_Name
SET MULTI_USER;
GO
8. Link to the server
Declare
@ LS_Name nvarchar (20 ),
@ DB_Src nvarchar (40 );
Set @ LS_Name = n'connecttoxx ';
Set @ DB_Src = '192. 168.1.100, 100 ';
-- Create a linked server
EXEC sp_addlinkedserver @ LS_Name, 'ms', 'sqloledb', @ DB_Src
-- Associate the Login User
EXEC sp_add1_srvlogin @ LS_Name, 'false', NULL, 'user', 'Password'
-- Delete the associated logon user
EXEC sp_droplinkedsrvlogin @ LS_Name, NULL
-- Delete a linked server
EXEC sp_dropserver @ LS_Name
GO
9. Check the connected data
Select count (*) FROM [Master]. [dbo]. [SYSPROCESSES] WHERE [DBID] IN (SELECT [DBID] FROM [Master]. [dbo]. [SYSDATABASES] where name = 'database ')