af ·0 kommentarer

Ret alle userdatabaser til Full recovery mode, for at få styr på din backup. Systemdatabaser er ekskluderet.

-- This script will set recovery model to Simple on all databases
use [master]
go

-- Declare variable for each database name
declare @databaseName nvarchar(128)

-- Define the cursor
declare databaseCursor cursor

-- Define the cursor dataset
for
select [name] from sys.databases where [name] not in ('master', 'tempdb', 'model', 'msdb')

-- Start loop
open databaseCursor

-- Get information from the first row
fetch next from databaseCursor into @databaseName

-- Loop until there are no more rows
while @@fetch_status = 0
begin
print 'Setting recovery model to Full for database [' + @databaseName + ']'
exec('alter database [' + @databaseName + '] set recovery Full')

-- Get information from next row
fetch next from databaseCursor into @databaseName
end

-- End loop and clean up
close databaseCursor
deallocate databaseCursor
go

af ·0 kommentarer

Find dine udvikleres datababaser-filer og hvilken database det hænger sammen med. Drift-teknikere elsker udviklere, især hvis de får adgang til serverne og opretter databaser med “hoved op i røven”. 

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id

Resultat:
sql2012 list db files