How to Taking Full BackUp our Database through Query?
Here I explained about Setting the Database recovery mode to BULK_LOGGED and How to Backup our Database through Query and Taking Full BackUp
How to Backup our Database through Query
USE MASTER
GO
Creating new Database named : SampleDBCreating new Database named : SampleDB
CREATE DATABASE SampleDB
GOSetting Database recovery mode to BULK_LOGGED
ALTER DATABASE SampleDB SET RECOVERY BULK_LOGGED
GOCreating a table named : Table1
USE SampleDB
GO
CREATE TABLE table1
(
id INT IDENTITY(1,1),
column1 VARCHAR(10)
)
GO
Inserting a record into "Table1"
INSERT table1 VALUES('DNS')
GOTaking Full BackUp
BACKUP DATABASE SampleDB TO DISK='e:\share\Full.Bak' WITH INIT
GOOutput
Processed 160 pages for database 'SampleDB', file 'SampleDB' on file 1.
Processed 5 pages for database 'SampleDB', file 'SampleDB_log' on file 1.
BACKUP DATABASE successfully processed 165 pages in 0.075 seconds (17.148 MB/sec).
Performing some BULK operation
SELECT * INTO table2 FROM table1
GOInserting data into "table2"
INSERT table2 VALUES('SQL DBA')
GOIdentifying current timestamp
SELECT GETDATE()
GO
-- 2011-04-11 17:28:30.043Taking Log BackUp
USE MASTER
GO
BACKUP LOG SampleDB TO DISK='e:\share\Log.Trn' WITH INIT
GOOutput
BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize
NULL NULL 2 NULL 0 1 2 uno TestSr\SQL2008 db1 655 35:58.0 207872 2.6E+16 2.6E+16 2.6E+16 2.6E+16 37:03.0 37:04.0 52 0 1033 196609 100 4608 10 0 4000 SCUFHOGPCELL 513 CBBC36EE-BBC9-4514-A5EF-D6E3D9EFDCC7 BCAE9BE8-1DD2-4427-AC5F-8CFCCFDF4D0B SQL_Latin1_General_CP1_CI_AS BCAE9BE8-1DD2-4427-AC5F-8CFCCFDF4D0B 1 0 0 0 0 0 1 0 0 0 BCAE9BE8-1DD2-4427-AC5F-8CFCCFDF4D0B NULL BULK-LOGGED NULL NULL Transaction Log 8D3567C4-699C-42D9-951F-FD6ECF4D339B 207872 Droping the table
USE SampleDB
GO
DROP TABLE table2
GOIdentifying the Bulk-Operation performed any, from the Log backup file
USE MASTER
GO
RESTORE HEADERONLY FROM DISK='e:\share\log.trn'
GOOutput
Processed 160 pages for database 'DB1', file 'DB1' on file 1.
Processed 5 pages for database 'DB1', file 'DB1_log' on file 1.
RESTORE DATABASE successfully processed 165 pages in 0.006 seconds (214.518 MB/sec).Result from the above statement
We have a column called "HasBulkLoggedData", The column has value '1', means the Log backup contains Bulk operation
Performing DATA restore on SampleDB database with NoRecovery Model for continue with Log restore
RESTORE DATABASE SampleDB FROM DISK='e:\share\Full.Bak' WITH NORECOVERY, REPLACE
GOOutput
Processed 160 pages for database 'SampleDB', file 'SampleDB' on file 1.
Processed 5 pages for database 'SampleDB', file 'SampleDB_log' on file 1.
RESTORE DATABASE successfully processed 165 pages in 0.006 seconds (214.355 MB/sec).Performing LOG restore on SampleDB database upto Partiular time
RESTORE LOG SampleDB FROM DISK='e:\share\log.trn' WITH RECOVERY, STOPAT ='2011-02-27 19:55:28.760'
GO
RESTORE DATABASE SampleDB WITH RECOVERY
GOOutput
RESTORE DATABASE successfully processed 0 pages in 0.309 seconds (0.000 MB/sec).
