Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

Basic Sql Server Administering

sql server administering basic administering combobox

  • Please log in to reply
No replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 27 May 2012 - 01:40 AM

Basic SQL Server Administering Tasks

This tutorial will guide you on how to do basic administering with SQL Server 2008 R2 installation. Since this tutorial aims to developer, we only will address basic administering tasks that a developer needs to maintain their development SQL Server. Do not expect this tutorial to cover advance SQL Server administering features.


Overview
From my experience, coders who want to work with SQL Server should at least know how to perform the following tasks:
  • Creating Database
  • Deleting Database
  • Simple Database Backup
  • Simple Database Restore
  • Detaching Database
  • Attaching Database
  • Create New User
  • Manage User Privilege

This tutorial series will only use Microsoft SQL Server Management Studio (SSMS) to perform the administering tasks. Note that we will only use the Express version of it (Microsoft SQL Server Management Studio Express or SSMSE). This is to make sure that everyone can try and practice first without needing to have SQL Server commercial license.


Creating Database
Of course before we can work with database we have to create the database. There are two ways to create a database.
  • Through GUI.
  • Through SQL Script. We will not address this option in this tutorial. It will be explained in the next installment.

Creating Database Using GUI
Here you will create a new database by using a very friendly dialog by specifying the database name along with its options. For coders, we don't need to specify any option except maybe the database files location.

  • Open SSMSE and login to your SQL Server Installation. In our tutorial we will login to "local/CODECALL".
    ManagementTools_Run02.png
  • In the left treeview panel, locate Database node, then right click on it. You will get pop up menu like shown in the following picture. Click on New Database... menu item.
    Part2_CreateDB_01.png

    You will be taken to New Database dialog. Visit this msdn page for details of this dialog.
  • In New Database dialog, enter the name for the new database. In this tutorial, let's create a new database named Test01.
    Part2_CreateDB_02.png
  • Usually we can get away by leaving the other options to their default values. However sometimes we need to store the database files in other location instead in the default one. If you need to do this, scroll right in the Database files table until you see Path column, like shown in the following picture.

    You can either directly type in the new location of the corresponding database file in the red highlighted area, or use folder selection dialog which will be pop up if you click on the ellipsis button.
    Part2_CreateDB_03.png Part2_CreateDB_04.png

    Do this for each database file that you want to store in custom location.
  • Click OK button to close the New Database dialog and create the new database. You will see in the left treeview a new database node with caption of "Test01". It's our new database.
    Part2_CreateDB_05.png


Deleting Database
Sometimes we need to delete our database. It might be because we want to start new one with the same name, or perhaps we have finished with it and need to clean up our system from it. Like its counterpart, creating database, there are two ways to create a database.
  • Through GUI.
  • Through SQL Script. Also like its counterpart, we will not address this option in this tutorial, we'll explain this in the next installment.

Deleting Database Using GUI

  • Open SSMSE and login to your SQL Server Installation. In our case we login to "local/CODECALL".
    ManagementTools_Run02.png
  • In the left treeview panel, locate Database node, expand it if it's collapsed. Then find the database you want to delete, then right click on it. You will get pop up menu like shown in the following picture. Click on Delete menu item.
    Part2_DeleteDB_01.png

    You will be taken to Delete Object dialog. See this msdn page for detail reference about this dialog.
    Part2_DeleteDB_02.png
  • In the Delete Object dialog, usually you only have to click the OK button to confirm that you really want to detete the corresponding database, close the dialog, and delete the selected database.


Simple Database Backup

  • Open SSMSE and login to your SQL Server Installation. In our case we login to "local/CODECALL".
    ManagementTools_Run02.png
  • In the left treeview panel, locate Database node, expand it if it's collapsed. Then find the database you want to backup, then right click on it. You will get pop up menu like shown in the following picture. Highlight on menu item Tasks to open its submenu, and then click on Backup... menu item.
    Part2_BackupDB_01.png

    You will be taken to Back Up Database dialog. See this msdn page for full detail about this dialog.
    Part2_BackupDB_02.png
  • For our use (as coders) we only interested in the following items in Back Up Database dialog.
    • Description of the backup. Give good description for the backup so it will easier for you to manage later.
    • Make sure the generated backup file won't be expired by setting the value of "Backup set will expire: After" to 0 days. This makes sure that the backup file will not be reused/overwritten by SQL Server.
    • Destination file. You can use the autogenerated default value, but you may want to adjust this.
    Part2_BackupDB_03.png
  • Click OK button to start the backup process. You will get a notification just like in the following picture to indicate that the backup process has been carried out successfully.
    Part2_BackupDB_04.png


Simple Database Restore

  • Open SSMSE and login to your SQL Server Installation. In our case we login to "local/CODECALL".
    ManagementTools_Run02.png
  • In the left treeview panel, locate Database node, expand it if it's collapsed. Then find the database you want to restore to, then right click on it. You will get pop up menu like shown in the following picture. Highlight on menu item Tasks to open its submenu, and highlight on Restore to open its submenu, then click on Database... menu item.
    Part2_RestoreDB_01.png

    You will be taken to Restore Database dialog. See this msdn page for full detail about this dialog.
    Part2_RestoreDB_02.png
  • In Restore Database dialog, we really have to pay attention on the source option.
  • If you choose From Database, you will be presented with a list of backups known for database selected in the combobox. You can then select which backup you want to restore before clicking OK button to start the restore process.
  • If you choose From Device you will need to specify file(s) which contain the backup(s). To do this:
  • Click on the ellipsis button to open Specify Backup dialog.
    Part2_RestoreDB_03.png
  • Select File for the Backup Media.
  • Click Add button to add a source file to Backup Location. This will open a file selection dialog with title of Locate Backup File just like shown below.
    Part2_RestoreDB_04.png
  • Browse and select file containing the backup, then click OK button.
  • Previously selected file will be added to the backup location list.
  • You can inspect its content by clicking Contents button.
    Part2_RestoreDB_05.png
    With this dialog, you can examine the file whether it contains the backup you really want.
  • Click OK button to close Specify Backup dialog. You should now presented with available backups of the devices.
  • Select a backup then click OK button to start restore process.

Note that this option allows you to use backup file created from other database, even if the database has no relation the restore target. You must be more careful when using this option. Make sure you always inspect the content before proceed with restore process.
[/list]

Detaching Database

Detaching database is to remove the database from a SQL Server instance but still leaving its files intact. It is very useful when moving a database from a SQL Server instance to another, or simply to move the location of the files.

To detach a database, perform the following steps.
  • Open SSMSE and login to your SQL Server Installation. In our case we login to "local/CODECALL".
    ManagementTools_Run02.png
  • In the left treeview panel, locate Database node, expand it if it's collapsed. Then find the database you want to detach, then right click on it. You will get pop up menu like shown in the following picture. Highlight on menu item Tasks to open its submenu, then click on Detach... menu item.
    [Part2_DetachDB_01.png]

    You will be presented with Detach Database dialog like the following. See this msdn page to get full details of this dialog.
    [Part2_DetachDB_02.png]
  • While not really necessary, I suggest to add check marks both on Drop Connections and Update Statistics columns.
  • Click OK button to detach the database.
  • You will see in the left treeview panel that the database now is gone. But if you check its files, they will still be left where they are.


Attaching Database

Attaching is, of course, the reverse of detaching. Attaching database means to add a database to an instance of SQL Server from a set of existing database file(s). Combined with detaching, you can use attaching to move a database from one instance to the other, or to simply change the location of the files.

To attach database, perform the following steps. Of course you must already have a detached database files. As an example, we will use detached files we have got from the detaching example previously (Test01 database).

  • Open SSMSE and login to your SQL Server Installation. In our case we login to "local/CODECALL".
    ManagementTools_Run02.png
  • In the left treeview panel, locate Database node, then right click on it. You will get pop up menu like shown in the following picture. Click on Attach... menu item.
    Part2_AttachDB_01.png

    You will be presented with Attach Databases dialog like the following. See this msdn page to get full details of this dialog.
    Part2_AttachDB_02.png
  • Click button Add... to open Locate Database Files dialog then browse and select the file of the database you want to attach. As an example, we are going to browse to where the .mdf file of Test01 database we previously detached, like shown below.

    Part2_AttachDB_03.png

  • Click button OK to close Locate Database Files dialog and add the selected file to the Databases to attach table. Like shown below. The database details table will show you all files associated with the database you select to attach.

    Part2_AttachDB_04.png

  • Click OK button to attach the database.
  • You will see in the left treeview panel that there is a new database.

  • 0





Also tagged with one or more of these keywords: sql server administering, basic administering, combobox

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download