SQL database operations

-- Pay special attention

-- Please follow the steps and do not follow the previous steps.
-- Otherwise, your database may be damaged.

-- Generally, steps 4 and 6 are not recommended.
-- Step 2 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)

Modify database user password:
Exec sp_password 'old password', 'new password', 'username'

 

Add User:
Exec sp_addlogin 'user', 'Password', 'his library'
Go
Use his library
Exec sp_adduser 'user'

 

/* -- Example description
In the database pubs, create a role r_test with all permissions for table jobs and select permissions for table titles.
Then a login l_test is created, and the user account u_test is created for login l_test in the database pubs.
At the same time, add user account u_test to role r_test so that it can obtain the same permissions as role r_test through permission inheritance.
Finally, the Select permission for table titles in user account u_test is denied by using the deny statement.
After using l_test to log on to the SQL server instance, it only has all permissions for table jobs.
--*/

Use pubs

-- Create role r_test
Exec sp_addrole 'r _ Test'

-- Grant r_test all permissions on the jobs table
Grant all on jobs to r_test
-- Grant the role r_test the select permission on the titles table.
Grant select on titles to r_test

-- Add login l_test, set password to PWD, default database to pubs
Exec sp_addlogin 'l _ test', 'pwd', 'pubs'

-- Add the security account u_test to the database pubs for login l_test
Exec sp_grantdbaccess 'l _ test', 'U _ Test'

-- Add u_test as a member of role r_test
Exec sp_addrolemember 'r _ test', 'U _ Test'

-- Deny the select permission of the security account u_test on the titles table
Deny select on titles to u_test

/* -- After completing the preceding steps, log on with l_test to perform all operations on the jobs table, but cannot query the titles table. Although the role r_test has the select permission on the titles table, however, the Select permission for titles has been explicitly denied in the security account, so l_test has no select permission for the titles table --*/

-- Delete a security account from the database pubs
Exec sp_revokedbaccess 'U _ Test'

-- Delete login l_test
Exec sp_droplogin 'l _ Test'

-- Delete role r_test
Exec sp_droprole 'r _ Test'