Restore script restoring to old version of the database

A month ago I set up a small program to backup my databases – it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run – so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in
a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups – I appear to have lost a mon
ths
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) – so the backups were happening. What am I do
ing
wrong here? Note that the recovery model was set to FULL.
Please help.Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also – I can’t use Enterprise Manager because it doesn’t have the opti
on to
specify a password – so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE
.
> If you do the restore though enterprise manager, it will the backups too a
nd
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>
>|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
[vbcol=seagreen]
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also – I can’t use Enterprise Manager because it doesn’t have the op
tion to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
>