RECOVERING A DATABASE WITH SUSPECT STATUS

To RECOVER a DATABASE STATUS you need to run sp_resetstatus. If you don’t have one, below are the steps on how to create and execute sp_resetstatus on WINDOWS platform for SYSBASE server.

1) Go to start --> run --> cmd
2) At cmd prompt type this command

C:\>isql –Uusernamehere –Sservernamehere

3) To create and execute sp_resetstatus, take the following steps
At command prompt

1> use master
2> go
1> sp_configure "allow updates", 1
2> go


4) Create the sp_resetstatus procedure in the master database.

Start --> program --> Sybase --> Sybase central java edition

After that, write the following to the sp. Name it as sp_resetstatus:

CREATE PROC sp_resetstatus @dbname varchar(30) ASDECLARE @msg varchar(80)IF @@trancount > 0BEGINPRINT "Can't run sp_resetstatus from within a transaction."RETURN (1)ENDIF suser_id() != 1BEGINSELECT @msg = "You must be the System Administrator (SA)"SELECT @msg = @msg + " to execute this procedure."RETURN (1)ENDIF (SELECT COUNT(*) FROM master..sysdatabasesWHERE name = @dbname) != 1BEGINSELECT @msg = "Database '" + @dbname + "' does not exist!"PRINT @msgRETURN (1)ENDIF (SELECT COUNT(*) FROM master..sysdatabasesWHERE name = @dbname AND status & 256 = 256) != 1BEGINPRINT "sp_resetstatus can only be run on suspect databases."RETURN (1)ENDBEGIN TRANUPDATE master..sysdatabases SET status = status ^ 256WHERE name = @dbnameIF @@error != 0 OR @@rowcount != 1ROLLBACK TRANELSE BEGINCOMMIT TRANSELECT @msg = "Database '" + @dbname + "' status reset!"PRINT @msgPRINT " " PRINT "WARNING: You must reboot SQL Server prior to "PRINT " accessing this database!"PRINT " "ENDGO

5) after the procedure is created, at the command prompt type

1> use master
2> go
1> sp_configure "allow updates", 0
2> go


6) to run sp_resetstatus, at the command prompt type

1> sp_resetstatus "databasename"
2> go

SYSTEMS DATABASE RELOCATION

note: use sp_helpdb 'DATABASE name' to check the location of the database before you start and do backup all the DATABASES first.


STEP 1:master

1) stop SQL SERVER
2) copy master to the new location
3) check the registry parameter :

open regedit(modify registry)
-->local machine -->software -->microsoft -->mssqlserver -->MSSQLSERVER -->parameter
change below parameter

-dD:\Microsoft SQL Server\MSSQL\data\master.mdf (-d should remain)

-lD:\Microsoft SQL Server\MSSQL\data\mastlog.ldf (-l should remain)
4) start sql server


STEP 2:tempdb

1) query analyzer

use master

go

Alter database tempdb modify file (name = tempdev, filename = 'h:\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'h:\templog.ldf')

go

Results after command completed:

"File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.File 'templog' modified in sysaltfiles. "

You may delete the old file after restarting SQL Server.
2) restart sql server (do sp_helpdb to check the changes)
3) delete the old physical files.


STEP 3:msdb and model

1) if the db is hardened :open regedit(modify registry)
-->local machine -->software -->microsoft -->mssqlserver -->mssqlserver -->parameter-->rightclick, add new string value name SQLArg3 and value data -T3608
2) restart sql
3) query analyzer (pls close enterprise manager)
note: make sure the physical file exists at the new location.
example:

sp_detach_db xxx, 'true'goEXEC sp_attach_db 'xxx','c:\xxx.mdf','c:\xxx.ldf'
sp_detach_db yyy, 'true'goEXEC sp_attach_db 'yyy','c:\yyy.mdf','c:\yyy.ldf'
(do sp_helpdb to check the changes)
4) delete newly created string value name SQLArg3 and value data -T3608
5) restart sql server