Differences

This shows you the differences between two versions of the page.

--- computing:windows:mssql_backup [2012/01/20 21:54]
gcooper
+++ computing:windows:mssql_backup [2022/02/10 07:42] (current)
gcooper
@@ Line 1: / Line 1: @@
 ====== Microsoft SQL Server Backup ======
-===== Database Recovery Models =====
+**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>
-==== Script ====
+===== Database Restore =====
-Set all databases to the Full recovery model:
+**[[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]]**
-<file>
+<note important>Many MSSQL backup systems (like Ahsay) **will only restore the files required for you to perform the actual database recovery**.</note>
-/*--------------------------------------------------------------------------------
-Sometimes it is necessary to change properties of all your databases in one shot
+<note tip>It is convenient to use the **Microsoft SQL Server Management Studio** to perform the actual database recovery.</note>
-In this script, we use a system table to get all the database names on our server.
+<note important>When performing a recovery, it is important to **order the files properly** for a successful recovery.</note>
-Then we use a cursor to loop through all the records and then change the recovery
+==== MSSQL Management Studio ====
-model using Alter Database command.
---------------------------------------------------------------------------------*/
+**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
---Declaration of variables
+==== Transact-SQL ====
-declare
+|RESTORE DATABASE database_name FROM full_database_backup WITH NORECOVERY |complete database backup      |
-@dbnm sysname,
+|RESTORE DATABASE database_name FROM differential_backup WITH NORECOVERY  |differential database backup  |
-@sql varchar(100)
+|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 =====
--- Declare begin cursor to get the database names and get info from sys.databases catalog
+==== Script ====
-declare cursor_db cursor
+Set all databases to the FULL recovery model.  Replace the three occurrences of "full" with "simple" if desired.
-for select name from sys.databases where name != 'tempdb'
+<file>
+USE master
+GO
--- Using a cursor to loop through database names and change recovery model
+-- Declare a variable to store the value [database name] returned by FETCH.
+DECLARE @dbname sysname, @cmd varchar(1000)
-open cursor_db
+-- Declare a cursor to iterate through the list of databases
-fetch next from cursor_db into @dbnm
---While Loop with Alter database command
+DECLARE db_recovery_cursor CURSOR FOR
+SELECT name from sysdatabases
-while @@fetch_status = 0
+-- Open the cursor
+OPEN db_recovery_cursor
-begin
+-- Perform the first fetch and store the value in a variable.
+FETCH NEXT FROM db_recovery_cursor INTO @dbname
---print 'database is ' + @dbnm
+-- loop through cursor until no more records fetched
+WHILE @@FETCH_STATUS = 0
+BEGIN
+IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> 'full' and @dbName <> 'tempdb' BEGIN
-set @sql='alter database ' + @dbnm + ' set recovery simple'
+-- create the alter database command for each database
-print 'sql is ' + @sql
+SET @cmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY full'
-exec (@sql)
+-- alter each database setting the recovery model to full
-fetch next from cursor_db into @dbnm
+EXEC(@cmd)
-end
+PRINT @dbname
+END
---clean up objects
+FETCH NEXT FROM db_recovery_cursor INTO @dbname
+END
-close cursor_db
+-- close the cursor and deallocate memory used by cursor
-deallocate cursor_db
+CLOSE db_recovery_cursor
+DEALLOCATE db_recovery_cursor
 </file>

Virtual Architects Support Wiki

User Tools

Site Tools

Differences

Page Tools