The MSSQLFix Solutions

Thursday, April 29, 2010

Different Types Of Backups In SQL Server

Thursday, April 29, 2010  Nareshkar Pakanati  No comments

Create Full backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak'

Create Differential backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksdiff.bak' WITH DIFFERENTIAL

Create Transaction Log backup

Backup Log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworkslog.trn'

Create COPY ONLY Full Backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak' WITH COPY_ONLY

Create COPY ONLY Differential Backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak' WITH DIFFERENTIAL, COPY_ONLY

Create COPY ONLY Log Backup

Backup log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.trn' WITH COPY_ONLY

Create Tail-Log Backup if Database is Doesn't Start/Suspect mode

Backup Log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworkslog.trn' WITH {CONTINUE_AFTER_ERROR | NO_TRUNCATE}

Create Tail-Log Backup if Database is Online

Backup Log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworkslog.trn' WITH norecovery

Create FILE Backup

Backup database Adventureworks 
FILE= 'file name' 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak'

Create FILEGROUP Backup 
Backup database Adventureworks

FILEGROUP= 'filegroupname' 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak'

Create MIRRORED Backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak' 
MIRROR 
To Disk = 'E:\Projects\BACKUPS\New folder\adventureworksfull.bak' 

WITH FORMAT

The supported backup types depend on the recovery model of the database, as follows.

Scope of backup	Backup types
Whole database	Database backups cover the whole database.
Partial database	Partial backups cover read/-write filegroups and, possibly, one or more read-only files or filegroups.
File or filegroup	File backups cover one or more files or filegroups, and are relevant only for databases that contain multiple filegroups. Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.

Single table restore from backup. table backup and restore. how to do table restore from backup file. how to take single table backup. how to restore single table from old backup file. What are the types of backups in sql server 2008 R2. What are the types of backups in sql server 2012. What are the types of backups in sql server 2005. how many types of backups in sql server 2008.   how many types of backups in sql server 2012. how many types of backups in sql server 2008 R2. how many types of backups in sql server 2005. how many types of restores in sql server 2008. how many types of restores in sql server 2012. how many types of restores in sql server 2005. native backups in sql server, compressed backup.

Read More

Wednesday, April 28, 2010

Different Types Of Restores In Sql Server

Wednesday, April 28, 2010  Nareshkar Pakanati  No comments

1. Restore an entire database from a full database backup (a complete restore).
2. Restore part of a database (a partial restore).
3. Restore specific files or filegroups to a database (a file restore).
4. Restore specific pages to a database (a page restore).
5. Restore a transaction log onto a database (a transaction log restore).
6. Revert a database to the point in time captured by a database snapshot.

Restore a full database
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups

Restore full and differential database backups
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\SQLServerBackups\AdventureWorks.bak'
NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\SQLServerBackups\AdventureWorks.bak'
RECOVERY;

Restore a database using RESTART syntax
-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups 

--Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups WITH RESTART

Restore a database and move files
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO
'C:\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'AdventureWorks_Log'
TO 'C:\MSSQL\Data\NewAdvWorks_log.ldf'
RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH RECOVERY

Copying a database using BACKUP and RESTORE
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups;
GO

RESTORE FILELISTONLY
FROM AdventureWorksBackups;
RESTORE DATABASE XYZ
FROM AdventureWorksBackups
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\XYZ.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\XYZ.ldf';
GO

Restoring to a point-in-time using STOPAT
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE=3, 

NORECOVERY;
 

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=4, 

NORECOVERY, 

STOPAT = 'Apr 20, 2010 12:00 AM';
 

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=5, 

NORECOVERY, 

STOPAT = 'Apr 20, 2010 12:00 AM';
 

RESTORE DATABASE 

AdventureWorks WITH RECOVERY;

Restore the transaction log to a mark
USE AdventureWorks;
GO
BEGIN TRANSACTION ListPriceUpdate
WITH MARK 'UPDATE Product list prices';
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'KK-%';
GO
COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database.
-- and log backups are taken.
-- An error occurs in the database.

USE master
GO
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE = 3, 

NORECOVERY;
GO

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'ListPriceUpdate';
GO

Restore database From Tape
RESTORE DATABASE AdventureWorks
FROM TAPE = '\\.\tape0'

Restore using FILE and FILEGROUP syntax
RESTORE DATABASE MyDatabase
FILE = 'MyDatabase_data_1',
FILE = 'MyDatabase_data_2',
FILEGROUP = 'FileGroup_6'
FROM MyDatabaseBackups
WITH FILE = 9,
NORECOVERY;
GO

Restore the log backups.
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 10,
NORECOVERY;
GO

RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 11,
NORECOVERY;
GO
 

RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 12,
NORECOVERY;
GO

Recover the database:
RESTORE DATABASE MyDatabase
WITH RECOVERY;
GO

Reverting from a database snapshot
USE master
GO
RESTORE DATABASE AdventureWorks
FROM
DATABASE_SNAPSHOT = 'AdventureWorks_dbss2100';
GO
Single table restore from backup. table backup and restore. how to do table restore from backup file. how to take single table backup. how to restore single table from old backup file. What are the types of backups in sql server 2008 R2. What are the types of backups in sql server 2012. What are the types of backups in sql server 2005. how many types of backups in sql server 2008.   how many types of backups in sql server 2012. how many types of backups in sql server 2008 R2. how many types of backups in sql server 2005. how many types of restores in sql server 2008. how many types of restores in sql server 2012. how many types of restores in sql server 2005.

Read More

Tuesday, April 27, 2010

Find Orpahend Login Name With Encrypted Password

Tuesday, April 27, 2010  Nareshkar Pakanati  1 comment

Here is the script for finding orpahend users with their SID, this script is very useful while server migration from one location to another location.

 
Use Master

GO
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
where loginname Not like 'NA%'
and
loginname not like 'Builtin%' and loginname Not like 'sa'

orphaned login details in sql server. sql server login migration script. login migration script. login move move details. users migration script. user move script in sql server 2012. in sql server 2008 r2. in sql server 2008. login move. server login migration. server login move from sql 2005 to sql 2012. sql login with encrypted password. easy script for login movement. login script. SID ID with login, user mapping in sql server, login mapping in sql server, mapping in sql server 2012, mapping in sql server 2008.

Read More

Sunday, April 25, 2010

How To Find Find Database Restore History

Sunday, April 25, 2010  Nareshkar Pakanati  No comments

Here is the script for finding the database restore history in SQL Server.

USE msdb;
GO
Select
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
from RestoreHistory h
inner join BackupSet b
on h.backup_set_id = b.backup_set_id
inner join BackupFile f
on f.backup_set_id = b.backup_set_id
order by RestoreDate
GO

The output would be like below for the above script:

How to find out what when was last transaction log restored. How to find out what when was last transaction log restore happened.  How to find out what when was last transaction log restore done. How to know when was last transaction log backup happened in sql server. in sql server 2008 R2. in sql server 2012. in sql server 2005. in sql server 2000. how to find the time when last backup taken. last backup time in sql server 2008 R2. last backup time in sql server 2012. data base administrator. microsoft database administrator. last restore time in sql server. last restored transaction details in sql server. last restored date and time. sql questions and answers. interview questions for servers. database administrator interview questions. sql dba interview questions and answers. server interview. sql questions for interview. ms sql interview questions with answers. becoming a database administrator.

Read More

Saturday, April 24, 2010

Find When Was The Last Backup Taken In SQL Server

Saturday, April 24, 2010  Nareshkar Pakanati  No comments

Here is the script to find the last backup taken time in SQL Server.

SELECT Database_Name,

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last

FROM MSDB.dbo.BackupSet

WHERE Type = 'd'

GROUP BY Database_Name

ORDER BY 3 DESC

Go

Here is the script to find the last Transaction Log backup

SELECT Database_Name,

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,

DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last

FROM MSDB.dbo.BackupSet

WHERE Type = 'l'

GROUP BY Database_Name

ORDER BY 3 DESC

How to find out what when was last transaction log restored. How to find out what when was last transaction log backup happened.  How to find out what when was last transaction log backup done. How to know when was last transaction log backup happened in sql server. in sql server 2008 R2. in sql server 2012. in sql server 2005. in sql server 2000. how to find the time when last backup taken. last backup time in sql server 2008 R2. last backup time in sql server 2012. data base administrator. Microsoft database administrator. sql questions and answers. interview questions for servers. database administrator interview questions. sql dba interview questions and answers. server interview. sql questions for interview. ms sql interview questions with answers. becoming a database administrator.

Read More

Friday, April 23, 2010

List Of Replication Maintenance Jobs

Friday, April 23, 2010  Nareshkar Pakanati  No comments

Replication uses the following jobs to perform scheduled and on-demand maintenance.

What is Publisher. What is Subscriber. What is Article. What is Publication. What is Distributor. What is Pull Subscription. What is Push Subscription. What is Snapshot Agent. What is Log Reader Agent. What is Distribution Agent. What is Merge Agent. What is Queue Reader Agent. What is replication. how to configure replication. What are the terms used in Replication? replication interview question and answers. interview question and answers. sql server 2012 replication interview question and answers. experienced dba interview question and answers. Distribution database. system database. replication configuration steps. most asked questions in interview. ibm interview question and answers. wipro interview question and answers. tech mahindra interview question and answers. replication types. replication usage. sql server 2008 r2 replication. sql server 2000 replication. sql server 2005 replication. sql server 2012 replication. snapshot replication. transactional replication. merge replication. queue update replication. how many types of agents in sql server 2008 r2. types of agents. SQL server replication jobs, jobs will create on replication, replication jobs list, jobs details in replciation

Read More

Thursday, April 22, 2010

What Are The SQL Server Agent Roles

Thursday, April 22, 2010  Nareshkar Pakanati  No comments

SQL Server 2005 and later versions introduces the following msdb fixed database roles, which give administrators finer control over access to SQL Server Agent. With this roles we can give access to developers only view their owned jobs.

1. SQLAgentUserRole
2. SQLAgentReaderRole
3. SQLAgentOperatorRole

SQLAgentUserRole Permissions:

SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRoleJob Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

SQLAgentReaderRole Permissions:
SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

SQLAgentOperatorRole Permissions:

 SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

 SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule.

SQL Server Agent roles. how to give sql server job access permission. how to give sql jobs permission. how to give jobs access to developers. how to restrict to access jobs in sql server 2008 r2. how to give sql server agent access to developers. how to grant access to sql server agent. how to give permission on sql server 2012. how to give access on sql server 2005. sql server agent job schedule. sql server agent job configuration. sql server agent. sql server job history.

Read More

Tuesday, April 20, 2010

Checking The SQL Server 2005 Installation From Cluster Administrator

Tuesday, April 20, 2010  Nareshkar Pakanati  No comments

Once an instance of SQL Server 2005 clustering is installed, you can view its cluster resources by going to Cluster Administrator and opening up the SQL Server Group resource, as shown below.

The above figure shows the cluster resources for the SQL Server 2005 cluster. We see all of the names of the resources, their state, and which node the resources are running on. 


Cluster Administrator is a great tool for seeing if all the resources are up and running and which node is the current active node.

Here is a brief rundown on each of the SQL Server 2005 cluster resources:

1.   Disk F: This is the shared disk array where the SQL Server data files and logs are stored.

2.   SQL Network Name (sqlcluster): This is the virtual SQL Server name used by clients to connect to this clustered instance of SQL Server 2005. The name "sqlcluster" is the name I have assigned this cluster instance, and will not be the same as your cluster, unless you name yours the same as mine. 

3.   SQL IP Address (sqlcluster): This is the virtual SQL Server IP address used by clients to connect to this clustered instance of SQL Server 2005. Again, the name "sqlcluster" is the name of the virtual server, and is the one I have used for this cluster. Your name will most likely be different. 

4.   SQL Server: This is the SQL Server service. 

5.   SQL Server Agent: This is the SQL Server Agent service. 

6.   SQL Server FullText: This is the SQL Server FullText service. Even though you may not use this service, it is automatically installed as a cluster resource.

data base administrator. Microsoft database administrator. sql questions and answers. interview questions for servers. database administrator interview questions. sql dba interview questions and answers. server interview. sql questions for interview. ms sql interview questions with answers. becoming a database administrator. server interview tips. SQL Server cluster installation, step by step guide to install cluster services, sql server 2012 cluster installation, sql server 2008 cluster installation, sql server 2008 r2 cluster installation, sql cluster per-requisites, cluster interview question and answers, sql server cluster interview question and answers, cluster configuration, cluster fail over, cluster failover, cluster fail back, cluster fail back, cluster in sql server 2005, sql server 2000 cluster, sql server 2005 cluster, sql server 2008 cluster, cluster installation, windows server 2008 r2 cluster, windows cluster installation, windows cluster prerequisites, Step By Step Guide To Configure SQL Server Failover Cluster, step by step cluster configuration, step by step images guide for cluster, cluster installation with screenshots

Read More

Monday, April 19, 2010

Step By Step Guide To Configure SQL Server Failover Cluster

Monday, April 19, 2010  Nareshkar Pakanati  No comments

To start installing your SQL Server 2005 cluster, you will need the installation CD or DVD or you can either install it directly from the media, or copy the install files from the media to the current active node of the cluster, and run the setup program from there.

To start the installation, run Setup.exe. you will get the first install dialog box as shown below.

Once the prerequisite components have been successfully installed, the SQL Server Installation Wizard launches, as you can see below.

Click Next to proceed.

The next step is you want all checks to be successful, with a green icon. If you get any yellow warning or red error icons, then you need to find out the problem, and correct it before proceeding. In some cases, yellow warning icons can be ignored, but red error icons cannot. If you have any yellow or red icons, you may have to abort the setup process, fix the problem, then restart the setup process. If all is well, click Next to proceed. 

The next dialog box is Registration, where you enter your company name and license key, if applicable.

Next, you must select the SQL Server 2005 components to install. See below.

I checked the options to "Create a SQL Server failover cluster" and to "Create an Analysis Server failover cluster" (currently grayed out). Since we are creating a SQL Server 2005 cluster, you must select the "Create a SQL Server failover cluster." If you are going to install Analysis Services then you must select "Create an Analysis Server failover cluster." Once you have selected all the components you need to include, click Next.

As with any install of SQL Server 2005, the next step is to select the name of the instance to be installed. You can choose between a default instance and a named instance. Click Next to proceed.

Now, here is a very important step. This is when you enter the name of the virtual SQL Server 2005 instance you are currently installing. This is the name that clients will use to connect to this instance. Ideally, you have already selected a name to use that makes the most sense to your organization. Click Next to proceed. If you ever need to change this virtual name, you will have to uninstall and then reinstall SQL Server 2005 clustering.

This is also a very important step. This is where you enter the virtual IP address for this instance of SQL Server 2005. Like the cluster virtual name, it is used by clients to connect to this instance of SQL Server 2005. The IP address must belong to the same subnet as the IP addresses used by all of the nodes.

In addition, in this dialog box you must select the network to be used for the public network—the network used by the clients to connect to this instance of SQL Server 2005. All of the available networks will be listed in the drop-down box next to Network to use. If you have named the public and private networks Public and Private, respectively, it will be very easy for you to select the correct network, as I have above.

Once you have entered the IP address and selected the public network, click on Add, so that the information you just selected is in the Selected networks and IP addresses box. Then click Next.

In this dialog box, select the SQL Server Group as the group where you want to create the SQL Server resources. In addition, be sure that the Data files will be created on the correct logical drive of the shared array using the folder name you choose. Click Next to proceed.

Now, you specify which nodes you want to install this instance of SQL Server on. Because our example is for only two nodes, the default setting works for us. Notice that under Required node is SQL2005A, which is the name of the physical node where we are running the setup program. And under Selected nodes is SQL2005B, the second physical node in our 2-node cluster. Click Next to proceed.

In this dialog box, we must select an account (with password) that has administrative rights on all of the nodes where we want to install this instance of SQL Server 2005. This can be any domain account that is a local administrator of all the nodes. Click Next to proceed.

The Service Account dialog box is identical to the one you see when you install SQL Server 2005 on a non-cluster, and it is configured the same. Click Next to proceed.

In this dialog box, you must select pre-existing global domain groups that are used to contain the startup account for each clustered service. You can choose to add all three services to the same global domain group, or to create separate global domain groups, one for each service, as has been done above. Once you have selected appropriate domain groups, click Next to proceed.

The next four dialog boxes of the Installation Wizard, not shown here, are the same as for any other installation of SQL Server 2005. After you have completed these steps, the installation of this instance of SQL Server 2005 begins, and you see the following dialog box.

The installation process will take some time as it is installing the binaries on both nodes of the cluster, and installing the system data files on the shared array. The Setup Progress step shows the status of the first node's install. If you want to see the status of the second node's install, you can change the drop-down box watch its progress.

As the installation proceeds, you will want to see all green icons next to each installation step. If any step should fail, then the entire installation process will need to be rolled back.

Sometimes, if the installation breaks, it just dies and a rollback of what has been done so far will not occur. If this is the case then you manually uninstall the node instead of entire and try, if it fails again then you have to do from scratch OS.

It the installation was a successful, you will see a final dialog box, where you can click Finish. SQL Server 2005 clustering had now been successfully installed on the two cluster nodes.

data base administrator. microsoft database administrator. sql questions and answers. interview questions for servers. database administrator interview questions. sql dba interview questions and answers. server interview. sql questions for interview. ms sql interview questions with answers. becoming a database administrator. server interview tips. SQL Server cluster installation, step by step guide to install cluster services, sql server 2012 cluster installation, sql server 2008 cluster installation, sql server 2008 r2 cluster installation, sql cluster per-requisites, cluster interview question and answers, sql server cluster interview question and answers, cluster configuration, cluster fail over, cluster failover, cluster failback, cluster fail back, cluster in sql server 2005, sql server 2000 cluster, sql server 2005 cluster, sql server 2008 cluster, cluster installation, windows server 2008 r2 cluster, windows cluster installation, windows cluster prerequisites,Step By Step Guide To Configure SQL Server Failover Cluster, step by step cluster configuration, step by step images guide for cluster, cluster installation with screenshots

Read More

Sunday, April 18, 2010

How To Create A SQL Server Maintenance Plan Using Management Studio

Sunday, April 18, 2010  Nareshkar Pakanati  No comments

In SQL Server one of the best feature is Maintenance Plan, where you can schedule hourly/daily/weekly/monthly backup, restore, rebuild index job and many more. Here I show you how to schedule daily back Job through maintenance Plan.

Step 1. Open SQL Server 2008 or 2005 Management Studio and connect to a server

Step 2. Expand the tree (left navigation) Management

Step 3. Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.

Step 4. Follow the wizard

Step 5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following.

Step 6. Once you click the button “Change” you will get a new popup to enter schedule details as shown below.

Step 7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.

Step 8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.

Step 9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.

Step 10. From the above window, browse the folder location where to store the backup files.

Step 11. Continue the the steps until you get to Finish step.

Step 12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.

Step 13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).
how to delete database maintenance plan, how to configure maintenance plan in sql server 2008 r2, how to delete maintenance plan in sql server 2008 r2, how to delete maintenance plan in sql server 2012. SQL Server 2005 maintenance plan.

Read More

Wednesday, April 14, 2010

Creating a SQL Server Maintenance Plan For 2000

Wednesday, April 14, 2010  Nareshkar Pakanati  No comments

The only way to be ready for a disaster is to plan ahead and ensure that the SQL Server maintenance plan and disaster recovery measures you have in place will work. 

A good SQL Server maintenance plan can do wonders if planned correctly, implemented successfully and managed appropriately and properly.

SQL Server provides a maintenance plan that help to implement appropriate measures to ensure that your database is safe and properly maintained.

So what is a database maintenance plan and how can you configure a good SQL Server 2000 maintenance plan?

A maintenance plan is a set of measures taken to ensure that a database is properly maintained and that routine backups are scheduled and handled.

Within SQL Server 2000, a database maintenance plan can include tasks such as backups of the database, backups of the transaction log, recomputing statistics on the database, managing indexes and taking care of internal data storage issues.

A nice feature of SQL Server 2000 is that the maintenance plan can be configured by a wizard, which can help alleviate some of the burden of creating the plan. It can also ensure that the common features of the maintenance plan are taken care of.

The Database Maintenance Plan Wizard relies on a graphical user interface to give the user the ability to visually create and implement one or more jobs within SQL Server 2000 that will run under SQL Server Agent.

These jobs can automatically check the database integrity, get rid of unused space within the database, which will increase performance, reorganize your indexes, create the backups of the database and transaction logs.

For most administrators and instances, the wizard will suffice in creating the backup plan unless you want to create a custom maintenance plan yourself.

In order to use the wizard to create the maintenance plan in Enterprise Manager, select the Tools menu option and navigate to the Database Maintenance Planner option. This will bring up the following screen: 

Click the Next button to continue with the wizard. This will bring up the following screen: 

This screen allows you to specify the database(s) that will be involved with the maintenance plan that you are creating. The available options are

• All databases
• All system databases (master, model, and msdb)
• All user databases (all other than master, model, and msdb)
• These databases – which allows a selection from a list of one or more databases to include

Depending on which databases you would like to include in the SQL Server maintenance plan, make the selection from this screen and continue with the wizard.

Clicking the Next button will allow you to continue with the creation of the database maintenance plan. The Next button will open the following screen: 

The first option in this dialog will instruct the plan to reorganize the data and index pages in the database.

If this option is selected, the statistics are recreated automatically when this step runs.

Updating the statistics will increase the performance and reliability of the database(s) in question. If you select the option to reorganize then you cannot select the update statistics box because the statistics will automatically get recreated.

You can however, just select the update statistics box without the reorganization. You can also specify that you would like to remove unused space from the database files.

During the course of normal operations, database files will become filled with unused space, which will lead to decreased performance.

After these options are selected, click the Next button to continue with the wizard and the following screen is shown: 

This screen allows you to check the integrity of the database.

The integrity check can be configured to include indexes and attempt to repair any problems that it finds. 

The checks can also be setup to be performed before doing a backup. This option can have negative side effects either way.

The integrity check may take up precious time on the server, but it can fix problems. On the other hand, the check may not be able to fix all the problems that it encounters. The check can also be scheduled by clicking the Change button to set up the schedule.

Selecting the Next button will bring up the following screen: 

This screen allows the creation and schedule of the backup and to specify the location of the backup file, as well as, to verify the integrity of the backup when the operation is complete.

If you select the disk option on this screen you will be taken to the screen to configure the file location and select options to remove older backup files that may be in that location already.

This screen is shown in the following illustration: 

Clicking Next will allow you to specify information relating to the transaction log backup as shown in the following image: 

This screen functions the same way as the screen for configuring the database backup. It allows the selection of the file location, the verification of the integrity and the scheduling of the backup.

After clicking Next, if disk was selected as the location of the backup file, the user will be presented with the following screen:

This screen allows the selection of the directory location for the backup file, as well as the ability to instruct SQL Server to create a subdirectory for each database being backed up while the operation is running.

You can also specify the removal of old backup files and assign an extension to the backup files. Clicking on the Next button will bring up the following screen: 

This allows you to specify whether or not to generate a report for the SQL Server maintenance plan and store that in a specific location.

You may also delete report files older than a specified timeframe. Operator email notifications can also be configured on this page as well.

Selecting Next will bring up the following screen for input: 

This screen deals with options relating to whether or not the SQL Server maintenance plan history will be stored in a SQL Server database somewhere for easy retrieval and historical purposes.

Selecting Next will finalize the maintenance plan and bring up the confirmation screen as seen in the following illustration: 

This will allow the naming of the SQL Server maintenance plan and clicking finish will generate the necessary jobs that are needed to perform the database plan.

The objects generated by these steps can be seen by looking at the SQL Server maintenance plan for the database or looking at the individual jobs created and scheduled on the server. 

Read More