backup command confusion

Question

backup command confusion

Aijaz Ahmed Mir

SSC Veteran

Points: 239
More actions
September 19, 2007 at 2:09 am

#180991

hi all,
i am living in the world of confusion, please help me

BACKUP DATABASE [master] TO [thursday] WITH INIT , NOUNLOAD , NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP DATABASE [msdb] TO [thursday] WITH NOINIT , NOUNLOAD , NAME = N'msdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP DATABASE [PROD] TO [thursday] WITH NOINIT , NOUNLOAD , NAME = N'prod backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP DATABASE [mi_dbadmin] TO [thursday] WITH NOINIT , NOUNLOAD , NAME = N'mi_dbadmin backup', NOSKIP , STATS = 10, NOFORMAT

the output for the command
RESTORE filelistonly FROM DISK = 'd:\SQL_back\thursday.bak'
is
master d:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf D DEFAULT 19529728 35184372080640
mastlog d:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf L NULL 2621440 35184372080640
the output of the command
RESTORE headeronly FROM DISK = 'd:\SQL_back\thursday.bak'
is
msdb backup NULL 1 NULL 0
rasgasprod backup NULL 1 NULL 0
mi_dbadmin backup NULL 1 NULL 0
(other columns eliminated)

filelistonly shows me only the "master" and "masterlog"
headeronly shows me all the database backup status.

what shall i presume? is my backup command right or wrong.

help will be appreciated

Thanks and Regards,
Aijaz

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

AiJaz,

After playing with this a little it looks like Restore FileListOnly returns only the files of the first database placed in the backup file unless you use WITH file=n. If you issue the command like this:

Restore FileListOnly From [file name] with File = 2 it will show the second db backed up, etc...

Restore HeaderOnly from [file name] actually lists all DB's backed up on the backup file.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Aijaz Ahmed Mir SSC Veteran Points: 239 More actions · Answer 2

hi Jack,

many many thanks to you. my doubt is clear now. infact i was in the process of writing new backup scripts. thanks once again you save my lot of time.

regards

Aijaz