[ms sql 2000] Backup problem

[mar_zim]

Hi guys good day. I have this problem in backuping a database. I use SP to this task. Here's the code.

backup database sample to disk = 'c:\program files\backup.bak'

Backup thingy was successful the problem I don't understand was that when I restore the database using the backup.bak file, it seems it will not update the database the data that populated in database were the data when I first backup the database and not the latest one. What could be the problem here?

Thanks in advance guys.

[mendhak]

How are you restoring? Is it a complete restore?

[szlamany]

If you backup several times into the same .BAK file you will have several backups within that .BAK file. RESTORING will restore the first one.

This is from an old post I made:

BACKUP DATABASE Acctfiles 
   TO DISK = 'c:\ACS DESKTOP\Acctfiles_D18.bak'
-- this does a backup

RESTORE FILELISTONLY 
   FROM DISK = 'c:\ACS DESKTOP\Acctfiles_D18.bak'
-- this displays the "files" in the backup

RESTORE DATABASE Acctfiles_D18
   FROM DISK = 'c:\ACS DESKTOP\Acctfiles_D18.bak'
   WITH MOVE 'Acctfiles_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\Acctfiles_D18.mdf',
   MOVE 'Acctfiles_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\Acctfiles_D18.ldf'
-- this actually does a restore - you can restore to a different DB
-- name and specify the data and log locations
GO

Note that the second query - the FILELISTONLY - will display the contents of a .BAK file...

[mar_zim]

Thanks.
What if I restore the file using the enterprise manager and not using SP in restoring it? Right click on the database->all tast->restore. What option should I use in order to populate the latest data in the database. As of now I can only populate the first data when I do the first back up.

[szlamany]

I just did a test...

In query analyzer I did a BACKUP...to...'C:\Test.Bak'

I did that command twice - putting two backups in TEST.BAK.

The first one reported in the MESSAGES pane that it processed into file 1.

The second one reported in the MESSAGES pane that it processed into file 2.

I did the RESTORE FILELISTONLY FROM DISK='C:\Test.Bak' with File=1

and also with FILE=2. I can see both backup sets in the .BAK file.

If you leave off the WITH FILE= it defaults to the first backup set in the backup file.

Now when I go into ENTERPRISE MANAGER and do all tasks->restore I notice that it really wants to RESTORE a backup made with the BACKUP in ENTERPRISE MANAGER. Those backups are made to a unique filename...

But I am able to change the RESTORE to come "from device (radio button)" and then "SELECT DEVICES...(command button)" and browse out to the .BAK file that I made.

Then back to the GENERAL tab I see that it's got a BACKUP NUMBER of 1 appearing in a text box - I can change that to 2 if I want. I can also click the "VIEW CONTENTS...(command button)" and see the two backup sets in this backup file. It's got a check box next to either file - I can select either 1 or 2 to restore from...

So it appears that EM will allow processing of a .BAK file with more than one backup set in it. Remember that all that EM is doing is running the same "t-sql scripts" that I showed you from that GUI - it's just hiding the syntax from you - but apparently all the same capability is there.