Creating a user database
Create Database database_name On equipment _1 = Size_1, // Units: M equipment _2 = Size_2, "" log on log device = Log_Size [With Override] // use when creating a database and transaction logs on the same device that option [For Load] // disable user access until the load or restore operation is complete database of examples:
Create Database test_db On data_dev = 100, // Units: M Index_dev = 50 Log on log_dev = 30 Description
- the log on a separate device, help to improve database performance
- illustrates:Data_dev Index_dev log_dev
- If the database and logs on the same device, you can not achieve an incremental backup
- System and Default segment usually reduced to a range of devices, such as Default section and remove the device Index_dev System section on creating a new segment, used to store specialized database objects.
- Change the database owner
- Extending Database
Extended database space alter database database name expansion of space on the device name = @ Unit: M
If the expansion device for the database is new, System and Default section will automatically expand to the device.
Extended transaction log to the new device sp_logdevice database name, device name
Example: In addition to expand the database 5M, for storing log
alter database my_db on my_dev = 5 go sp_logdevice my_db, my_dev go
- Delete Database
Typically there is a system administrator to create user database, the default owner is dbo. System process sp_changeddbowner can change the owner of a relational database, it must be changed in case of a database by a database administrator to perform the primary relationship. The syntax is as follows:
sp_changeddbowner login_name [, True]
where the parameter True half for permission to pass to the new owner.
drop database database name You must remove all databases on it before removing the device, remove the device command is:
Log files are used to record database every time you modify an active file. SQL Server Each database has its own log file, the system table syslogs, also called the transaction log. The transaction log is to undo the transaction and recovery when the transaction fails basis. In some cases, the transaction log is more important than the data itself.
What is a transaction?
Modify the database transaction is performed in units. A transaction is a sequence of operations, these operations are either all do-or-not, it is an indivisible unit of work. Any transaction with the following features.
- atomic execution (Atomic)
- to maintain data consistency (Consistency)
- mutual isolation (Isolation)
- The effect of persistent (Durability)
Four features of the above transaction is called a transaction ACID guidelines.
Transactions during operation, Sybase ASE to the transaction began, the transaction ends and insert the database, deleting and updating each operation is stored as a log record to the transaction log. First update transaction in the database buffer (memory) and will be used to record the operation buffer respectively active data page (data page) and log pages (log page). When running to commit tran when the log buffer is written to disk Top start, then the data from the buffer to disk pages that follow the “first and logs (write_ahead log)” principle, thus ensuring that the case of failure by logging can maximize recovery. Recovery must undo transaction failure has not submitted completed transactions if there is still not written to the database from the buffer device, but also to re-run the transaction. Appendix: Creating and loading a database instance use master
use master declare @vdevno int select @vdevno=max(convert(tinyint,substring(convert(binary(4),d.low),v.low,1)))+1 from master.dbo.sysdevices d,master.dbo.spt_values v where v.type=’E’and v.number=3 declare @v_str char(2) select @v_str=convert(char(2),@vdevno) print @v_str disk init name=”TESTDBDATA”, physname=”D:\testdb\TestDBData.dat”, vdevno=@vdevno+1, size=153600 disk init name=”TESTDBINDEX”, physname=”D:\testdb\TestDBIndex.dat”, vdevno=@vdevno+2, size=102400 disk init name=”TESTDBLOG”, physname=”D:\testdb\TESTDBLOG.dat”, vdevno=@vdevno+3, size=76800 create database TESTDB on TESTDBDATA=300,TESTDBINDEX=200 log on TESTDBLOG=150 use TESTDB execute sp_addsegment indexdev,TESTDBDATA,TESTDBINDEX execute sp_dropsegment “default”,TESTDBDATA,TESTDBINDEX execute sp_dropsegment system,TESTDBDATA,TESTDBINDEX use master load database TESTDB from ‘d:\bak\testdb_backup.dmp’ online database TESTDB