MS SQL Server New Users - Trying to restore a database

Author

Trying to restore a database

isabelle

2005-11-23, 7:24 am

Hi,

A user deleted some data and I'm trying to restore the database up to a
certain point in time. My backups are going to a network drive and I see the
backup and log files that I want to use for my restore. Before I started the
restore, I issued a manual complete backup of the database to a local drive
on the server in case anything went wrong with the restore. Here are the
steps I took:
1) detached databaseA and re-attached with name databaseA_old
2) created a new database with name databaseA
3) used Enterprise Manager - all tasks - restore and it only showed the last
backup that I did to the local drive. It did not show any of the previous
backups to the network drive. I navigated to the network drive and see all
the backups, but they do not show up in the restore window. Why couldn't I
see all the previous backups?

So, I aborted the restore and changed the name back from databaseA_old to
databaseA (using detach and attach). Also, after all this, when I looked at
my maintenance plan, databaseA was no longer checked to be backed up. Is
this related?

Thanks in advance.
Bea

Andrea Montanari

2005-11-23, 7:24 am

hi Bea,
isabelle wrote:
> Hi,
>
> A user deleted some data and I'm trying to restore the database up to
> a certain point in time. My backups are going to a network drive and
> I see the backup and log files that I want to use for my restore.
> Before I started the restore, I issued a manual complete backup of
> the database to a local drive on the server in case anything went
> wrong with the restore. Here are the steps I took:
> 1) detached databaseA and re-attached with name databaseA_old
> 2) created a new database with name databaseA
> 3) used Enterprise Manager - all tasks - restore and it only showed
> the last backup that I did to the local drive. It did not show any
> of the previous backups to the network drive. I navigated to the
> network drive and see all the backups, but they do not show up in the
> restore window. Why couldn't I see all the previous backups?
>
> So, I aborted the restore and changed the name back from
> databaseA_old to databaseA (using detach and attach). Also, after
> all this, when I looked at my maintenance plan, databaseA was no
> longer checked to be backed up. Is this related?

backup history is saved in msdb database (and can be cleaned via
sp_delete_backuphist
ory msdb stored procedure,
http://msdn.microsoft.com/library/d..._da-di_1xih.asp),
but it's not that vital to have that information, as you can specify the
backup set you want to be used at restore time...
in the restore window, select "restore from device", press <select device>,
add the file name in UNC form like \\remote_computer\sh
are\pubs.bak and then
you can see it's contents (providing the Windows account running SQL Server
Agent has enougth permission accessing the remote share, but this should not
be a problem as you already said you regularly backup to that share) and
specify all your restore options..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

isabelle

2005-11-23, 11:23 am

Hi Andrea,

Thank you for the reply. I come from an Oracle/Unix world, so I am trying
to understand how the SQL restore works. I did as you suggested and
navigated to the path and saw the backup that I wanted. But what about all
the logs that I need to apply after that? Why can't I just put in the dir
path and it show all the backups instead of the full path to that one backup?
As I understand it, I could restore the database with the one full backup
file and leave the database non operational to restore additional logs.
Would I then need to start the restore again, type in the full path to the
log file and run the restore again? Is there anyway that I can just run the
restore once and include the full backup and all the logs that I need? I did
this okay on my test box, but it was showing me all the backups okay. How
do I get around that when nothing shows in the drop down list and I have to
manually navigate to the path?

Thank you!
Bea

"Andrea Montanari" wrote:

> hi Bea,
> isabelle wrote:
>
> backup history is saved in msdb database (and can be cleaned via
> sp_delete_backuphist
ory msdb stored procedure,
> http://msdn.microsoft.com/library/d..._da-di_1xih.asp),
> but it's not that vital to have that information, as you can specify the
> backup set you want to be used at restore time...
> in the restore window, select "restore from device", press <select device>,
> add the file name in UNC form like \\remote_computer\sh
are\pubs.bak and then
> you can see it's contents (providing the Windows account running SQL Server
> Agent has enougth permission accessing the remote share, but this should not
> be a problem as you already said you regularly backup to that share) and
> specify all your restore options..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>
>
>

Mike Hodgson

2005-11-24, 3:23 am

I think it's always easier to explain SQL solutions in T-SQL (rather
than describing GUI tools) as it's black and white (plus it gives you a
better understanding of what goes on under the GUI covers, so to speak).

To restore a database to a point in time using a DB back & subsequent
transaction log backups you use the RESTORE
<http://msdn.microsoft.com/library/e..._ra-rz_25rm.asp>
statement. For example, to restore MyDB to the state it was in at
8:43:52am on November 24, 2005, where the backup files are stored in
" \\SERVER1\MyShare\My
BackupDir\" you'd run this series of RESTORE
statements:

restore database MyDB from
disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
.bak' with norecovery
restore log MyDB from
disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup1.trn' with norecovery
restore log MyDB from
disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup2.trn' with norecovery
restore log MyDB from
disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup3.trn' with norecovery
restore log MyDB from
disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup4.trn' with norecovery
restore log MyDB from
disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup5.trn' with
recovery, stopat = '20051124 8:43:52.000'

Hope this makes things clearer for you.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

isabelle wrote:
[color=darkred]
>Hi Andrea,
>
>Thank you for the reply. I come from an Oracle/Unix world, so I am trying
>to understand how the SQL restore works. I did as you suggested and
>navigated to the path and saw the backup that I wanted. But what about all
>the logs that I need to apply after that? Why can't I just put in the dir
>path and it show all the backups instead of the full path to that one backup?
> As I understand it, I could restore the database with the one full backup
>file and leave the database non operational to restore additional logs.
>Would I then need to start the restore again, type in the full path to the
>log file and run the restore again? Is there anyway that I can just run the
>restore once and include the full backup and all the logs that I need? I did
>this okay on my test box, but it was showing me all the backups okay. How
>do I get around that when nothing shows in the drop down list and I have to
>manually navigate to the path?
>
>Thank you!
>Bea
>
>"Andrea Montanari" wrote:
>
>
>

Mike Hodgson

2005-11-24, 3:23 am

Sorry, I forgot to say that you run this batch of T-SQL statements in
Query Analyzer (or whatever your favourite SQL client tool is - QA is
almost certainly the most popular SQL client tool for MSSQL).

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

Mike Hodgson wrote:
[color=darkred]
> I think it's always easier to explain SQL solutions in T-SQL (rather
> than describing GUI tools) as it's black and white (plus it gives you
> a better understanding of what goes on under the GUI covers, so to speak).
>
> To restore a database to a point in time using a DB back & subsequent
> transaction log backups you use the RESTORE
> <http://msdn.microsoft.com/library/e..._ra-rz_25rm.asp>
> statement. For example, to restore MyDB to the state it was in at
> 8:43:52am on November 24, 2005, where the backup files are stored in
> " \\SERVER1\MyShare\My
BackupDir\" you'd run this series of RESTORE
> statements:
>
> restore database MyDB from
> disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
.bak' with
> norecovery
> restore log MyDB from
> disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup1.trn' with
> norecovery
> restore log MyDB from
> disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup2.trn' with
> norecovery
> restore log MyDB from
> disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup3.trn' with
> norecovery
> restore log MyDB from
> disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup4.trn' with
> norecovery
> restore log MyDB from
> disk='\\SERVER1\MySh
are\MyBackupDir\MyDB
_LogBackup5.trn' with
> recovery, stopat = '20051124 8:43:52.000'
>
> Hope this makes things clearer for you.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> isabelle wrote:
>

Andrea Montanari

2005-11-24, 7:23 am

hi Bea,
isabelle wrote:
> Hi Andrea,
>
> Thank you for the reply. I come from an Oracle/Unix world, so I am
> trying to understand how the SQL restore works. I did as you
> suggested and navigated to the path and saw the backup that I wanted.
> But what about all the logs that I need to apply after that? Why
> can't I just put in the dir path and it show all the backups instead
> of the full path to that one backup? As I understand it, I could
> restore the database with the one full backup file and leave the
> database non operational to restore additional logs. Would I then
> need to start the restore again, type in the full path to the log
> file and run the restore again? Is there anyway that I can just run
> the restore once and include the full backup and all the logs that I
> need? I did this okay on my test box, but it was showing me all the
> backups okay. How do I get around that when nothing shows in the
> drop down list and I have to manually navigate to the path?

Mike suggestions is indeed ok.. UI tools are nice but can not beat a set of
Transact-SQL statements... and his example is very clear...
else, you have to manually restore via UI the very last full backup and add
all transaction log "restore", one at a time...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

isabelle

2005-11-28, 11:24 am

Thanks for all the replies. I agree with using T-SQL rather than the GUI.
Most of my Oracle maintenance was done using SQL instead of the Oracle
Enterprise Manager GUI tool and as you both stated, this is a perfect example
why. I actually ran through a test restore using the T-SQL statements that I
got from BOL and it worked perfectly. I just wanted to make sure I was
understanding the GUI tool correctly and be sure I wasn't doing something
wrong.

Thanks!
Bea

"Andrea Montanari" wrote:

> hi Bea,
> isabelle wrote:
>
> Mike suggestions is indeed ok.. UI tools are nice but can not beat a set of
> Transact-SQL statements... and his example is very clear...
> else, you have to manually restore via UI the very last full backup and add
> all transaction log "restore", one at a time...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>
>
>

Sponsored Links