Changing the Recovery model to SIMPLE for multiple database in a single Query: MSSQL Server

If you executed the below query then you will get the output of another query which you need to execute to change the Recovery model from Full to Simple.

Query:
set nocount on go select 'ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE' from sys.databases where database_id > 6 and recovery_model_desc = 'FULL'

Output:
ALTER DATABASE [DB1] SET RECOVERY SIMPLE
ALTER DATABASE [DB3] SET RECOVERY SIMPLE
ALTER DATABASE [DB6] SET RECOVERY SIMPLE
ALTER DATABASE [DB7] SET RECOVERY SIMPLE
ALTER DATABASE [DB9] SET RECOVERY SIMPLE

Note:  The SQL Server have around 15 database and the DB 1,3,6,7,9 only running in Full recovery model. So that you will got the above output copy it and executed like the below.

Query:
Use Master
ALTER DATABASE [DB1] SET RECOVERY SIMPLE
ALTER DATABASE [DB3] SET RECOVERY SIMPLE
ALTER DATABASE [DB6] SET RECOVERY SIMPLE
ALTER DATABASE [DB7] SET RECOVERY SIMPLE
ALTER DATABASE [DB9] SET RECOVERY SIMPLE
Once the above executed successfully please cross verify it randomly by manual check. Now all the database in the SQL server will set to Simple recovery model.

Note:
If the recovery model is in Simple mode then you can't recover the database fully.

Thanks!!!

MSSQL query for changing recovery model of all the databases in a server.

The below SQL query will help you to change the recovery model of all the databases in the server.

Recently i noted one of my development server is having 160+ databases and it occupied more than 600 gig space in HDD.

In development box no need of transaction log backup and every day full backup maintenance plan is running.

tried to change all the database recovery model to SIMPLE from Full or BULK LOGGED and the below script helped me. Hope it will help me in after sometime as well as you.

declare

@dbnm 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 name != 'tempdb'

-- Using a cursor to loop through database names and change recovery model

open cursor_db

fetch next from cursor_db into @dbnm

--While Loop with Alter database command

while @@fetch_status = 0

begin

--print 'database is ' + @dbnm

set @sql='alter database ' + @dbnm + ' set recovery simple'

print 'sql is ' + @sql

exec (@sql)

fetch next from cursor_db into @dbnm

end

--clean up objects

close cursor_db

deallocate cursor_db

Thanks.

Steps for Backup and Restore of SQL database from SQL server 2008 / 2008 R2 to 2012.

Steps of Performing Backup activity in SQL server.

1. Login to the Database server through SSMS.
2. Right Click the Database -> Tasks -> Backup (Refer Figure 1)
3. Choose the path where the backup file need to place. (Refer Figure 2)
4. Choose the Full in Backup Type and Select Copy-Only Backup in Production Environment. (Refer Figure 2)
Note: Move the backup file of SQL database to any file share or the destination server HDD.

Steps of Performing Restore activity in SQL server 2012.

1. Login to the Database server through SSMS of 2012.
2. Right Click the databases (Next to the server name) -> Select Restore Database.
3. Select the Device and provide the backup file as a input to restore database window.
4. Give a unique Database name to identify. In this scenario I gave “WSS_Content_5002_MigratedfromSP2010”
5. Once Database restored successfully, close the SSMS.