June 8, 2010 at 8:50 pm
1.create database ABC
2.backup database ABC to disk='D:\backup\DB\ABC.bak'
Here i created a new database 'ABC'.NOW my DATABASE is empty(no objects where created), i am taking full back up,the out put i am getting
OUTPUT:
Processed 152 pages for database 'ABC', file 'ABC' on file 1.
Processed 2 pages for database 'ABC', file 'ABC_log' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.267 seconds (4.698 MB/sec).
MY dout is even though my database is not having any objects,why it is backup 152 pages data file,
i did't done any transations even why it backuped 2 pages of log files.
2) AGAIN when i take the LOG BACKUP ,with out doing any transaction again it showing that " 2 log pages where backuped"
backup log ABC to disk='D:\backup\DB\ABC.bak'
OUTPUT:
Processed 2 pages for database 'ABC', file 'ABC_log' on file 2.
BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.219 MB/sec).
3)AGAIN when i take the log backup ,it showing 'ZERO pages backuped'(no transation were done)
backup log ABC to disk='D:\backup\DB\ABC.bak'
OUTPUT:
Processed 0 pages for database 'ABC', file 'ABC_log' on file 3.
BACKUP LOG successfully processed 0 pages in 0.033 seconds (0.000 MB/sec).
can any one tell why it backuped 152 pages ,2 pages even though database is empty.
after taking taking log backup second time it showing zero pages .WHY?
plz answer me any one
Thankyou
June 8, 2010 at 10:27 pm
When you create a database it will create a copy of model database with new name and settings what every you set, and whenever a new database is created it is not 100% empty it will have all sys tables, DMV's, DMF's and etc those objects also need some pages
And when you do a full backup sql server will perform following steps
1.When backup command is executed, SQL server locks the database, blocking all transactions and check points is issued on the database, which writes all deity pages to disk. This ensures that all committed transactions up to the point of backup command will be part of the full backup.
2.SQL Server makes a mark in the transactional log to point where the full back begins. This is important in the recovery process; SQL Server will use this mark to help validate what combination of files can or cannot be used to restore the database from the full backup, releases lock on the database.
3.The data is then read out of the database files to the backup files, which is plain text file that is create on disk or tape.
4.Lock the database, blocking all transactions, and issues checkpoint.
5.When full backup is completed SQL Server makes another mark in the transaction log.
6.Release database lock.
7.Extracts all transactions between the two log marks and append to the backup, by maintaining the consistency of backup for e.g., if someone modifies the data on the pages while backup process, and if he restores the backup he might lose the modified data.
and if you look at the point 2 and 5 you can see that some thing are wrote on the log file so your log is not empty.
I hope its help full to you and one small suggestion books online and some online content will give very good information about these thing in details, please try to research.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply