Very often I need to change the recovery model on development servers from Full -> Simple as there is no need to backup the transaction data(because it is dev server). To accomplish this, normally you would right click a database node in sql server management studio -> options -> change recovery mode. But since I like to use t-sql directly, I decided to dig through the documentation to find out if there is a better way to do this and there is a definitely one.
In SQL Server, there is an undocumented(which means, this may change anytime without notice) store procedured named sp_MSForEachDB in the master database and is used to execute a single t-sql statement. The syntax is something like this:
sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand
and each parameter is defined below:
- @command1 is of type nvarchar and is the first command to be executed
- @replacechar is of type nchar and is the character in the first command which will be replaced by the Database name. By default it is '?'.
- @command2 is of type nvarchar and is an additional command which can run against each database
- @command3 is of type nvarchar and is same as @command2
- @precommand is of type nvarchar and is a command which runs before running any command on each database
- @postcommand is of type nvarchar and is a command which runs after all the commands are run on all databases
Except @command1, every other parameter is optional. Enough of theory, lets start using it. Below are few examples on how to use the stored procedure:
Print all the databasename names
exec sp_MSforeachdb 'PRINT ''[?]'''
Check orphans
exec sp_MSForEachDB '[?].DBO.SP_CHANGE_USERS_LOGIN ''REPORT'''
Change db owner to sa
exec sp_MSForEachDB 'USE [?]; EXEC SP_CHANGEDBOWNER ''SA'''
Print the physical names and attributes of files associated with the current database
exec sp_MSForEachDB 'USE [?]; EXEC SP_HELPFILE'
Run DBCC CheckDB on all the databases
exec sp_MSForEachDB "DBCC CHECKDB ('?')"
and finally to change the recovery model to simple for al l the databases, first run the below command and then copy the output and execute it
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY SIMPLE''; PRINT ''GO'''
Thats all for now and maybe next time, I will have the examples which use the additional parameters.
