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 : SampleDB

Creating new Database named : SampleDB



CREATE DATABASE SampleDB
GO

Setting Database recovery mode to BULK_LOGGED



ALTER DATABASE SampleDB SET RECOVERY BULK_LOGGED
GO

Creating 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')

GO

Taking Full BackUp



BACKUP DATABASE SampleDB TO DISK='e:\share\Full.Bak' WITH INIT
GO

Output


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
GO

Inserting data into "table2"



INSERT table2 VALUES('SQL DBA')

GO

Identifying current timestamp



SELECT GETDATE()
GO
-- 2011-04-11 17:28:30.043

Taking Log BackUp



USE MASTER
GO
BACKUP LOG SampleDB TO DISK='e:\share\Log.Trn' WITH INIT
GO

Output

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
GO

Identifying the Bulk-Operation performed any, from the Log backup file



USE MASTER
GO
RESTORE HEADERONLY FROM DISK='e:\share\log.trn'
GO

Output


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
GO

Output


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
GO

Output


RESTORE DATABASE successfully processed 0 pages in 0.309 seconds (0.000 MB/sec).


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: