Understanding SQL Server related Windows Registries

SQL Server related information from Windows registries

The Windows Registry is a hierarchical database that stores configuration settings and options on Microsoft Windows operating systems. It contains settings for low-level operating system components and for applications running on the platform that have opted to use the registry. The kernel, device drivers, services, SAM, user interface and third party applications can all make use of the registry. The registry also provides a means to access counters for profiling system performance.

See Wikipedia Article Windows Registry.

To View SQL Server related registries.

Go to Run and type regedit

HKLU=>Software=>Microsoft=>Microsoft SQL Server

To find valuable information about edition, version, patch label etc. Traverse as below pic

 

To Change  Default authentication mode 1-window authentication 2-SQL server Authentication

Edit Login Mode registry value.

Value= 1 for Window Authentication

Value=2 for SQL server Authentication

Restart SQL server and agent services your default SQL server authentication mode has been changed. You can change for each instance of SQL server.

Note: Serious problems might occur if you modify the registry incorrectly. So do not modify any registry if you do not understand it.

However you can achieve same using xp_instance_regwrite stored procedure to edit registries.as

The below query is used to change authentication mode from mixed to Windows only.

EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', 
REG_DWORD, 
1;

The below query is used to change authentication mode from windows only to Mixed back.

EXEC xp_instance_regwrite 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', 
REG_DWORD,
2;

Each time you execute query,restart the server and check.

To find installed instances on current machine, traverse as below pic

9 points before backing up SQL Server databases

Backing up SQL Server Databases

 Backing up your SQL Server database is essential for protecting your data. The word Backup refers to “Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.”

SQL server backup.

You should consider some below points also when making planning for database backups.

1.   Firstly set your database backup plan, In plan do not only include backup databases but also include backups of other things like reporting services, analysis services ,integration services and other third party tools. You should back up complete SQL server environment.you can use third party tools to achieve the same.

2.   After that you should include your physical backup storage location protection policy, since this storage has all data of your databases. It may be very dangerous if it is getting in wrong hands, because the can easily restore this and access your confidential data like Credit card nos. , passwords etc. Also it will be better policy if you restricts from being copied your database backups to another location. However there are some third party database backup tools that allow backup encryption.  

3.   Store database backup another location [some other drive] from database location.so that if in any chance to corruption of files on database drives your back will be secure.

4.   Do not write multiple backups in one physical file so create one backup for one database so that if a backup file for single database has been corrupted it does not affect other backups.

5.   Do not allow multiple users to access on creating backups but if you allow then monitor who is creating backups and where they are being created.

6.   Also backup system databases at least master and msdb since the master database stores security information as well as metadata about the other databases. Msdb stores information about jobs, operators, and alerts etc.

7.   Monitor backup failure, backup size and backup time regularly, if you scheduled backup using Job Scheduling then create an alert to your mail id if job fails.

8.   Testing the entire restore process to verify backup periodically.

9.    Remove older backups time to time to free disk space.

To create and automate backup using SSMS, follow below steps

1.       Go to databases in object explorer and select your database to backup.

        

    

2.       Right click on database =>Backups

3.       In destination, you must select backup device that can be any drive or tape.

4.       Go to options

5.       In options you can set over all existing back up options to overwrite however ignore this rule because if backup corrupted you lost all of backups.

Choose Verify backup when finished.

You also can select Backup compression option from here.

6.       Click ok.The backup file is created on your defined destination.

7.       You can also schedule this task using Script

8.       Set all option as your requirement in job scheduling

9.       Test your job by running once. If it successfully runs, you have successfully automated your backup.

SQL Server Database Backup Resources.
Backup SQL Server

SQL Server Maintenance Solution’s stored procedure for backing up databases

A White paper on top SQL server Backup mistakes

Red gate SQL Backup Pro

NetVault LiteSpeed for SQL Server

Querying Microsoft SQL Server: T-SQL

Querying Microsoft SQL Server: T-SQL: T -SQL is advancement of SQL. It  include    procedural  programming,  local variables , various support functions for string processing, d...