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.
- 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
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
- Copy the SQL Server 2000 setup files on the Local Drive of the server from Segotn10063
- Run the Rebuildm.exe command as shown below
- When you run this command below shown window will open
- Now specify the server name, the location of the setup and the collation.
- Click on the rebuild button.
When you click on rebuild a new window will open “Configuring Server” mentioned below.
The configuration will take some time and executes many steps during this procedure, wait till it completes.
- Close all SQL Related application\windows
- When the process completes start your SQL Service in Single user mode.
SQLServr.exe –c –m –T3608
- Check all files TDP SQL related files like “dsm.opt”, instance.bat, tdpsql.cfg etc
- Open TDPSQL GUI
- Now start the process of restoring Master Databases as mentioned below using TDP SQL
- Overwrite the existing Master Database by using restore into option
- When the Master Database restore will finish your SQL Server stops and comes out from Single User Mode.
- Now when you start SQL Server you can see all user databases in suspect mode, as their respective files are missing.
- Now Restore Model Database using TDP SQL from the last backup available
- Now Stop SQL Server Agent, and restore MSDB database using TDP SQL (No Need to start SQL Server in Single User Mode)
- After restoration of MSDB start SQL Server agent.
At this point you have now restored all SQL Server Logins and Jobs
- 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.
How to FIX this issue:
- 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.
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.
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.
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.
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”.
Under that key, there is a value named “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.
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.