Restoreing From Log File

if the datafile is corrupted but log file is there how do I restore the
database with latest transaction from log file in sql server
*** Sent via Developersdex http://www.codecomments.com ***Backup the log file (using NO_TRUNCATE), restore latest database backup, and
all subsequent log
backups (including this last one).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jaydip Das" <jaydip_j@.hotmail.com> wrote in message news:ezGvukroFHA.2444@.tk2msftngp13.phx.
gbl...
> if the datafile is corrupted but log file is there how do I restore the
> database with latest transaction from log file in sql server
> *** Sent via Developersdex http://www.codecomments.com ***

Restore SQL database from a network drive

Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
You must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:

> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>

Restore SQL database from a network drive

Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:

> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'
.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I u
se
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>

restore question

Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
Mangesh
I keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon
|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been applied
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C
|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish

restore question

Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
MangeshI keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been appli
ed
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish