MS SQL Server DBA Checklist - High Availability Best Practices

MS SQL Server DBA Checklist - High Availability Best Practices

-- Physically protect your SQL Servers from unauthorized users.
-- Physically document all of your SQL Server instances. Incorporate effective change management.
-- Always use a RAIDed array or SAN for storing your data.
-- Use SQL Server clustering, database mirroring, or log shipping to provide extra fault tolerance.
-- Replication is not an effective means to protect your data.
-- Ensure that your entire IT infrastructure is redundant. It is only as strong as its weakest link.
-- Always use server-class hardware, and standardize on the same hardware as much as possible.
-- Use hardware and software monitoring tools so you can quickly become aware of when problems first arise.
-- After testing, apply all new service packs and hot fixes to the OS and SQL Server.
-- Cross-train staff so that there are multiple people who are able to deal with virtually any problem or issue.

MS SQL Server DBA Checklist - High Availability - Log Shipping

-- If you don’t currently employ clustering or database mirroring for your SQL Servers because of cost, consider employing log shipping to help boost your high availability. It provides reasonably high availability at low cost.
-- If you take advantage of SQL Server log shipping capability, you will want to keep the log shipping monitoring service on a SQL Server of its own, not on the source or destination servers participating in log shipping. Not only is this important for fault tolerance, but because the log shipping monitoring service incurs overhead that can affect the performance of the source and destination servers.
-- Monitor log shipping daily to ensure that is working successfully.
-- Learn what you need to know to fix log shipping if synchronization is lost between the production and backup databases.
-- Document, and test your server recovery plan, so you will be ready in case of a server failure.

MS SQL Server DBA Checklist - General – Replication

-- Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning.
-- Ideally, publishers, distributors, and subscribers should be on separate physical hardware.
-- Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice.
-- Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed.
-- Use default replication settings, unless you can ensure that a non-default setting will actually improve replication performance or other issues. Be sure that you test all changes to ensure that they are as effective as you expect.
-- Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes.
-- Periodically, validate data between publishers and subscribers.
-- Regularly monitor replication processes and jobs to ensure they are working.
-- Regularly monitor replication performance, and performance tune as necessary.
-- Add alerts to all replication jobs so you are notified of any job failures.

  

MS SQL Server DBA Checklist - High Availability - SQL Server Mirroring

-- The principal database and the mirror database should be on separate physical hardware, and ideally, in different physical locations.
-- The witness server should be on separate physical hardware, and be on a separate network (best if at a third location).
-- Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect performance of the production database being mirrored.
-- Use high availability mode whenever possible, and high performance mode only when required.
-- While a fast connection is not required between mirrored servers, the faster the connection, and the better quality the connection, the better.
-- You will want to optimize the performance of the mirrored database as much as possible to reduce the overhead caused by the mirroring process itself.
-- Thoroughly test database mirroring before putting it into production.
-- Monitor database mirroring daily to ensure that it is working properly, and is meeting performance goals.
-- Develop a formal operational and recovery procedure (and document) to support mirroring. Periodically test the failover process to ensure that it works.

MS SQL Server DBA Checklist - High Availability – Clustering

-- Detailed planning is critical to the success of every SQL Server cluster installation. Fully plan the install before performing the actual install.
-- An expensive cluster is of little value if the supporting infrastructure is not also fault tolerant. For example, don’t forget power redundancy, network redundancy, etc.
-- Run only a single instance of SQL Server per node. Whether you have two or eight nodes in your cluster, leave at least one node as a failover node.
-- Cluster nodes must not be domain controllers, and all nodes must belong in the same domain and should have access to two or more domain controllers.
-- Since clustering is not designed to protect data (only SQL Server instances), the shared storage device used by the cluster must incorporate fault tolerant technology. Consider log shipping or mirroring to further protect your production databases.
-- When initially installing Windows and SQL Server Clustering, be sure that all drivers and software are up-to-date, including the latest service packs or hot fixes.
-- Each node of a cluster should have identical hardware, drivers, software, and configuration settings.
-- Once the cluster has been installed, test it thoroughly for every possible failure scenario.
-- Do not run antivirus or antispyware on a SQL Server cluster.
-- Monitor active production clusters on a daily basis, looking for any potential problems. Periodically test failover on production servers to ensure all is working well.
-- Once you have a stable SQL Server Cluster running, be very leery about making any changes to it, whatsoever.

MS SQL Server DBA Checklist

MS SQL Server DBA Checklist - General DBA Best Practices

-- Join (or start) a local SQL Server users group 
-- Attend at least one professional conference each year.
-- Attend at least one training session each year.
-- Read at least four books on SQL Server each year.
-- Read the free e-book, How to Become an Exceptional DBA
-- Learn everything you can about your job, especially those areas that nobody else likes or wants to master.
-- Volunteer at your work for new and challenging tasks that will make you known throughout your organization.
-- Install SQL Server on a laptop or desktop computer at home and practice learning new aspects of SQL Serve.
-- Participate in SQL Server forums (asking and answering questions)
-- Become a Microsoft Certified IT Professional

MS SQL Server DBA Checklist - General - Day-to-Day

-- Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
-- Verify that all scheduled jobs have run successfully.
-- Confirm that backups have been made and successfully saved to a secure location.
-- Monitor disk space to ensure your SQL Servers won’t run out of disk space. For best performance, all disks should have 20% or more of free space.
-- Throughout the day, periodically monitor your server’s performance. Use System Monitor, Profiler, DMVs, or the SQL Server 2008 Performance Data Collector
-- Use Management Studio or Profiler to help monitor and identify blocking issues.
-- Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
-- Create SQL Server alerts to notify you of potential problems, and have them e-mailed to you. Take action as needed.
-- Take some time to learn something new as a DBA to further your professional development.

MS SQL Server DBA Checklist - General – Installation

-- Always fully document installs so that your SQL Server instances can easily be reproduced in an emergency.
-- If possible, install and configure all of your SQL Server instances consistently, following an agreed upon organization standard. Optionally use SQL Server 2008 Policy-based Management to enforce standards.
--. Don't install SQL Server services you don't use, such as Microsoft Reporting Services or Analysis Services, if you won’t be using them. -- For best performance of SQL Server running under Windows, turn off any operating system services that aren't needed.
-- For optimum SQL Server performance, dedicate your physical servers to only running a single instance of SQL Server, no other applications.
-- For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate volumns on your server to isolate potentially conflicting reads and writes.
-- If tempdb will be used heavily, also put it on its own separate array. In addition, pre-size tempdb to a size that will meet your server’s needs without having the need to autogrow. Divide the tempdb database into multiple files so that the number of files is equal to 50% to 100% of the number of CPU cores in your servers. Each physical file must be the same size.
-- Do not install SQL Server on a domain controller.
-- Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.

MS SQL Server DBA Checklist - General – Upgrading

-- Run the Upgrade Advisor on any database you intend to upgrade to identify potential problems.
-- Before performing an upgrade to SQL Server, thoroughly test your application in a test environment to ensure compatibility. Make any necessary changes before performing the upgrade.
-- Before you upgrade, be sure you have a plan in place to fall back to in case the upgrade is problematic.
-- While upgrading in place can work well, it is less risky to upgrade to new hardware with a fresh install of the OS and SQL Server.
-- If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases using UPDATE STATISTICS. This is because statistics are not automatically updated during the upgrade process. In addition, run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page counts.

MS SQL Server DBA Checklist - General – Security

-- Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers.
-- Only install required network libraries and network protocols on your SQL Server instances.
-- Minimize the number of sysadmins allowed to access SQL Server.
-- As a DBA, log on with sysadmin privileges only when needed. Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.
-- Assign the SA account a very obscure password, and never use it to log onto SQL Server. Use a Windows Authentication account to access SQL Server as a sysadmin instead.
-- When assigning permissions to users, only give them the minimum permissions they need to perform their jobs.
-- Use stored procedures or views to allow users to access data instead of letting them directly access tables.
-- When possible, use Windows Authentication logins instead of SQL Server logins.
-- Use strong passwords for all SQL Server login accounts.
-- Don’t grant permissions to the public database role.
-- Remove user login IDs who no longer need access to SQL Server.
-- Remove the guest user account from each user database.
-- Disable cross database ownership chaining if not required.
-- Never grant permission to the xp_cmdshell to non-sysadmins.
-- Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
-- Avoid creating network shares on any SQL Server.
-- Turn on login auditing so you can see who has succeeded, and failed, to login. In SQL Server 2008, you can use SQL Server Audit instead. -- Don’t use the SA account, or login IDs who are members of the Sysadmin group, as accounts used to access SQL Server from applications. -- Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
-- Remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. Before you do this on a clustered SQL Server, check Books Online for more information.
-- Run each separate SQL Server service under a different Windows domain account.
-- Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed. SQL Server setup will automatically configure service accounts with the necessary permissions for them to run correctly, you don’t have to do anything, with one possible exception. You may want to give the SQL Server service account SE_MANAGE_VOLUME_NAME rights so that instant file initialization can be used for SQL Server 2005/2008. This is only required if the SQL Server service account is not a member of the local administrator’s group.
-- When using distributed queries, use linked servers instead of remote servers.
-- Do not browse the web from a SQL Server.
-- Instead of installing virus/antispyware protection on a SQL Server, perform scans from a remote server during a part of the day when user activity is less.
-- Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
-- Encrypt all SQL Server backups with a third-party backup tool, such as SQL Backup Pro. If you have SQL Server 2008, Enterprise Edition, you can use Transparent Data Encryption to ensure encrypted backups.
-- Only enable C2 auditing or Common Criteria compliance if required.
-- SQL Server 2008 includes a new built-in auditing tool called SQL Server Audit. It can be used to audit virtually any user activity. Keep the number of activities and objects you audit to a minimum to reduce performance overhead.
-- Consider running a SQL Server security scanner against your SQL servers to identify security holes.
-- If using SQL Server 2005/2008, enable password policy checking.
-- If running SQL Server 2008, Enterprise Edition, consider implementing Transparent Data Encryption to help protect data stored on disk.

MS SQL Server DBA Checklist - General - Job Maintenance

-- Avoid overlapping jobs on the same SQL Server instance. Ideally, each job should run separately at different times.
-- When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
-- Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
-- If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
-- Periodically (daily or weekly) run a database rebuild or reorganize job on all the indexes on all the tables in all your database. This will rebuild the indexes so that the data is no longer logically fragmented. Fragmented data can cause SQL Server to perform work, slowing down SQL Server's performance. Rebuilding or reorganizing tables will also update column statistics.
-- . As often as you take full backups, which is probably once a day, run DBCC CHECKDB on your databases to verify database integrity.
-- Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users.
-- Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers.

MS SQL Server DBA Checklist - General - SQL Server Configuration Settings

-- SQL Server configuration settings should remain at their default settings. Any changes to these settings should only be made by an experienced DBA who understands the pros and cons of making changes.
-- If you are using the 32-bit version of SQL Server, and if you are using 4 GB or more of RAM, ensure that you have all the AWE settings correctly set.

MS SQL Server DBA Checklist - General - Database Settings

-- Database settings should generally be kept at their default values. Ensure that the following settings are set for overall best performance: These are default settings:
* Auto Create Statistics: On
* Auto Update Statistics: On
* Auto Shrink: Off
* Page Verify: Checksum 
-- Don’t rely on AUTOGROWTH to automatically manage the size of your databases. Instead, proactively monitor and alter database size as circumstances dictate. Only use AUTOGROWTH to deal with unexpected growth.

SSRS Interview Questions and Answers

Which versions of SSRS have you used?

Microsoft SQL Server Reporting Services Version 2009.0100.1600.01

To check this:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

Have you used custom assemblies in SSRS? If Yes give an example

Custom assemblies  allows to re use code in  reports

Can you update report data using SSRS?

It is possible

What are query parameters?

Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.

What is a sub-report?

Sub-reports are inserted into a main report. Just like a main report, you also pass parameters and queries to it.

What are RDL files?

RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.

What is a data set?

Data sets are the components that contain your records. You can have a blank data set, a data set with one record, or a data set with millions of records, You can view a data set like a customized table. The data set has the columns and rows like a table

What is a data source?

A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008 and 2012.

What servers can be used with SSRS?

While most companies use SQL Server with SSRS, you can also integrate other database servers with your SSRS reports. SSRS is compatible with Oracle, ODBC and OLEDB connections, Hyperion, Teradata and flat XML files that contain data.

 What formats can SSRS export or render to?

SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files

How do you deploy a report to a reporting server?

The SSRS reporting software includes tools to deploy directly to the SQL Server reporting server.

You want to include an image in a report. How do you display the Image Properties dialog box? 

When you drag an image item from the Toolbox window to the Report Designer, the Image Properties dialog box automatically opens

You want to configure an amount to display the value in a currency format. Which property do you use?

To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c

What are data regions?

Data regions are report items that display repeated rows of summarized information from datasets

You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?

No, the Report Wizard lets you create only tabular and matrix reports. you must create the chart report directly by using the Report Designer.

You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?

You can right-click the project in Solution Explorer and then change the Target-Server URL property.

Which rendering formats are affected by the PageSize properties?

Because only the Adobe PDf file, Word, and Image rendering extensions use physical page breaks, they are the only formats that are affected by the PageSize properties.

Can you use a stored procedure to provide data to an SSRS report?

Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

You want to use a perspective in an MDX query. How do you select the perspective?

Use the Cube Selector in the MDX Query Designer to select a perspective.

Can you use data mining models in SSRS?

Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.

You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?

Create a text box item, set the action to Go To URL, and then configure the URL.

You want a report to display Sales by Category, SubCategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report?

Group the Sales information by Category, SubCategory, and Product. Hide the SubCategory group and set the visibility to toggle based on the Category item. Hide the Product category group and set the visibility to toggle based on the SubCategory item.

You want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web? 

No, you cannot create the same experience with SSRS. you can, however, use Excel to create such an experience.

What is the main difference between a Matrix report item and a Table report item? The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.

When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset?

From a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.

How do you configure a running aggregate in SSRS?

You can use the RunningValue function to configure a running aggregate.

What is the main purpose of a report parameter?

The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.

What is the main purpose of a query parameter?

The main purpose of a query parameter is to filter data in the data source.

You want your users to select a parameter from a list of values in a list box. How should you configure the parameter?

You should create a data source that contains the possible values and then bind the data source to the parameter.

What is the main benefit of using embedded code in a report?

The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.

What programming language would you use to create embedded functions in SSRS?

An SSRS report supports only visual Basic .nET embedded code.

Which of the following are valid options for deploying a report? (Choose all that apply.)

a. With BIDS 
b. With the Computer Management console 
c. With the .nET START command 
d. With SSMS 
e. With Report Manager

The correct answers are a and e, BIDS and Report Manager.

Solution for "C:\ is not accessible" (Permission is Denied)

Here is the most effective way to access your   ‘C’ drive

Most of the time we get an error C:\ is not accessible(permission is denied)  when we are trying to open C drive, this is because of C drive privileges  went disable, here is the easy way to make the C drive accessible.

                                                Right click on ‘C’ drive go to “Properties”

    In Properties go to  ” Security “  tab

In This window click    “Edit”  Then it will show below window with the Permissions for Local Disk(C)

In Permissions for Local Disk(C)

·         select Authenticated Users, and enable Modify, Read & execute, List folder Contents, Read, write (Don’t enable Full Control and special Permissions) in

“Allow “column

Then Click “ OK”

Now you can access your C drive without any problem

Performance Dashboard Reports - Microsoft SQL Server 2012

Performance Dashboard Reports - Microsoft SQL Server 2012

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:
• CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer (missing indexes)
• Blocking
• Latch contention
The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

This is a downloadable available from Microsoft and can be downloaded from the Microsoft.

This also works for SQL Server 2008 R2 and SQL Server 2008 as well.

Here is the link

Reference: http://www.microsoft.com/download/en/details.aspx?id=29063

How to Repair a Suspect Database in SQL Server

How to Repair a Suspect Database in SQL Server

here can be a possibility that when you connect to an SQL Server Instance you will find the database being marked as SUSPECT. During such scenarios, you will not be able to connect to the database. In this article we will go through the steps which you need to follow to recovery a database which is marked as SUSPECT. The steps mentioned in this article works on SQL Server 2005 and higher versions.

Some of the reasons why an SQL Server database can be marked as SUSPECT

1. 1. Database could have been corrupted.
   2. There is not enough space available for the SQL Server to recover the database during startup.
   3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.
   4. Database files are being held by operating system, third party backup software etc.
   5. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

Due to a hardware failure one of our database namely BPO was marked SUSPECT when the SQL Server came back online. Already due to the hardware failure we had downtime for more than two hours and adding to that when the server came back online our mostly critical database was marked as SUSPECT.

Steps to Recover a Database Marked as SUSPECT

1. Execute the below mentioned TSQL code to identify all the databases which are marked as SUSPECT.

USE master
GO

SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'GO

2. Open the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. You can open SQL Server Error Log by expanding Management Node à SQL Server Error Logs. In my server I could find below mentioned entries in SQL Server Error Logs.

Sample Error Messages within SQL Server Error Log when database is marked as SUSPECT

Starting up database 'BPO'.
Error: 9003, Severity: 20, State: 9.

The log scan number (189624:16:2) passed to log scan in database 'BPO' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Error: 3414, Severity: 21, State: 1.

An error occurred during recovery, preventing the database 'BPO' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

CHECKDB for database 'BPO' finished without errors on 2009-12-15 11:30:28.320 (local time). This is an informational message only; no user action is required.

3. When a database is in SUSPECT mode you will not be able to get connected to the database. Hence you need to bring the database first in EMERGENCY mode to repair the database. Execute the below mentioned TSQL code to bring the database in EMERGENCY mode.

USE master
GO 

ALTER DATABASE BPO SET EMERGENCY
GO

Once the database is in EMERGENCY mode you will be able to query the database.

5. Next step will be to bring the user database in SINGLE_USER mode by executing the below mentioned TSQL code.

ALTER DATABASE BPO SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO

6. Once the database is in SINGLE_USER mode execute the below TSQL code to repair the database. When you repair your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data. Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command then there is no way to go back to the previous state.

DBCC CHECKDB (BPO, REPAIR_ALLOW_DATA_LOSS) 
GO

7. Finally, execute the below mentioned TSQL command to allow MULTI_USER access to the database.

ALTER DATABASE BPO SET MULTI_USER
GO

Copying Database Files from SQL Server 2005 to Higher Versions

Copying Database Files from SQL Server 2000 to Higher Versions

Try making a backup of your database, and restoring it into a brand new database.

• Create new DB.
• Make a full backup of your original.
• Right click on your new DB, hit restore.
• Navigate to your .BAK, and ensure the .mdf and .ldf match the new.

Note:In SQL Server 2005 Express edition there is no Copy Database Wizard, the n the above method is better for Copying database files from lower to higher editions.

If Copy Database Wizard available:

Following are the steps to copy database from one instance to another instance.

Specify Source Server

Specify Destination Server

Here you can select option if you want to keep the database ONLINE when it is being copied.

You can also select option of MOVE or COPY database as well.

Give appropriate database name.

On this screen you can select additional options to copy as well.

You create the package over here.

You can schedule the package using SQL Server Agent.

When this process is over it will show the success message and database will be copied to another server.

Moving Database from SQL Server 2005 to Higher Versions(Detach &Attach Method)

Detach and Attach method to move database from sql 2005 to 2008

Detach:

In this Topic we will see how to Detach Database from SQL Server 2005 to SQL Server  Higher Versions. You can use database Detach and Attach Method option to move database  from one location to another location on the server.

Different ways to Detach Database in SQL Server

• Detach Database in SQL Server Using SQL Server Management Studio (SSMS)
• Detach Database in SQL Server Using TSQL Script

Permissions to Detach Database in SQL Server

You need membership in the db_owner fixed database role to detach database in SQL Server.

Important Note: It is recommend to note down the location of Data and Log file before detaching the SQL Server Database.

TSQL Script to Identify location of Data and Log file of SQL Server Database

USE MyTechMantra
GO

Exec sp_helpfile
GO

How to Detach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Expand Databases node in Object Explorer

3. Right click User Database -> Tasks ->  Detach… option from the drop down list as shown in the snippet below.

4. In Detach Database dialog box, select Drop Connections and Update Statistics check boxes as shown in the snippet below and click OK to detach the user database from SQL Server. You won’t be able to detach a database until all the connection to the database is dropped. Refer the following article titled “How to Get Exclusive Access to SQL Server Database” to know how to kill all active connections from a database. It is always a best practice to Update Statistics before detaching SQL Server database.

5. Once you have clicked OK the database will be detached from SQL Server.

How to Detach Database in SQL Server Using TSQL Script

Database Administrator can detach database from SQL Server using the below mentioned TSQL script.

USE [master]
GO
ALTER DATABASE [MyTechMantra] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyTechMantra', @skipchecks = 'false'
GO

Attach:

How to Attach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Right click Databases -> Attach… option from the drop down list as shown in the snippet below.

3. In Attach Databases dialog box click on Add… button as shown in the snippet below.

4. In Locate Database Files dialog box select the .MDF file of the database and click OK to return to Attach Database dialog box.

5. In Attach Databases dialog box; under Databases to attach and Database Details you will see all the relevant information with respect to the database which you are attaching in SQL Server. Finally, click OK to attach the database to SQL Server.

How to Attach Database in SQL Server Using TSQL Script

Database Administrator can attach database in SQL Server using the below TSQL script.

USE [master]
GO
CREATE DATABASE [MyTechMantra] ON 
( FILENAME = N'D:\MSSQL\Data\MyTechMantra.mdf' ),
( FILENAME = N'D:\MSSQL\Data\MyTechMantra_log.ldf' )
 FOR ATTACH
GO