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

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 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.
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 option 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 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...
>
>
|||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 option to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:

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 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.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 option 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 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.
>
>|||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:
> 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 option 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 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.
> >
> >
> >

Restore problem - It takes a long time (loading...)

Hi,
I have set a restore job for a Database using Enterprise Manager. The job
ran quite quickly but the Database now appears on the database list with
(loading) written after it and none of the objects are visible. It has been
sitting there for about n hours. The database file is only about 1,5Mb and
this is the first time is has been restored. Is this kind of time usual? Is
there something else I need to do?
I´m doing this in 2 servers, one for backup and the other one for restore
tks,
edFirst right click on the databases tab and select refresh ( SEM is bad about
not refreshing.)
If the database is still marked as loading, it means that recovery has not
yet run... If you have finished loading all of the logs etc... then to make
the database available run
restore database prod with recovery
from Query Analyzer... The database will now be available...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Edmilson" <troqui@.padtec.com.br> wrote in message
news:#xDTrT1VDHA.2012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have set a restore job for a Database using Enterprise Manager. The job
> ran quite quickly but the Database now appears on the database list with
> (loading) written after it and none of the objects are visible. It has
been
> sitting there for about n hours. The database file is only about 1,5Mb and
> this is the first time is has been restored. Is this kind of time usual?
Is
> there something else I need to do?
> I´m doing this in 2 servers, one for backup and the other one for restore
> tks,
> ed
>