Generate Recovery Model Change Script for all databases

We thought of planning to change all our database recovery model from FULL/BULK-Logged to SIMPLE.

It is little painful to change it by using GUI by checking each database properties and change it like below if you more databases:

Do we have any chance to change it by using all databases by using some script JJ , yes, here is there script which will help us to do the same.

set nocount on

---To check the databases current recovery models

select name, databasepropertyex(name, 'Recovery') as RecoveryModel

from master.dbo.sysdatabases

order by name

--Declaration of variables

DECLARE @DBName SysName, @sql VarChar(100)

-- Declare begin cursor to get the database names and get info from sys.databases catalog

DECLARE Cursor_db Cursor FOR SELECT Name From sys.Databases WHERE databasepropertyex(name, 'Recovery')

not in ('SIMPLE') and Name not in('TempDB','model','master','msdb')

-- Now using the cursor to loop through database names and change recovery model

OPEN Cursor_db Fetch NEXT FROM Cursor_db INTO @DBName

--While Loop with Alter database command

WHILE @@fetch_status = 0

BEGIN--print 'database is ' + @DBName

SET @sql='ALTER Database ' + @DBName + ' SET Recovery Simple'

PRINT @sql

--exec (@sql)

Fetch Next FROM Cursor_db INTO @DBName

END--clean up objects

CLOSE Cursor_db

Deallocate Cursor_db

It will show you the current database recovery models

It will show you the alter database statement to change the recovery model to SIMPLE.

Copy  the statements and run it in new query window.

Note : If you want to execute the statement while generating them, just uncomment (exec @sql) in the above statement.

Please mail me if you are facing any issues in this regard so that I will try to help you.

Forgot the ‘sa’ password and none of the user is having access to the SQL server

Today I got one SQL request saying that they are not able to login into the SQL server 2008 R2 after installing the SQL server in Windows Server 2008 R2.

While talking to them I came to know the below issues:

·         They have installed the SQL server with default instance and it’s a fresh installation and no one have accessed the server yet.

·         They haven’t added any user Windows User id while installing the SQL server.

·         Have disabled the ‘sa’ account and forgot the password for it.

·         It is a SQL server 2008 R2 installed windows SQL server 2008 R2.

Resolution:

Open CMD by run as administrator (Right Click on Command PromptàSelect Run as administrator) as below:

Note : Incase of Windows Server 2003, we may need Windows administrator password.

It will open the cmd prompt à type Sqlcmd –S Server_Name as below and click on ENTER.

Ex: sqlcmd –S ABCDSQL01

 

Type user masteràEnteràgoàEnter as below, it will change the query window to master now.

  

To make sure whether you are the same server , please run SELECT @@SERVERNAME and check as above to confirm.

Type the below command and click on Enter

If you want to add some SQL User id , please follow the below steps:

                                                                                          

                                                                                                                                                      

CREATE LOGIN User_ID with Password=’Some Password’

Ex:  CREATE LOGIN DILIP with Password='Abcd@123'

EXEC sys.sp_addsrvrolemember @loginame = N'DILIP', @rolename = N'sysadmin'

  

If you want to add some Windows User ID, please follow the below steps:

CREATE LOGIN [Domain/userid] with Windows

Ex: CREATE LOGIN [corp\abcd] with windows

EXEC sys.sp_addsrvrolemember @loginame = N'[corp\abcd]', @rolename = N'sysadmin'

                                                                                                                                                                             

Note: @rolename , please specify the permission which ever you need , for example I have given sysadmin (FULL permissions to the user id).

After adding the user id , please try to check your access by login into the SQL server and add the user id which ever you want.

Finally issue is resolved and we are able to access the SQL server with the account.

Please let me know if you are facing any issue even after trying the above steps, I will try to help you to fix the issue.

Transaction Logshipping - Introduction

Logshipping Introduction:

•       It is a high available technique, and it is used to Shipping of logs from primary server to the secondary server.

•       Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.

•       An optional third server instance, known as the monitor server, records the history and status of backup and restore operations.

•       Additionally it will record the backup information in Primary server and restoration information in Secondary server in MSDB database on Logshipping tables.

Log shipping consists of three operations:

ü  Back up the transaction log at the primary server instance.

ü  Copy the transaction log file to the secondary server instance into some backup share (\\servername\Tlog_bkp ).

ü  Restore the log backup on the secondary server instance.

•       The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

•       A log shipping configuration does not automatically fail over from the primary server to the secondary server.

•       If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

•       We can use a secondary database for reporting purposes.

•       We can configure alerts for  with our log shipping configuration.

Primary Server & Database:

•       The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server.

•       The primary database is the database on the primary server that you want to back up to another server.

•       The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.

Secondary Server and Databases:

•       The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.

•       A secondary server can contain backup copies of databases from several different primary servers.

•       For example, a department could have five servers, each running a mission-critical database system. Rather than having five separate secondary servers, a single secondary server could be used. The backups from the five primary systems could be loaded onto the single backup system, reducing the number of resources required and saving money.

•        The secondary database must be initialized by restoring a full backup of the primary database initially when configuring the Transaction logshipping.

•       The restore can be completed using either the NORECOVERY or STANDBY option. This can be done manually or through SQL Server Management Studio.

Logshipping Modes:

Logshipping can be configured in 2 modes/ways:

No-Recovery Mode : This is also called a Non-operational mode, users may not able to access the database for querying. Database status would be in Restoring Mode.

Standby Mode  : To allow users read-only access to the standby database, choose Read only (Standby Mode); this means you can distribute queries across the standby server to reduce the primary server’s workload (but you cannot modify the data in the standby database and normally used this for reporting purposes). Database would be in Standby/read-only mode.

Logshipping Jobs:

We have 5 logshipping jobs

·         Backup Job (Primary Server) – Will take the transaction log backup from primary server database.

·         Backup Alert Job (Primary Server) – Will send us alert if the transaction logshipping backup job fails.

·         Copy Job (Secondary Server) – Copy the transaction logs from primary sever DB backup location to the backup share.

·         Restore Job (Secondary Server) – Will restore the database backup logs from the backup share.

·         Restore Alert Job (Secondary Server) – Will send us alerts if the restore job fails.

Advantages:

• Log shipping can be configured by using the Database Maintenance Plan Wizard. It’s just a BCR (Backup, Copy & Restore).
• Can be configured in No recovery mode (will not available for users to query) & Standby mode , available for read only purpose or else reporting purpose.
• Low Maintenance.
• You can ship the transaction logs from the primary server to multiple standby servers. This way, you further reduce the chances of downtime.

Disadvantages:

• Transactions at the primary and secondary may or may not sync until or unless if the backup ,copy and restore is very frequent.
• Possible data loss when the primary server fails. If the primary server becomes completely unusable, transactions that occurred after the last transaction log backup that was copied to the standby server are lost. For example, suppose that server A fails at 5 a.m. and you cannot get to it at all. If the last backup copied to server B was taken at 4:45 a.m., all transactions that occurred between 4:45 a.m. and 5 a.m. are lost forever.
• Some manual DBA work is required to bring the standby server online.
• Log shipping setup cannot be scripted. This means that you cannot mimic the production environment for testing purposes without going through the wizard screens.

Important Keys Points:

ü  You must have at least two SQL Server database engine servers or two database engine instances in your log shipping implementation.

ü  The database must use the full or bulk-logged recovery model.

ü  You must have a shared folder to copy the transaction log backups to.

ü  The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder.

ü  The SQL Server Agent account of the standby server must have read and delete access to the shared folder.

ü  The SQL Server Agent services must be running and configured with network credentials (such as a domain account)

How to configure logshipping was discussed in one of my earlier post ….. http://dilipparakala.blogspot.in/2013/02/setup-sql-server-transaction-logshipping.html

Setup SQL Server Transaction Logshipping

Logshiping :

Log shipping provides a way of keeping in step databases that reside on different servers, or on different instances on the same server.

·         You must have at least two SQL Server database engine servers or two database engine  instances in your log shipping implementation.

·         Note that you cannot ship transaction logs from a SQL Server 2005 database to a SQL Server 2000 database, because of the  differences in their structures (but you can ship from a SQL Server 2000 database to a  SQL Server 2005 database if you use No Recovery Mode .

·         It achieves this by automating the process of backing up data from a primary database on one server or instance and restoring the data to one or more secondary databases on another server or instance.

·         You can then use the secondary server as a ‘standby’ for when your primary server is unavailable.

·         Transaction log backups are performed on the database on the primary server (for example the production database).

·         The backup files are copied to a shared folder that can be accessed by both servers.

·         The backup files are restored to the secondary database on the standby server.

How to Configure Logshipping :

Step 1: Take the Full Backup of PRADIP DB (Primary DB)

Step 2: Create the DB with the name PRADIP_LS in the secondary server and restore the DB with the primary backup as below

restore database PRADIP_LS from disk = 'D:\Install_Software\PRADIP_LS\PRADIP.bak'

with move 'PRADIP' to 'D:\Install_Software\Instance\MSSQL11.DILIP\MSSQL\DATA\PRADIP_LS.mdf',

move 'PRADIP_log'  to 'D:\Install_Software\Instance\MSSQL11.DILIP\MSSQL\DATA\PRADIP_LS_log.ldf'

,replace

Step 3:

Configure the logshipping between Primary and secondary server by following the below steps:

Right Click on the PRADIP DBà Click on Properties à Go to Transaction Log shipping

Select the “Enable this as a primary ” Check box and Click on “Backup Settings”

Provide the backup location as above and Click on OK. Then Secondary Databases Tab will be enabled.
 

Click on Add button .

Make sure that you have restored you destination server (Secondary Server) with the Source Server (Primary Server) database backup.

 

Logshipping can be configured in two ways :

·         No recovery mode (will not available for users to query) .

·         Standby mode , available for read only purpose or else reporting purpose.

Logshipping Jobs :

Change the job ownership on the following jobs to SA

·         Transaction Log Backup Job.

·         Transaction Log Copy Job.

·         Transaction Log Restore Job.

·         Backup Alert Job.

·         Restore Alert Job.

Location of the jobs :

·         Tran log backup job lives on the Primary Server

·         Tran log copy job lives on the Secondary

·         Tran log restore job lives on the Secondary

·         The Backup Alert job lives on the Monitor

·         The Restore Alert job lives on the Monitor

·         The Log Shipping Monitor lives on the Monitor

Verify each job runs without failure before sending notification to the customer.

Please let me know if you have any other with logshipping and its issues.

Regards,
Dilip