Backup DatabasePermissions Required to Take Database Backup in SQL Server

In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.

Error Message

Msg 262, Level 14, State 1, Line 1

BACKUP DATABASE permission denied in database 'DatabaseName'.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'

GO

CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin

GO

EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'

GO

EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'

GO

Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role can take a full backup of database using either:-

• Using SQL Server Management Studio

• Using TSQL scripts

Let us take a look at both the above mentioned options to perform a full backup of a database in SQL Server.

Take Full Backup of a Database Using SQL Server Management Studio

1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.

2. Expand Databases node and then right click the database which you want to take a full backup and point to Tasks, and then click Back Up… option as shown in the below snippet to open up Back Up Database dialog box.

3. In Back Up Database Dialog box, in Database List box, verify the database name. Optionally you can even select a different database to backup. You will be able to perform a Full Backup of a database which is FULL, BULK_LOGGED or Simple recovery model.

4. In Backup type list box select Full. Once you have taken a full backup of a database then you can perform Differential backup or Transactional log backups. However, if you database is in Simple recovery model you will not be able to take a Transactional log backup. This is by design from Microsoft.

5. If you want to perform a Copy-Only backup of a database then choose Copy-Only Backup option in Back Up Database Dialog box. Using copy-only backup option you can take a full, differential or transactional log backup which is independent of the sequence of convential SQL Server backups. The copy-only backup option was introduced in SQL Server 2005.

6. In Backup Component select Databases and in Backup set leave the default Name and optionally enter Desciption. Leave the default value as 0 days for Backup set will expire after option.

7. Click the Add button to select the file or backup device for the backup destination as shown in the below snippet and click OK to return to Backup Database Dialog box.

8. Once you have completed the General settings you will be able to see a screen similar to below snippet.

9. Select Options in the Select a page pane to view the advanced options. In Overwrite Media choose Back up to the existing media set and Append to the existing backup set option as shown in the below snippet.

10. Under Reliability section, you can select the checkbox Verify backup when finished option. By selecting this option you can make sure the database backup is good.

11. Under Compression section, you can select the Compress backup from the drop down list as shown in the below snippet. Microsoft introduced Database Backup Compression Feature in SQL Server 2008 for Enterprise Edition.

12. To generate TSQL Script for the database backup click Scripts and choose Script Action to your choice from the different options which are available as shown in the snippet below.

13. Once the database is successfully backed up you will get a popup message similar to the one shown in below snippet.

How to Create Full Database Backup Using TSQL Script

The following example TSQL script can be used to take the full back up of AdventureWorks database.

DECLARE @BackupDatabaseName          SYSNAME DECLARE @DatabaseBackupLocation      NVARCHAR(120) 

SET @BackupDatabaseName = 'AdventureWorks'SET @DatabaseBackupLocation = 'C:\DBBackups\AdventureWorks.BAK'

/* Database Backup Script Using Compression Feature of SQL Server 2008 */BACKUP DATABASE  @BackupDatabaseNameTO  DISK = @DatabaseBackupLocationWITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */RESTORE VERIFYONLYFROM DISK = @DatabaseBackupLocationGO

Restoring DatabaseStep 1: Start SQL Server 2012:

Step 2: Expand the database and select database => right-click on selected database => now click on Task => then click on Restore =>  then click on database:

Step 3:  The Restore dialog box appears to restore the database backup:

Step 4: Now verify the database or select a different database in database list box for the destination database:

Step 5: Now specify the source of the database and the date and time of the database; to choose the date of the database click on Timeline:

Step 6: A new dialog box appears. There are two options for choosing the backup database. One is for the last backup taken and the second is a specific data and time. Choose the one approriate for you then click on the "OK" Button:

Step 7: Now click on File. Here we can reallocate all files to folder:

Step 8: After that, click on Options; it provides many options like store options, tail log backup before restore, server connection and prompt:

Step 9: Now click on the ok button to restore the database:

Finish

Importing

To begin launch SSMS by clicking SQL Server Management Studio from the Microsoft SQL Server program group.  Upon launching SSMS you will be prompted for a connection; connect to the Database Engine. 

Locate the MSSQLTipsSSISTutorial database in the SSMS Object Explorer (create the database if necessary); if the Object Explorer isn't visible click the View menu then select Object Explorer:

Right click on the MSSQLTipsSSISTutorial database in the Object Explorer, select Tasks, then Import Data from the context menu to launch the Import Wizard.  Click Next to advance past the Welcome dialog (if shown).  In the following sections we will walk through the wizard step-by-step.

Choose a Data Source

The Choose a Data Source dialog allows you to specify the source of your data.  Fill in the dialog as shown below to specify an Excel spreadsheet as the data source:

Click Next to proceed to the Choose a Destination dialog.

Choose a Destination

The Choose a Destination dialog allows you to specify the destination data source for the data you are importing.  Since we are running the Import wizard, the dialog will be displayed with the values already filled in as shown below (based on the database you right clicked to start the Import wizard):

Click Next to proceed to the Specify Table Copy or Query dialog.

Specify Table Copy or Query

The Specify Table Copy or Query dialog allows you to choose whether to import data by selecting tables and/or views from the data source or specifying a query to extract data.  Select Copy data from one or more tables or views as shown below:

Click Next to proceed to the Select Source Tables and Views dialog.

Select Source Tables and Views

The Select Source Tables and Views dialog allows you to select the tables and views that you want to import.  For our demonstration we are going to select the DimGeography table as shown below:

You can click the Preview button to view the first 100 rows of the data in the data source as shown below:

You can click the Edit Mappings button (on the Select Source Tables and Views dialog) to review the column mappings from the data source to the data destination as shown below.  If the table does not exist in the destination data source, you can also edit the mappings.

You can click the option to drop and recreate the table in the destination data source; by default this option is unchecked.  You can click Enable identity insert if the destination table has an identity column and the data source has values that you want to assign to the identity column.  If the table does not exist in the destination data source, the Create destination table radio button will be selected and the other radio buttons will be disabled.  If the table already exists in the destination data source,  the Create destination table radio button will be disabled and you can select either the Delete or Append options.

You can click the Edit SQL button to review and/or edit the SQL to create the table in the destination data source as shown below:

Click OK twice to return to the Select Source Tables and Views dialog, then click Next to proceed to the Save and Execute Package dialog.

Save and Execute Package

The Save and Execute Package dialog gives you options to perform the import operation and to create an SSIS package and save it to SQL Server or the file system as shown below:

For our purposes we will save the SSIS package.  Click Next to proceed to the Save SSIS Package dialog.

Save SSIS Package

The Save SSIS Package is invoked if you chose to save your import operation as an SSIS package on the Save and Execute Package dialog.  Fill in the dialog as shown below:

Click Next to proceed to the Complete the Wizard dialog.

Complete the Wizard

The Complete the Wizard dialog shows a summary of the options that you have chosen for the import operation as shown below:

Click Finish to execute the SSIS package.  You can open the imported table in SSMS and view it.  A portion of the table is shown below:

Exporting

First, connect to your WinHost database using SQL Server Management Studio.  For this tutorial, I am using the sample AdventureWorks database provided by Microsoft.  Once you have logged into your database, right click on it and selectTasks -> Export Data…

This will launch the SQL Server Import and Export Wizard.

Click on Next to continue.  Now, you will need to choose a Data Source.  You can leave the Data source as SQL Server Native Client 10.0.  Enter the Server name.  Check SQL Server Authentication and enter the User Name and Password for your database.  For the Database dropdown menu, select the name of your database.

Click on Next to continue.  In the Choose a Destination window, you can select a variety of formats to export to including Microsoft Access, Microsoft Excel, and another Microsoft SQL Server database.  For this tutorial, I will be showing you how to export your data to a flat file (i.e. .txt and .csv).

In the Destination dropdown menu, select Flat File Destination.  For theFile name, click on the Browse button, select Desktop and give the file a name.  You can choose either .txt or .csv format by clicking on the dropdown menu next to the file name.

You can leave the Locale as English (United States) and Code page as 1252 (ANSI – Latin I).  The Format will be Delimited and check Column names in the first data row.  You can set a Text qualifier if you’d like, but I’m not going to.

Click on Next to continue.  In the Specify Table Copy or Query window, checkCopy data from one or more tables or views.  If you check the other option, you can write your own SQL query (SELECT statement) to extract the data you want from two or more tables.

Click on Next to continue.  In the Configure Flat File Destination window, I will be drawing data from the Employees table.  In Source table or view, select[HumanResources].[Employee].  You can leave the Row delimiter as{CR}{LF} and the Column delimiter as Comma {,}

You may also want to spend some time looking at Edit Mappings.  For a flat file, you can choose not to include certain columns to be exported by selecting theDestination column name and choosing <ignore> as shown below.

This tool is actually more powerful when you are exporting to an existing file that already has column names in place as you can map a column name from the Source file to a different column name in the Destination file.

For example, you have an Excel file with a column named BEID.  You can map BusinessEntityID to BEID.  The data will be populated in the BEID field in Excel.  Click on Next to continue.  This will bring up the Run Package page.

Click on Next to continue.  The Wizard is complete and you can click on Finish.

The Wizard will now run through a series of checks prior to exporting the data.   If everything goes smoothly, you should get the screen below.

Server RolesA new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level/scope permissions to these roles. DBA's have always had the ability to create user defined database roles which act as a security layer at the database level, but we've never been able to create roles at the server level until SQL Server 2012.

In this tip I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.

What Permissions Can Be Assigned

First, to view the list of permissions that can be assigned to a user defined server role run the following query:

USE master GOSELECT * FROM sys.fn_builtin_permissions(DEFAULT) WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') ORDER BY class_desc, permission_nameGO

Create a Server Role in T-SQL

To create a server role called "juniordba" use the following:

USE masterGOCREATE SERVER ROLE juniordba

Next we will create a login called Brady and then add it to the new juniordba role that was created:

USE master GOALTER SERVER ROLE juniordba ADD MEMBER Brady

We haven't added any permissions to the server role, so Brady shouldn't have access. To test this we can login as Brady and run the following query:

SELECT * FROM sys.dm_exec_connections

As you can see we get the following error message:

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Next, I'll assign permissions to the server role that will allow Brady to run DMV's.

GRANT CONNECT SQL TO juniordba GRANT VIEW ANY DATABASE TO juniordba GRANT VIEW ANY DEFINITION TO juniordba GRANT VIEW SERVER STATE to juniordba

After running the query again using login Brady we get the following:

Create a Server Role in SSMS

In SSMS, drilldown into the server and open Security, right click Server Roles and click New Server Role...

In the New Server Role window, name the server role, choose securables and assign the permissions related to the securables selected. In this example, I have named my server role juniordba, selected Servers as the securable and granted connect to sql, view any database, view any definition, and view server state. I have also denied the shutdown permission.

Next, click on the next tab called Members. Here you will add the logins that you want to be associated with the new Server Role. In this example, I'll choose Brady.

The last tab, Memberships, will allow you to nest the Server Role with default Server Roles. In this example, we wanted to create one from scratch so we'll ignore this and click OK.

In SSMS, you can now see your new Server Role: