How to avoid reboot Physical Server during install SQL Server if required?

Hi All We have seen so many time when ever install SQL Server sometimes SQL Setup ask required reboot Physical Server else you can't go ahead with installltion becuse next button get disable. So if its a critical server or you don't want to reboot this server but SQL Server installation also very important you cant wait for long so how can you proceed.

Here is a one hack that you can use but make sure this is not suggested but sometime we use undocumented command as well in SQL server so in critical time we can go ahead .

So how we can avoid this without reboot the server see below :-

Step 1:-

Required Reboot Server

I followed the following steps to solve this problem,

1. Run Regedit.exe on Run.

2. Go to, HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager , and Select"Session Manager, and you will see the entry called, PENDING FILE RENAME OPERATION.

3. Take backup of registry value first.

4. Then, If PENDING FILE RENAME OPERATION has any values just remove it and clear the value.

See below screen shot for more info :- 

2. Go to, HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager , 

"PENDING FILE RENAME OPERATION."

If now you can go back the same installation windows and Click  on Re-run button you will see that reboot required will gone  and you can go ahead with installation without without reboot whole server.

How to restore huge backup if its stop in between restoring time due to power failure,restart Server or connection failure?

Today I found one interesting thing related to restore backup failure example you are restoring a huge backup on SQL server and you found that only 10 % backup restore pending that we be restore soon and you send the email to client with full confidence backup will be finish with in 30 or 40 minutes. then suddenly backup got failed due to some network issue or power failure  restart the server by somebody mistakenly . So how you will proceed again  .the first thing come in mind we have to start restore operation from beginning and need a lot of time to restore .

But here we can use one technique if you don't want to restore backup from starting.

Here is great feature provided by Microsoft that is WITH RESTART option.

You can restart the failure backup from the same point where is got stopped.

See below command :-

You can see in below scree shot the Advetureworks2012 database in restoring state because server got stopped when it was restoring from backup and when we strat server again it gone in restoring state . 

Here you can restart your interrupt backup again with the following command and it will work as below :)

RESTORE  DATABASE AdventureWorks2012 FROM DISK='D:\AdventureWorks2012.BAK' WITH RESTART

Thanks :)

How to find how many user are mapped with particular login in SQL Server?

Sometime we need to check how may user has been associate with particular login .So ,here is cool system SP's through we can check this .

EXEC MASTER..sp_MSloginmappings [ashish-pc\ashish]

Out Put :-

EXEC sp_helplogins 'ashish-PC\ashish'

Use “Template” folder data files rather than rebuilding system databases in SQL Server 2012 during Master database corruption or other system DB corruption

I found one interesting thing to recover master database

There are multiple way to recover master databases but I found one interesting way that is also recommended way by MS.

As everyone know master database is really critical database to start SQL Server instance if master database got corrupt then your SQL Server Instance will not start.

There are some way through we can recover our Master database or start SQL Server instance:-

1)           We can restore backup of Master database if we are able to restart SQL Server in single user mode like if .ldf file missing or corrupt or any page corruption.

2)           We can copy and replace the .mdf and .ldf file from another server where the same SQL Server build running and it will work in case you don’t have backup or you are enable to restart SQL Server in single user mode.

3)           Most complicated and dangerous technique from my side that is Rebuild system database dangerous because whenever we rebuild the Master database it rebuild all the system database as well so before rebuild the master database it mandatory to take all the system and user database backup or copy paste all the .mdf and .ldf file in safe location if you didn’t take the backup then you will lose the all data and database from instance.So we need to take some per-step and post steps before rebuild master databases.

4)           It really interesting solution from my side and its provided by Microsoft as a system template.Have you ever heard about system database templates in SQL Server that exist in :-

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates

So, what next how we can recover master database by provided system database template.

Step 1>Copy Master .mdf and .ldf file from template folder and replace with old master file in the following path.

Now if you try to restart SQL Server it will not start because master database contain the all other database information as well so still we can’t start SQL Server because to start SQL Server we need other system database as well means master database contain all system database info ,so here we copy the master .mdf and .ldf file from template folder and we need to modify the other system database path as well that will help to start SQL Server.

If you try to start SQL server you will get below error:-

System error 1067 has occurred.

The process terminated unexpectedly.

If you check the windows event log you will see error like:-

Error states SQL Server enable to find path of Model database due to SQL Server unable to start.

So, now question is how will be achieve this then .Here is the main twist you have to modify the path of all system database and once it’s done it will work as it is.

So start how to achieve next steps:-

Start SQL Server with single user mode with below command by CMD:-

C:\Windows\system32>NET START MSSQLSERVER /m /t3608

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Once it start with Single user mode run below command to modify the path of all system database.

C:\Windows\system32>sqlcmd

1> ALTER DATABASE model  MODIFY FILE  ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf')

2> go

The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf');

2> go

The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now you can check the path for all database where they exist to before modify with following command.

1> select * from sys.master_files

2> go

You can see below tempdb path in strange location

TempDB database Path

Tempdev

   e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\tempdb.mdf

Templog

 e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\templog.ldf

Model Database

For model we have already modify the path with above command so it’s showing in right drives

modeldev

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf

modellog

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf

MSDB database

Msdb still not modified so it’s showing in different location

MSDBData

e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf

MSDBLog

e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf

so same way we need to modify all database tempdb and MSDB as well.

If we ty to start SQL Server again without modify the path of tempdb SQl Server will not start and you can see below error in event viewer:-

C:\Windows\system32>NET START MSSQLSERVER

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service could not be started.

A service specific error occurred: 1814.

More help is available by typing NET HELPMSG 3547.

C:\Windows\system32>sqlcmd

1> ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

2> go

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog1.ldf');

2> go

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf');

2> go

The file "msdbdata" has been modified in the system catalog. The new path will be used the next time the database is started.

Same way modify MSDB as well and start Sql Server it will work and please restore master database backup if you have else you will loss all the login and users even your own access as well.

If you don’t have Master database backup then you will lose your own access as well from instance .So other way to create new login in SQL Server by run SQL Server single user mode.

Run SQL server Single user mode create login and provide admin access

Please check if your error log also working as expected most of time error log lose the permission and we get error like:-

SQL Server error: 

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

So it might be issue with your domain account so please provide "SYSADMIN"rights to account it will work.