Full Backup via SSMS (SQL Server Management Studio):
Notes:
- Restore SQL server must be same version or newer. Never try to restore a database to an older server than the backup was made from.
- The system command: sp_spaceused is used to estimate the full backup size.
- The full database backup includes the SQL logins, but if the restore target does not have the OS users, the restored SQL users are "orphaned". Transfer OS logins and passwords first.
- - If you have the error: Login failed for user 'Username'. (Microsoft SQL Server, Error: 18456), this is typical error code/message for an orphaned user.
- - If there are mapping issues, use ALTER USER . . . WITH LOGIN = . . . to perform the remap of SQL database users with OS users.
1. Navigate to Database:
Microsoft SQL Server Management Studio --> <servername> (left menu - twistie) --> Databases (folder twistie) --> <databasename>
2. Navigate to Backup Page:
<databasename> (right-click) --> Tasks (pop-up option) --> Back Up ... (sub-pop-up option).
3. On the Back Up Database - <databasename> page:
a. On General (tab left side)
- Under Source (heading):
- - Database: <confirm databasename>
- - Recover model: Full
- - Backup type: Full
- - Copy-only Backup: <checked>
- - Backup component: Database
- Under Destination (heading):
- - Back up to: Disk
- - Click Remove (button) to remove the current path (does not delete a back-up from that path)
- - Click Add ... (button)
- - On Select Backup Destination (dialog)
- - - Click . . . (button) and navigate to destination location, click OK (button)
- - - Click OK (button)
b. On Media Options (tab left side):
- Under Overwrite media (heading):
- - Back up to the existing media set (radio button): <selected>
- - - Overwrite all existing backup sets (radio button): <selected>
- Under Reliability (heading):
- - Perform checksum before writing to media (checkbox): <checked/selected>
c. On Backup Options (tab left side):
- Under Backup set (heading):
- - Name: <name-of-backup>
- - Description: <description of what why>
- Under Compression (heading):
- - Set backup compression: Compress backup
d. Click OK (button) to start backup.
4. Wait for backup to complete.
5. Click OK (button) on the successful backup pop-up.
Full Backup via Transact-SQL/T-SQL Client:
Example:
Server: MWNetSQL01
Database: ProjectAppDb
Today's Date: 20251026
> BACKUP DATABASE [MyDatabase] -- database name
TO DISK = N'W:\backups\MWNetSQL01\ProjectAppDb\ProjectAppDb_20251026_FullBackup.bak'
WITH INIT
, NAME = N'ProjectAppDb-Full Database Backup'
, COMPRESSION
, STATS = 10
, CHECKSUM
> GO
Notes:
TO DISK = Backup file name
WITH INIT = Overwrite backup file
, NAME = Backup Name
, COMPRESSION - Perform compression
, STATS = Display backup progress indication
, CHECKSUM = Perform pre-backup checksum validation
Other T-SQL Examples:
Differential Backup (After Full Backup):
> BACKUP DATABASE [ProjectApp] TO DISK = N'W:\backups\ProjectApp20251003Diff.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
Sample Restore Example Using SSMS:
1. Navigate to database:
Microsoft SQL Server Management Studio --> <servername> (left menu - twistie) --> Databases (folder twistie) --> <databasename>
2. Navigate to Backup Page:
<databasename> (right-click) --> Tasks (pop-up option) --> Restore ... (sub-pop-up option).
3. On the Restore Database - <databasename> page:
a. Under Restore options (heading), choose the desired restore method
e.g. Overwrite the existing database (WITH REPLACE)
b. Under Server connections (heading):
Close existing connections to destination database: <checked>
Sample Restore Example Using T-SQL:
1. Set Database to Single User Mode:
> ALTER DATABASE [ProjectApp20251003] SET SINGLE_USER WITH ROLLBACK AFTER 30
Note:
This will kill active sessions after the time-out, and kill new connections connecting during the period.
2. Perform restore:
> RESTORE DATABASE [ProjectApp20251003] FROM DISK = N'W:\backups\ProjectApp20251003.bak' WITH FILE = 10, NOUNLOAD, STATS = 5
> GO
3. Set Database back to Multi User Mode:
> ALTER DATABASE [ProjectApp20251003] SET MULTI_USER
previous page
|