microsoft sql server

There is some problem with the server's hard disk. I copy the database backup file to a new position in the server. When I attach the database I got an error prompt of Microsoft Sql-DMO 823 errors. View Log as follows:

Error: 823, Severity: 24, State: 5.

I know this may caused by the damage of a database file or hardware. I try the following steps to fix the database and got succes.

Assumes that the database name to be fixed is "mydb", then:

1. Run MS-Sql Enterprise Manager, create a new database named "mydb" same as the bad database's name, assume its physical path and name are:

D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Data.MDF
D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Log.LDF

2. Stop the database service

    net stop mssqlserver

3. Remove the new database's files (D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Data.MDF & D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Log.LDF) we just created to a new directory (or just rename them). Then copy the bad database files here to replace the above files. Now the sql server would take the bad database as the new one we made.

4. Start the database service

    net start mssqlserver

5. Run Sql Server Enterprise Manager, see that the database is under a suspect state

6. Put the database into emergency mode using the following statement
in Sql Query Analyzer

    sp_configure 'allow', 1
    go
    reconfigure with override
    go
    update sysdatabases set status = 32768 where name = 'mydb'
    go

7, Rebuild the ldf log file. First delete the ldf file of the database, then run the following code in Sql Query Analyzer.
    DBCC REBUILD_LOG('mydb', 'D:Program Files\Microsoft SQL\Server\MSSQL\Data\mydb_Log.LDF')

then we found the new ldf file has already under 'D:Program Files\Microsoft SQL\Server\MSSQL\Data' directory.

8. Remove the database emergency mode, running
    update sysdatabases set status = 0 where name = 'mydb'
    go
    restore database mydb WITH RECOVERY
    go
    sp_configure 'allow', 0
    go
    reconfigure with override
    go

9. Using DBCC command to check the database file to see if there was anything wrong
    DBCC CHECKDB ('mydb')

Last, to run the Sql Server Enterprise Manager, expand the database item and found it has been changed to normal mode from the suspect state, open the relevant tables, the data has been restored.

When use this way to fixed your database, remember to change the 'mydb' to be your database name which need to be repaired. Have good luck! Thank you for visiting codeback.net. Codes here, back to take!

 

When you need to connect to a remote sql server, you could register a remote server in your sql anterprise manager. On the console root, expand the Microsoft SQL Server, right click "SQL Server Group", select "Create New SQL Server Register", then on the prompt dialog click "next", input your remote server ip in the available server text box, then add it to the right servers list. Click "next" button to continue, select one way of identify to log on the SQL Server. Click "next", input the user name and password. Then "next"->"next"->"Complete".

Ok, now you have complete the register of the remote server on your computer. If the connection is correct, everything could be ok, your could start to visit the remote sql server now. While sometimes you will get an error of "sql server does not exist or access denied".

To solve this problem, you could take the following steps to check the reason for.
    
  1. First, to ensure a ping command comes correct. If not, that mean your network has problems. You could contact to your network administrator to help to solve it.
  2. In windows command prompt, test this commmand: telnet sql-server ip address 1433. Well, after run this command, if you get any error message shows on the screen, still mean that your network has problems. Check if the server has a firewall or disable the 1433 port.
  3. If it does not work, then on the server computer, find the Microsoft SQL Server menu group, run "Server Network Utility". Enable the WinSock Proxy option, in "Proxy Address" option, input the server's ip and the Proxy Port, write 1433.
  4. If it still not work, on the client computer, find the "Microsoft SQL Server" menu group, run "Client Network Utility", click the "alias" tab and add an alias to the server(any name you want), then activate the TCP/IP option in the radio group of "network library". In Connection Parameters, enter the remote server's ip address. If you want to specify a port to the connection, deselect the "Dynamic decision to port" option and enter the port you want.
  5. If your Microsoft SQL Server has not patched, best to update it. To check your SQL Server's version, run the Sql Query Analyzer, run this command "select @@version". If the version is under 8.00.760 indicates that you have not patch the sql sp3.
   
To get the needed patchs, check the the url below:
All patchs:  http://www.microsoft.com/downloads/en/results.aspx?freetext=sql+server+patch&displaylang=en&stype=s_basic
Select the correct patch according to the version of SQL Server you install, here is mine:
http://download.microsoft.com/download/8/7/5/875E38EA-E582-4EE2-9485-B459CD9C0082/sql2ksp3.exe