Change Server Name for SQL Server Machine

As you may know, the internal SQL Server name is based on the physical server name, but changing the physical server name does not update the metadata within SQL Server. When connecting to SQL Server, we use the physical server name for the default instance and physical server name\INSTANCE Name for a named instance. If you change the physical server name, there is... » read more

Restore Database from Multiple Backup Files

You only have to specify MOVE if you want to move the file to another location. If you are restoring to the same drive and location, you don’t have to specify MOVE. Note: All backup files must be included in order to backup the database. Order of backup files does not matter. Renaming backup files... » read more

Database Corruption SQL Errors – 823, 824, 825

These alerts are focussed on successful access(IO) to the hard drives that SQL Server is using. If there are  any errors when reading or writing to the drives then one of these errors will be returned. Having the alerts on these errors means that any IO issues will be brought to the DBAs attention well... » read more

SQL Error 1205 Transaction was deadlocked

Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing... » read more

SQL Alerts

Events are generated by SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event. In addition to monitoring... » read more

Restore Database Master Key and Certificate

Database Restore Without Valid Certificate Database Restore of backup file will fail without valid certificate. Msg 33111, Level 16, State 3, Line 60 Cannot find server certificate with thumbprint ‘0xCC4472D721418B759E36A04034F0C2A95EE68DD6’. Msg 3013, Level 16, State 1, Line 60 RESTORE DATABASE is terminating abnormally. Restore Master Key and Certificate You also need to restore the Master... » read more

Remove Certificate

Note: Need to remove all current database using certificate before you can drop the certificate. Once the certificate is removed, all database restore of encrypted database will fail. Restore Database without Certificate Msg 33111, Level 16, State 3, Line 60 Cannot find server certificate with thumbprint ‘0xCC4472D721418B759E36A04034F0C2A95EE68DD6’. Msg 3013, Level 16, State 1, Line 60... » read more

Database Certificate

Note: Certificate is different for different databases. USE master database for TDE certificate. If expiration date not specified, default to 1 year. Note that there are several certificates listed, including one that looks like it’s for authentication (##MS_SQLAuthenticatorCertificate##). This is where SQL Server stores its certificates, in the master database. Obviously, if you create an... » read more