Recover Jobs from a crashed SQL Server
I recently came across the scenario where a SQL server had crashed (with Windows) but the file system was intact, so we started restore of databases which was straight forward as easy as attaching to the new instance on different server. But we also had lot of jobs scheduled on the old server, and wanted to recover them as well, so after a doing a bit of search figured out that the jobs are saved in “msdb” system database.
- Create a dummy instance of SQL Server.
- Stop the instance.
- Replace the .mdf & .ldf files from the crashed server of the msdb database.
- Start the Server Instance as well as agent.
- voila, you have your jobs, now script those jobs and execute on new server
and you are good to go..
Reset your SQL Server login
Recently I faced a situation where the SQL Server was setup but no one knew the login, and the SQL server was not configured in Mixed Mode Authentication, so how do you recover from this situation other than re installing the instance,
Stop the running instance and then open up command prompt in Binn directory on the instance which would be something like
c:\Program Files\Microsoft SQL Server\MSSQL...\Binn
Run the following command
sqlservr.exe -m"SQLCMD"
This would start the instance in single user mode, now open up another command prompt in the same directory
and then run “SQLCMD”
and execute the following statements based on requirement
If you want to add a windows user use
CREATE login [DOMAIN\USERNAME] FROM windows; EXEC sys.sp_addsrvrolemember @loginame = N'DOMAIN\USERNAME', @rolename = N'sysadmin'; GO;
To add a SQL Server Login use (remember this works only if mixed mode authentication is enabled)
CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master]; EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin'; GO;
now exit SQLCMD and also do CTRL+C on first window to stop instance and restart the instance in regular mode.