Database administrator's activities include:How to transfer ( move or copy) databases with SQL Server
To use the Copy Database Wizard, you must specify the following:
The source server where the databases to be copied reside.
The destination server to which the databases are to be copied or moved.
The databases to be moved or copied.
The name of a target database, if different than the name of the source database.
The source database name can be use for the copied or moved database only if name conflicts do not exist on the destination server. If name conflicts exist, you must resolve them manually on the destination server before you can use the source database name there.
Other objects to be copied or moved; for example, logins, shared objects from the master database, jobs and maintenance plans, and user-defined error messages.
The schedule for the copy or move operation, if you want it to run at a later time.
The following replication models are used for replicating databases in SQL Server
Peer-to-peer model, Central publisher model, Central publisher with remote distributor model, Central subscriber model, publishing subscriber
model. Replication is one of the options in SQL Server Management Studio as seen below.
SQL Server Management Studio is where most of the functionality of SQL server is location and it includes options: Databases, Security, Server Objects, Replication, Management, SQL Server Agent Maintaining database and ensuring its availability to users As with all files, maintaining databases includes making sure that data is backed up, both databases and servers and defragged, computer components are in good working, updates and applied and antivirus software is kept current.
Controlling privileges and permissions to database usersPrivileges and permissions to databases can be assigned both in the Roles section of SQL Server Server Management Studio as well as inside SQL code.
Monitoring database performance
To open Activity Monitor in SQL Server Management Studio
1. On the SQL Server Management Studio standard toolbar, click Activity Monitor.
2. In the Connect to Server dialog box, select the server name and authentication mode, and then click Connect.
You can also open Activity Monitor at any time by pressing CTRL+ALT A.
To open Activity Monitor in Object ExplorerIn Object Explorer, right-click the instance name, and then select Activity Monitor.
To open Activity Monitor when opening SQL Server Management Studio
1. On the Tools menu, click Options.
2. In the Options dialog box, expand Environment, and then select General.
3. In the At startup box, select Open Object Explorer and Activity Monitor.
4. To activate the changes, close and reopen SQL Server Management Studio.
Database backup and recovery SQL Server Databases may be backed up with either SQL code or with the SQL Server Management Studio
A. Backing up to a disk device
The following example backs up the complete AdventureWorks2008R2 database to disk, by using
to create a new media set.
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
MEDIANAME = 'Z_SQLServerBackups',
NAME = 'Full Backup of AdventureWorks2008R2';