How to create a simple database backup using SQL Server Management Studio (SSMS)

By: Graham Okely

Problem

You are brand new to SQL Server and you need to create a SQL Server database backup, but you have no idea what to click on.  In this tip we walk through the steps to create a simple backup using SQL Server Management Studio.

Solution

This tip guides you through the steps to make a simple one-off backup.

Step 1

Open SSMS and expand the Database tree as shown below and right mouse click on the database you wish to backup. Then move your mouse carefully over Tasks and then click on Back Up... as shown below.

Step 2

At this point pause and look at the options before you click.

When we see so many options, and there are more under the options tab, it is hard to know what to do so let's look at only the essential things. I have circled the essentials in red.

(If you wish to know more about the options page look at this page at Back Up Database (Options Page) at Microsoft. You may wish to visit this MSSQLTips backup tutorial as well.)

• First the source database is listed, confirm it is the database you wish to backup.
• Next the backup type is Full. This option will give us a full backup. There are other types, but let's leave it as Full.
• Notice the "Copy-only Backup" check box. Check it if you wish to use this option. You may be thinking, a backup is a copy of the database. Yes that is true however the copy it is referring to relates to a "chain" of backups. This is important if you do not wish to break the backup chain. My tip is to check that check-box. See tip 1772 by Atif Shehzad or Copy-Only Backups at Microsoft for more information.
• Next leave the "Database" as the component we wish to backup.  This will backup the entire database.
• Next unless you know the path and name displayed are what you want, click "Remove" to remove the default backup path and name. Note this does not remove that file if it exists it simply removes that path and file name from this dialog box.

Step 3

Next click on "Add..." and browse to a path you know has room for your backup. It is possible to enter a URL at this point. (\\Server\Drive\Path\File_Name)

Note that if at all possible you should place your backup into the usual location for backups. This means it is easy to find all your backups should you need to restore it and any automated clean up process will clean out your one-off backup.

Step 4

Enter the file name. I suggest the format "Database_Name_backup_YYYY_MM_DD.bak" as it is simple and follows the pattern of an automated SQL Server backup.

Once ready click OK.

Step 5

Here are the options, file path and name ready to backup.

It is ready to backup now. However at this point we could choose to click OK and back it up or click on the "Script" menu item. Clicking on Script will not run the backup, but produce a script for you.

(Note that tip 1070 by Greg Robidoux explains one TSQL method of performing backups.)

Let’s click on "OK" to do the backup. Notice the green circle icon indicates the backup percentage. Then it pops up a message once completed.

Click "OK" and the screen goes back to normal.

If we browse to that location we should see the backup.

Well done on a successful backup using the SSMS GUI.

Creating a Visual Studio Database Project for an existing SQL Server Database

By: Rick Krueger

Learn how SQL Server’s extended events help prevent performance challenges

Problem

Continuous Integration has become standard practice for many development projects. Every time a developer checks in a piece of code, the entire project or solution is built and deployed to an environment to make sure it didn't 'break the build'. Developers have been using source control for a long time, but there just wasn't a good way to get database objects under source control. As a result, there are many applications out there with the 'application code' in source control, but the database code is unmanaged. The preferred route for getting the database into source control at many shops will be Database Projects. If you are in this position, and your shop is using Visual Studio, there is a relatively easy path to get your databases into Database Projects, and ultimately source control. 'Reverse engineering' your database into a Database Project will help you start leveraging the features of Visual Studio Database Projects available in VS Premium and VS Ultimate (including source control, continuous integration, and code analysis).

Solution

We can use Visual Studio to run the Import Database Wizard and populate an empty Database Project.

Download the sample AdventureWorks database (AdventureWorks2008_Database.zip was used for this example) fromCodePlex, and attach the AdventureWorks2008 database to follow along with this tip.

NOTE: The screenshots are from Visual Studio 2010, but the same general process will work with Database Projects in Visual Studio 2005/08. Visual Studio 2005/08/10 all use VSDBCMD.exe for deploying Database Projects, while 2012 went to a new method for deployment.

Create an Empty Database Project

1. Open Visual Studio 2010.
2. From the File Menu, select New > Project...
3. In the Installed Templates tab, expand Database > SQL Server > Advanced
4. Select the SQL Server 2008 Database Project, and enter the Name of your database
   
   
   
5. Click OK and an empty Database Project will be created.

Run the Import Database Wizard

1. From the Project Menu, select Import Database Objects and Settings...
2. Click New Connection...
3. Select your local Server name, credentials, and database name. TIP: Click Test Connection here before clickingOK.
   
   
   
4. Click Start. I recommend keeping the provided defaults, unless you understand the implications of not accepting the defaults.
5. You will see a progress bar as Visual Studio inspects the selected database, and populates the Database Project with all of the database objects.
6. Click Finish.
   
   
   

Next Steps

Congratulations! Your database has been imported into a Database Project, and you should now start managing your database from Visual Studio and make sure you get source control set up. You will also want to learn how to deploythose changes from Visual Studio to SQL Server. I have been using Database Projects for a long time now, and I haven't come across many limitations. There are times I still prefer to write my code in SQL Server Management Studio, and then import the code into Visual Studio, but that is mostly a personal preference.

• Learn more about Visual Studio Database Projects
• Check out Adding Tables To A Database Project
• Visit Adding Additional Objects To A Database Project
• Use the MergeScripts Extension to organize and consolidate table scripts

select, insert, update, delete statements using Store Procedure in SQLServer

Here, we will see how to create select, insert, update, delete statements using stored procedure. Let's take a look at a practical example. We create a table.

Creating Table

CREATE TABLE employee(

    id          INTEGER NOT NULL PRIMARY KEY,

    first_name  VARCHAR(10),

    last_name   VARCHAR(10),

    salary      DECIMAL(10,2),

    city        VARCHAR(20),   

 )

Now insert some values in the table and using select statement to select a table.

 INSERT INTO employee VALUES (2, 'Monu',  'Rathor',4789,'Agra');

 GO

 INSERT INTO employee VALUES (4, 'Rahul' ,  'Saxena',   5567,'London');

 GO

 INSERT INTO employee VALUES (5, 'prabhat',  'kumar',  4467,'Bombay');

 go

 INSERT INTO employee VALUES (6, 'ramu',  'kksingh',  3456, 'jk');

 go

 select * from employee

Table looks like this.

Figure 1

Stored procedure for Select, insert, update, delete

Here, we create a stored procedure for select,insert,update,delete statements to select the data from the table.

Alter PROCEDURE MasterInsertUpdateDelete

(

    @id         INTEGER,

    @first_name  VARCHAR(10),

    @last_name   VARCHAR(10),

    @salary      DECIMAL(10,2),

    @city        VARCHAR(20), 

    @StatementType nvarchar(20) = ''

)

AS

BEGIN

IF @StatementType = 'Insert'

BEGIN

insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name,  @last_name,  @salary, @city)   

END

IF @StatementType = 'Select'

BEGIN

select * from employee

END 

IF @StatementType = 'Update'

BEGIN

UPDATE employee SET

            First_name =  @first_name, last_name = @last_name, salary = @salary,

            city = @city

      WHERE id = @id

END

else IF @StatementType = 'Delete'

BEGIN

DELETE FROM employee WHERE id = @id

END

end

Now press F5 to execute the stored procedure.

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right click select execute stored procedure...

Figure2

Execute procedure window will be open.

Figure3

Now for insert we fill the data in required field.

StatementType=insert

Figure4

Click on the ok Button. and check in the employee table with following inserted data.

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Update'

Figure6

Click on the ok Button. and check in the employee table with following updated data where id is 7.

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Delete'

Figure8

we delete record from table which has id=2

Click on the ok Button. and check in the employee table with following deleted data where id is 2.