SQL 2008 R2 Error No.2

SQL 2008 R2 Express  error 2 occurred at one office. By replaceing  old Master MDF & Master ldf  files issue solved.

Configuring Password Policy Settings in an Active Directory

Implementing Password Policy Settings Step-by-Step

• Credentials: You must be logged on as a member of the Domain Admins group.
• Tools: Active Directory Users and Computers.
• To implement password policy on computer systems that belong to an Active Directory domain
  1. Click Start, click Control Panel, double-click Administrative Tools, and then double-click Active Directory Users and Computers.
  2. Right-click the root container for the domain:
     
     
     
     
     Note: Screen shots in this document reflect a test environment and the information might differ from the information displayed on your screen.
  3. Select Properties from the menu that appears:
     
  4. In the properties dialog box for your domain, click the Group Policy tab, and then click New to create a new Group Policy object in the root container. Type "Domain Policy" for the name of the new policy and then click Close.
     Note: Microsoft recommends that you create a new Group Policy object rather than editing the built-in one called Default Domain Policy because doing so makes it much easier to recover from serious problems with security settings. If the new security settings create problems, you can temporarily disable the new Group Policy object until you isolate the settings that caused the problems.
  5. Right-click the root container for the domain, and then click Properties.
  6. In the properties dialog box, click the Group Policy tab, and then select Domain Policy.
  7. Click Up to move the new GPO to the top of the list, and then click Edit to open the Group Policy Object Editor for the GPO you just created.
  8. Under Computer Configuration, navigate to the Windows Settings\Security Settings\Account Policies\Password Policy folder.
     
  9. In the details pane, double-click Enforce password history, select the Define this policy setting check box, set the value of Keep password history to 24, and then click OK.
     
  10. In the details pane, double-click Maximum password age, select the Define this policy setting check box, set the value of Password will expire in to 42, clickOK, and then click OK to close the Suggested Value Changes window that appears.
      
  11. In the details pane, double-click Minimum Password Age, select the Define this policy setting check box, set the value of Password can be changed after to 2, and then click OK.
      
  12. In the details pane, double-click Minimum Password Length, select the Define this policy setting check box, set the value of Password must be at least to 8, and then click OK.
      
  13. In the details pane, double-click Password must meet complexity requirements, select the Define this policy setting in the template check box, selectEnabled, and then click OK.
      
  14. Close the Group Policy Object Editor, click OK to close your domain's properties dialog box, and then exit Active Directory Users and Computers.

Verifying New Settings

Use the following procedure to verify that the appropriate password policy settings are applied and effective in the Domain Policy GPO. Verifying the settings and their operation ensures that the correct password policies will be applied to all users in the domain.

Requirements

• Credentials: You must be logged on as a member of the Domain Admins group.
• Tools: Active Directory Users and Computers.

• To verify password policy settings for an Active Directory domain
  1. Open Active Directory Users and Computers, right-click your domain, and then click Properties.
  2. In your properties dialog box for your domain, click the Group Policy tab, select the Domain Policy GPO, and then click Edit to open the Group Policy Object Editor.
  3. Under Computer Configuration, go to the Windows Settings\Security Settings\Account Policies\Password Policy folder, and verify that your settings match the settings shown here:
     
  4. Close the Group Policy Object Editor, click OK to close the properties dialog box for your domain, and then exit Active Directory Users and Computers.
  5. Verify that users cannot specify passwords that are shorter than 8 characters, that they cannot create non-complex passwords, and that they cannot immediately change their new passwords.

Courtesy :  http://potools.blogspot.in/

SQL 2000 Installation Problem

A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup.

You don’t really have to restart your machine – at least I didn’t have too! I hate to close out all my apps and reboot (are you listening Microsoft?) for a simple install like this. Are you getting this error message “A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup.” when attempting to install the SQL Server Enterprise Tools on your workstation? Hopefully this will help.

Exit the installer and then do this:

Click START, RUN type regedit
Surf to the following key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
Delete PendingFileRenameOperations
Start the install process again.

Source: if the above doesn’t work, try the other items mentioned at the bottom of this Microsoft Support article.

source:sapost.blogspot.com

 

 

How to Repair a Suspected SQL Database

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found using the following query

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

      You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

source:sapost.blogspot.com

 

How to change the SQL sa password - For Security measure ( for SAs only)

1. How to change the SQL sa password in the SQL Server Enterprise Manager

To change the password on the 'sa' account from the graphical interfaces of MSSQL Server 7.0 and 2000 on both Windows NT 4.0 and 2000:

Quote:

1. Open the "SQL Server Enterprise Manager". This is usually under "Start"-->"Programs"-->"Microsoft SQL Server". 2. Navigate to the "Logins" object under the "Security" folder on the SQL Server you wish to administer. Then, right click on the 'sa' account and select "Properties". 3. Now, enter a new password in the "Password" field under the "Authentication" options.

For More details SQL 2000 / 2005   -  Visit :  MRSupport
Note:

It is for the Use of System Administrators only for maintaining the Security 

source:sapost.blogspot.com

Compatibility With SQL Server 2005

The ntwdblib.dll module is associated with the Microsoft SQL Server program. This is an essential file and should not be removed if you have SQL Server installed on your computer.

It is essential for Sanchaypost With SQL2005

Copy ntwdblib.dll into c:\Windows\System32 folder in the server & nodes having SQL 2005. 

 
 
Download    


 

Changing of Server Authentication Mode in SQL Server 2008 R2

During installation, SQL Server Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode. This topic describes how to change the security mode after installation.

If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password.

Security Note:The sa account is a well-known SQL Server account and it is often targeted by malicious users. Do not enable the sa account unless your application requires it. It is very important that you use a strong password for the sa login.

The sa login can only connect to the server by using SQL Server Authentication.

To change security authentication mode

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
   
   

To restart SQL Server from SQL Server Management Studio

• In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL

• Execute the following statements to enable the sa password and assign a password.

  
  ALTER LOGIN sa ENABLE ;
  GO
  
  ER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
  GO
  AL
  
  T
  

  To enable the sa login by using Management Studio

  
  
  
  In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
  
  
  On the General page, you might have to create and confirm a password for the sa login.
  
  
  On the Status page, in the Login section, click Enabled, and then click OK.
  
  
  Courtesy : http://bnjho.blogspot.in  source:sapost.blogspot.com
  
  
  
  

Shrink a Database SQL Server 2008 R2

This topic describes how to shrink a database by using Object Explorer in SQL Server Management Studio. The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.

To shrink a database

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand Databases, and then right-click the database that you want to shrink.
3. Point to Tasks, point to Shrink, and then click Database.
4. Optionally, select the Reorganize files before releasing unused space check box. If selected, a value must be specified for Maximum free space in files after shrinking.
   Selecting this option is the same as specifying a target_percent value when executing DBCC SHRINKDATABASE. Clearing this option is the same as executing DBCC SHRINKDATABASE. By default, the option is cleared.
5. Enter the maximum percentage of free space to be left in the database files after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is selected.
6. Click OK.
   
   
   Courtesy : http://bnjho.blogspot.in  source:sapost.blogspot.com

Restore database backup of SQL Server 2008 to SQL server 2005

Step by Step Guide

1) Start convert wizard

Open SQL Server Management Studio2008. in 'Object Explorer', right click the database that you want to convert. Select 'Tasks' > 'Generate Scripts...'.

2) Next

Click 'Next'.

3) Select database and objects

Select the database that you want to convert, and check on 'Scripts all objects in the selected databases'

4) Convert Options

Set options:

'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'
    

5) Output Option

Select option 'Script to file', 'Single file' and 'Unicode text'.

6) 'Finish'

View summary and click 'Finish'.

7) Result

Now you got a complete database creation script with data. It can be executed on target database server.

 

8) Amend Script

Open the generated script in SQL Server Management Studio 2005. Find the following section and amend the path to proper data folder

CREATE DATABASE [StockTraderDB] ON  PRIMARY 
( NAME = N'StockTraderDB', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB.mdf ,
 SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StockTraderDB_log', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB_log.LDF',
 SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

9) Execute the Script

When finished, You should get converted database of SQL Server 2005

Courtesy : http://potools.blogspot.in/  source:sapost.blogspot.com

Downgrade a Database from SQL Server 2005 to SQL Server 2000

Before beginning must assume, firstly that the user, who will attempt such thing, has  a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1 Generating Scripts for the Database Elements and Structures

1)      Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2)      At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3)      Set the following Elements to the following Values

a.       Script Collation , set to TRUE

b.      Script Database Create, set to TRUE

c.       Script of SQL Version, set to SQL SERVER 2000

d.      Script foreign keys, set to FALSE

e.       Script Triggers, set to FALSE

 Then Hit the Next button

4)      Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5)      Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1)      After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2)      From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3)      At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4)      A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1)      Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2)      Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3)      Set all the Elements on the List to a False Value except the ones that follow:

a.       Include IF NOT EXISTS , set to TRUE

b.      Script Owner, set to TRUE

c.       Script of SQL Version, set to SQL SERVER 2000

d.      Script foreign keys, set to TRUE

e.       Script Triggers, set to TRUE

 Then Hit the Next button

4)      After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5)      At the screen that follows hit the Select All button and the Next.

6)      Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7)      Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

Courtesy : http://bnjho.blogspot.in source:sapost.blogspot.com