How to take Full, Differential & Transaction log backup & restore it using T-SQL

Full backup:-

USE "TESTDB"
GO
BACKUP DATABASE "testdb"
TO DISK = 'E:\TESTDB02101.BAK'
GO


Differential backup or incremental backup:-


Prerequisites:-
Creating a differential database backup requires that a previous full database backup exist. If the selected database has never been backed up,
run a full database backup before creating any differential backups. For more information, see Create a Full Database Backup (SQL Server).

USE "TESTDB"
BACKUP DATABASE "TESTDB"
TO DISK = 'E:\TESTDB02101_DIFF.BAK'
WITH DIFFERENTIAL
GO



Transaction Log backup:-


Recommendations:-
If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data and to keep the transaction
log from filling. This truncates the log and supports restoring the database to a specific point in time.


USE "TESTDB"
BACKUP LOG "TESTDB"
TO DISK = 'E:\TESTDB02101_TRANS.TRN'
GO




First drop the database and then start the restoration process.
drop database "TESTDB"
GO



Now to restore the database using transaction log use the following T-SQL scripts.


RESTORE DATABASE "TESTDB"

FROM DISK = 'E:\TESTDB02101.BAK'

WITH

NORECOVERY;
GO
-----
RESTORE DATABASE "TESTDB"
FROM DISK = 'E:\TESTDB02101_DIFF.BAK'
WITH NORECOVERY;
GO
-----
RESTORE LOG "TESTDB"
FROM DISK = 'E:\TESTDB02101_TRANS.TRN'
WITH RECOVERY;
GO





Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.