Set DBs to Simple and shrink the logs

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64),
@logfile varchar(128)

declare c1 cursor for
SELECT  d.name, mf.name as logfile--, physical_name AS current_file_location, size
FROM sys.master_files mf
inner join sys.databases d
on mf.database_id = d.database_id
where recovery_model_desc <> 'SIMPLE'
and d.name not in ('master','model','msdb','tempdb')
and mf.type_desc = 'LOG'
open c1
fetch next from c1 into @dbname, @logfile
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
print @isql
--exec(@isql)
select @isql='USE ' + @dbname + ' checkpoint'
print @isql
--exec(@isql)
select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
print @isql
--exec(@isql)

fetch next from c1 into @dbname, @logfile
end
close c1
deallocate c1


--By Todd Erickson

SQL Server set DB into Single or Multi User mode.

ALTER DATABASE DB_Name_Here

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Or Multi_USER

SET Multi_USER WITH ROLLBACK IMMEDIATE

Script to find out the last restore date

use msdb;
select
      DBRestored  = destination_database_name,
      RestoreDate = restore_date,
      SourceDB    = b.database_name,
      SourceFile  = physical_name,
      BackupDate  = backup_start_date
from RestoreHistory h
inner join BackupSet b
      on h.backup_set_id = b.backup_set_id
inner join BackupFile f
      on f.backup_set_id = b.backup_set_id
where h.destination_database_name='DB_NameHere'
order by RestoreDate desc
go
 

Get Names & Locations of Data and Log Files

Every now and then I want the file names for when I am restoring a database from a different server. Plug in your DB name and this gives where the data files and logs are and what they are called.

USE Master
GO

SELECT

CONCAT('move','''',space(1),name,'''',space(1),'TO','''',SPACE(1),Physical_name,'''',',')

FROM sys.master_files

WHERE db_name(database_id) = 'DB_Name_Here'