Virtual Architects Support Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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

<note tip>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.</note>

===== Database Restore =====

**[[https://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward |Log vs. Differential Restores]]**

<note important>Many MSSQL backup systems (like Ahsay) **will only restore the files required for you to perform the actual database recovery**.</note>

<note tip>It is convenient to use the **Microsoft SQL Server Management Studio** to perform the actual database recovery.</note>

<note important>When performing a recovery, it is important to **order the files properly** for a successful recovery.</note>

==== 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.

<file>
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
</file>
Virtual Architects Support Wiki

User Tools

Site Tools

Page Tools
