Full Backup via SSMS (SQL Server Management Studio):
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
- - Backup component: Database
- Under Destination (heading):
- - Back up to: Disk
- - 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 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 T-SQL:
> RESTORE DATABASE [ProjectApp20251003] FROM DISK = N'W:\backups\ProjectApp20251003.bak' WITH FILE = 10, NOUNLOAD, STATS = 5
> GO
previous page
|