DBA,  Junior DBA

SQL server change single user mode or backup or recovery model of all database sql script

Advertisements

Following is the DBA script which generally used for most cases the action needed for all the databases like migration, upgrade etc.

Change the database recovery model and set to single_user mode.

Backup of all databases.

-- Dynamic SQL

select 'alter database ['+name+'] set single_user with rollback immediate' , * from sys.databases
where database_id not in (1,2,3,4)-- exclude system DBs

select 'alter database ['+name+'] set multi_usere' , * from sys.databases
where database_id not in (1,2,3,4)-- exclude system DBs

select 'alter database ['+name+'] set recovery simple' , * from sys.databases
where database_id not in (1,2,3,4)-- exclude system DBs

select 'backup database ['+name+'] to disk = ''B:\SQL_backup\'+name+'_full.bak''' , * from sys.databases
where database_id not in (1,2,3,4)-- exclude system DBs


 

We can write anything in the single quote and the separator is + symbol and can use any system objects like sys.databases and sys.objects.

This will be helpful for junior DBAs.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

1 + = 7

hacklink panel hacklinkal.org hacklink web shell download web shell archive hdxvipizle onwin venüsbet iqos sigara soma kömür seo hizmeti hacklink bahis forum piabellacasino
hack forum hacklink hacklink Shell download cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı saricahali.com.tr cami halısı cami halısı cami halısı cami avizesi cami avizesi cami süpürgesi cami süpürgesi cami ısıtma cami ısıtma evden eve nakliyat evden eve nakliyat evden eve nakliyat evden eve nakliyat ofis taşıma yurtiçi evden eve nakliyat yurtiçi evden eve nakliyat içel evden eve nakliyat Evden Eve Nakliyat Mng Evden Eve Nakliyat seo hizmeti Onwin casibom Rulet Casino Slot Oyna Bahigo 1xbet Deneme bonusu Bahis siteleri Maç özetleri Bahsegel Canlı Casino Siteleri Sweet Bonanza Blackjack Casino Rulet Oyna Canlı Casino takipçi satın al exeboost.com google.com.tr