Table of Contents

Microsoft SQL Server Backup

SQL Backup Background: https://wiki.ahsay.com/doku.php?id=public:microsoft_sql_database

Download SQL Server Management Studio: https://msdn.microsoft.com/library/mt238290.aspx

In general, it is simpler and easier to restore a complete backup (single file) than a differential or log backup (multiple file). If your backup window and storage permit, you might choose to implement full, complete, single file backups every night.

Database Restore

Log vs. Differential Restores

Many MSSQL backup systems (like Ahsay) will only restore the files required for you to perform the actual database recovery.
It is convenient to use the Microsoft SQL Server Management Studio to perform the actual database recovery.
When performing a recovery, it is important to order the files properly for a successful recovery.

MSSQL Management Studio

Restore a Database Backup Using SSMS: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver15

Transact-SQL

RESTORE DATABASE database_name FROM full_database_backup WITH NORECOVERY complete database backup
RESTORE DATABASE database_name FROM differential_backup WITH NORECOVERY differential database backup
RESTORE DATABASE database_name FROM log_backup WITH NORECOVERY oldest log backup
RESTORE DATABASE database_name FROM log_backup WITH NORECOVERY next chronological log backup
… in chronological order
RESTORE DATABASE database_name FROM log_backup WITH NORECOVERY last log backup
RESTORE DATABASE database_name WITH RECOVERY recover the database

Database Recovery Models

Script

Set all databases to the FULL recovery model. Replace the three occurrences of “full” with “simple” if desired. 

USE master
GO

-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)

-- Declare a cursor to iterate through the list of databases

DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases

-- Open the cursor
OPEN db_recovery_cursor

-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname

-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> 'full' and @dbName <> 'tempdb' BEGIN

-- create the alter database command for each database
SET @cmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY full'

-- alter each database setting the recovery model to full

EXEC(@cmd)
PRINT @dbname
END

FETCH NEXT FROM db_recovery_cursor INTO @dbname
END

-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor
DEALLOCATE db_recovery_cursor