Database Administration
Controlling privileges and permissions to database users
Monitoring database performance
You can also open Activity Monitor at any time by pressing CTRL+ALT A.
Example
The following example backs up the complete AdventureWorks2008R2 database to disk, by using FORMAT to create a new media set. USE AdventureWorks2008R2; GO BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak' WITH FORMAT, MEDIANAME = 'Z_SQLServerBackups', NAME = 'Full Backup of AdventureWorks2008R2'; 1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. 2. Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database. 3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears. 4. In the Database list box, verify the database name. You can optionally select a different database from the list. 5. You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE). 6. In the Backup type list box, select Full. 7. Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. The same could be said in reverse for Restore instead of Backup: --Restore the full database backup (from backup set 1). RESTORE DATABASE AdventureWorks2008R2 FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2FullRM.bak' WITH FILE=1, NORECOVERY; To restore a full database backup: 1. After you connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. 2. Expand Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database. 3. Right-click the database, point to Tasks, and then click Restore. 4. Click Database, which opens the Restore Database dialog box. 5. On the General page, the name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box. 6. In the To a point in time text box, either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box. 7. To specify the source and location of the backup sets to restore, click one of the following options: From databaseEnter a database name in the list box. From device Click the browse button, which opens the Specify Backup dialog box. In the Backup media list box, select one of the listed device types. To select one or more devices for the Backup location list box, click Add. After you add the devices you want to the Backup location list box, click OK to return to the General page. 8. In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Any backups that depend on a deselected backup are deselected automatically. 9. To view or select the advanced options, click Options in the Select a page pane. 10. In the Restore options panel, you can choose any of the following options, if appropriate for your situation: Overwrite the existing databasePreserve the replication settings Prompt before restoring each backupRestrict access to the restored database 11. Optionally, you can restore the database to a new location by specifying a new restore destination for each file in the Restore the database files as grid. 12. The Recovery state panel determines the state of the database after the restore operation. Database SecurityPart of database security is computer security. There are many protocols for encrypting data that is being transmitted. There are many types of firewalls, routers and security software for blocking potential threats such as viruses and hackers from destroying or steeling your information. Security is the 2nd option inside SQL Server Management Studio object explorer which restricts different database user's rights to database objects, enables and disables user instances, login permissions, and other security precautions.