Database Administration

How to restore System Databases from TDP SQL

Hence to startup the recovery process you have to first start SQL Service, but it will fail as your master database files are missing. Now we need to recreate our master database files first.

  1. Before starting the procedure make sure you have note down the port number and the Collation on which your SQL Server is running, this can be seen in TCP\IP properties and even in registry as well shown below

Default Instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

Named Instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server (InstanceName) \ MSSQLServer\ SuperSocketNetLib\TCP

For Collation refer below key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

  1. Copy the SQL Server 2000 setup files on the Local Drive of the server from Segotn10063
  2. Run the Rebuildm.exe command as shown below

Rebuildm

  1. When you run this command below shown window will open

TDPSQL1

  1. Now specify the server name, the location of the setup and the collation.

 TDPSQL2

  1. Click on the rebuild button.

When you click on rebuild a new window will open “Configuring Server” mentioned below.

TDPSQL3

The configuration will take some time and executes many steps during this procedure, wait till it completes.

 TDPSQL4

 TDPSQL5

  1. Close all SQL Related application\windows
  2. When the process completes start your SQL Service in Single user mode.

SQLServr.exe –c –m –T3608

  1. Check all files TDP SQL related files like “dsm.opt”, instance.bat, tdpsql.cfg etc
  2. Open TDPSQL GUI
  3. Now start the process of restoring Master Databases as mentioned below using TDP SQL

TDPSQL6

  1. Overwrite the existing Master Database by using restore into option

TDPSQL7

  1. When the Master Database restore will finish your SQL Server stops and comes out from Single User Mode.
  2. Now when you start SQL Server you can see all user databases in suspect mode, as their respective files are missing.
  3. Now Restore Model Database using TDP SQL from the last backup available
  4. Now Stop SQL Server Agent, and restore MSDB database using TDP SQL (No Need to start SQL Server in Single User Mode)
  5. After restoration of MSDB start SQL Server agent.

At this point you have now restored all SQL Server Logins and Jobs

  1. Now start restoring the user databases one by one using point in time recovery and with replace option.

When all user databases were restored make sure that all user databases are accessible and run some select queries randomly on some tables.

Note: Make sure that if it’s a reinstall and you don’t have a password for the service account run the SQL Services by LOCAL SYSTEM ACCOUNT not with your account. 

How to grant permission to connect to SSIS (SQL Server Integration Services)

Even if you have all related access on MSDB related to accessing SSIS, but you still can’t connect to Integration Services, you will receive below error message.

SSISError

How to FIX this issue:

  1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
    2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
    3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
    4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

SSISPErmission

5. Right-click on MsDtsServer and select Properties.
6. In the MsDtsServer Properties dialog box, select the Security tab.
7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

SSISPErmission_1
8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9. Click OK to close the dialog box.
10. On the same Security tab, under Access Permissions, select Customize, then click Edit to open the Access Permission dialog box.

SSISPErmission_2
11. In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Access, and Remote Access. The easiest is to add the local DCOM Distributed Users group.

SSISPErmission_3

12. Click OK to close the dialog box. Close the MMC snap-in.
13. Restart the Integration Services service.
14. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
15. Close the MMC snap-in.

16. Restart the Integration Services service.

 

How to avoid\bypass restart while installing SQL Server 2005\SQL 2008 on windows 2003

While installing SQL Server on windows 2003 the rule check failed “Restart Computer”, now to avoid this thing we had an easy option go to below mentioned registry key and delete the values under “PendingFileRenameOperations”.

HKLM\System\CurrentControlSet\Control\Session Manager

Under that key, there is a value named “PendingFileRenameOperations”

PendingFileRenameOperations

The main cause is that there are pending file changes and above registry key is used to move, or delete files on boot. This helps when you have files that are always in use and can only be deleted or renamed when windows has not started up yet.

 

How to RUN DSA.MSC in windows server 2008

Like windows server 2003 when you try to fire the same command dsa.msc in windows server 2008 for querying active directory it will not work if the feature is not enabled.

Windows2003

To enable this feature you need to go to SERVER MANAGER and then go to FEATUES go to REMOTE SERVER ADMINISTRATION TOOL and then mark checkbox ACTIVEVE DIRECTORY DOMAIN CONTROLLER TOOLS. This feature available in windows 2003 by default but it was not part of the default installation for windows 2008 server.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: