USE msdb; GO CREATE USER 'NewUser' FOR LOGIN 'ExistingLogin'; GO EXEC sp_addrolemember 'SQLAgentUserRole', 'NewUser'; GO
Even though is possible to change the recovery model of the master database to FULL as we can see with the next script:
USE [master]; GO ALTER DATABASE [master] SET RECOVERY FULL WITH NO_WAIT GO SELECT sd.name AS N'Database Name' , sd.recovery_model_desc AS N'Recovery Model' FROM sys.databases AS sd WHERE sd.name = N'master'; GO
When we try to make a Log Backup:
BACKUP LOG [master] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\master.bak' WITH COPY_ONLY , NAME = N'master-Full Database Backup' GO
We get the following error:
That’s because it is impossible to do LOG backups of the master database.
At MSDN it’s very clear that:
For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.
So what’s the point of having master database in full recovery model?
The best practice to apply to master database is using the simple recovey model and perform a backup as soon as possible after the following operations:
- Creating, modifying, or dropping any database
- Changing server or database configuration values
- Modifying or adding logon accounts
- Configuring end-points or linked-servers
An example for using xp_cmdshell… moving user databases. See the script…
It all starts with this error when we try to connect to SQL Server instance:
At SQL Server Configuration Manager, we could see that the SQL Server service is also stopped and, when we try to start the service, we got the following error:
So we must consult the Windows Event Log to see what is the problem of the instance (Use Windows key + R and type eventvwr.exe, as shown in the next picture)
Expand Windows Logs and then click in Application. (I also use a filter on the source column)
It’s now clear that we have a problem in master database (more information about master at MSDN)!
We could also try to start the instance with minimal configuration (the -f startup parameter, more info at MSDN) but the problem remains.
Step 1 – Repair System Databases
So, since we couldn’t start the service we must repair the master database. Repairing master database also rebuilds all of the system databases. This means that is very important to make a restore of this databases after the repair.
To perform a system databases repair we should use the SQL Server installation media but, in version 10 and later versions, the template databases are store at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates (in the case of the default instance of SQL Server 2014) and avaliable to usage in the repair process.
From command prompt, go to the following path:
- for SQL Server 2008:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008\
- for SQL Server 2008R2:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\
- for SQL Server 2012:
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\
- for SQL Server 2014:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\
And then we should run setup.exe with the following logic:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=«InstanceName» /SQLSYSADMINACCOUNTS=«SysSccount» [/SAPWD=«StrongPassword»] [/SQLCOLLATION=«CollationName»]
For this particular case, we don’t want to change the collation (and use the default server collation). So the command is:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DELL\Pedro /SAPWD=$trong3$t
After we run this command we don’t get any feedback (unless there are syntax problems or issues with parameters), so we must confirm the correct repair by open the file summary.txt at C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log (in the case of SQL Server 2014). An example of a failled repair operation:
And an exemple of what we are looking for:
INFO: By analysing SQL Server log we can see that were used some startup parameters:
After repairing the system databases we should apply the latest service pack and any applicable hot fixes.
The next step is making a restore of all of system databases, using their latest backup avaliable (if we don’t change the collation in the repair process). In this example we will only be performing the restore of master database. To do that is better to use the command prompt.
Step 2 – Restore Master Database
To do a restore of the master database we must start SQL Server service in single user mode. We can do that by using a NET command and using the -m startup parameter (learn more about SQL Server Service startup options at MSDN).
NET START MSSQLSERVER /m "SQLCMD"
We get this information:
INFO: At this moment if we try to connect to SQL Server with SSMS we will get the following error message:
Now connect to the instance using sqlcmd utility (more information about the usage of sqlcmd at MSDN).
HELP: If you don’t remeber the path of sqlcmd utility use:
dir /s sqlcmd.exe
As we can see in the image above, the path of sqlcmd utility is:
c:\Program Files\Microsoft SQL Serve\Client SQK\ODBC\110\Tools\Binn\
Type sqlcmd and we are ready to make the restore by typping:
RESTORE DATABASE master FROM DISK = 'D:\DBs\Backups\master.bak' WITH REPLACE; GO;
The screen should be like this:
As you can see from the above screen, after the database as been restored, the SQL Server service is stopped.
NOTE: You should also restore MSDB and MODEL database!! But that is not in the scope of this post.
Now we can start the instance in multi-user mode typing:
NET START MSSQLSERVER
The final step is recreating all the missing entries but, if we followed the best pratices for master database backup, this should not be a problem because it is a best practice to do a master database backup every time we change it.
So, at this point, we should be back online without any issues!
A step-by-step guide about how to create a new rule in Windows Firewall for SQL Server.