تغییر MS SQL Database Recovery Model در SQL Server

در این مقاله قصد داریم نحوه “تغییر MS SQL Database Recovery Model در SQL Server” را از طریق SQL Server Management Studio و Transact-SQL توضیح دهیم.

با توجه به اینکه لاگ های دیتابیس حجم بسیاری را اشغال می کنند، از طریق Recovery Model می توانیم تعیین کنیم که لاگ های SQL به چه صورتی ذخیره شوند. کلیه عملیات بک آپ گیری، ریستور و ریستور دیتابیس SQL Server بر اساس یکی از سه مدل بازیابی موجود است:

Simple

Full

Bulk-logged

در اکثر اوقات MS SQL از گزینه های Full و Simple استفاده می کند اما با این حال، هرزمان که نیاز باشد می توانیم مدل آنرا تغیر دهیم. پیشنهاد میکنیم قبل از انجام چنین تغییری، از دیتا، بک آپ تهیه کنید.

برای شروع ابتدا وارد SQL Server شده و به Instanse مدنظر کانکت شوید و سپس دیتابیس را انتخاب کنید و روی آن کلیک راست کنید و گزینه ی Properties را باز کنید و مطابق تصویر وارد تنظیمات Options شوید و Recovery Model را مطابق نیاز خود تغییر دهید.

راه دیگر استفاده از روش “Transact-SQL” می باشد. در این روش به Instanse مدنظر کانکت شوید و روی گزینه ی New Query کلیک کنید و کامند زیر را اجرا کنید:

SELECT name, recovery_model,recovery_model_desc FROM sys.databases

USE master

GO

ALTER DATABASE MODEL SET RECOVERY SIMPLE ;  

SELECT name, recovery_model,recovery_model_desc FROM sys.databases  where name='model'

چگونه می توان Recovery Model کل دیتابیس های سرور را تغییر داد؟

این روش برای تمام دیتابیس های سرور اعمال می شود و در کامند با دستور Alter اجرا می شود.

EXEC sp_msforeachdb "

IF '?' not in ('tempdb')

begin

exec ('ALTER DATABASE [?] SET RECOVERY FULL;')

print '?'

end

"

SELECT name, recovery_model,recovery_model_desc FROM sys.databases

در اکثر سرورها، Recovery Model روی Full ست شده است و به همین علت لاگ زیادی را ایجاد میکند. البته ممکن است این مود در گذر زمان به هردلیلی تغییر کند. برای اینکه متوجه شیم Recovery Model کدام دیتابیس های سرور ما روی SIMPLE ست شده است می توانیم از Dynamic T-SQL و  SQLcmd و یا Powershell خود ویندوز سرور استفاده کنیم.

در روش Powershell، نرم افزار را باز کرده و کامند زیر را وارد کنید :

#load the SqlServer module

Import-Module -Name SqlServer

#input file

$ServerList = "\\hq6021\c$\Server.txt"

#input the recovery model that is intended to search

$RecoveryModel='SIMPLE'

# To exclude system objects

$DatabaseFlag =0

ForEach ($instance in Get-Content $ServerList)

{

       $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

       $dbs=$s.Databases| where {$_.RecoveryModel -like "$RecoveryModel" -and  $_.IsSystemObject -eq $DatabaseFlag }

       $dbs | select @{Name="ServerName"; Expression = {$Instance}}, Name, RecoveryModel

}

پس از آن، لیست دیتابیس هایی که Recovery Model آنها SIMPLE است، مطابق تصویر بالا لیست می شود.