DB Backup from 1 PC & Restore to diff. PC

Friends,

I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:\...." directory.

Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:\...." directory only, as it has no partitions.

This restore process gives me error :

Restore failed for Server 'HSVM\MICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ?

Please try to solve the same for me.... W8ing...

Whe u move database from one machine to another , u can either do it by Detach/attach or backup /resotre method. U mentioned that u have backup of MDF and LDF. How have u taken this backup. Did u detached the database first and then copied or u stoped the services and then copied. Please let us know the process u carried out to take backup of MDF and LDF

If u have taken backup of database using Backup Database statement, then u must restore the database from this backup using With MOVE Option . Read about Restore Database with MOVE Option in BOL

From BOL

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Madhu

|||
I have taken Backup from PC(1) without stopping any services, from SQL Management Studio 2005 directly, using UI.
Database files were on "E:\...." directory.

Copied that "xyz.bak" file into other PC(2), which is having only "C:\..." Drive.|||

Now u follow these steps

(a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak' :- This command will give u the logical file names in the backup set

(b) RESTORE DATABASE YourNewDatabaseName
FROM DISK = 'c:\xyz.bak'
WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from Step(a)' TO 'c:\yourfolder\YourNewDatabaseName.ldf'

Above statement will restore the database to new location.

Madhu

|||
Practically I just tried with your suggession.... and ofcourse it worked. Thank you for that.

Are you aware of VB.NET ? and if yes, then MicroSoft.SqlServer.Management.Smo Object Model ?

Bcoz I have taken backup file manually, but restoring from .NET implementation.

If you aware of same, then plz guide me to use Restore Class of SMO Object Model.|||

check this

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx

Madhu

|||
still i am not satisfied.

while restoring DB it is giving me the error :

Exception >> Restore failed for Server 'HSVM\MICROFIT'.
Inner Exception >> Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\MicroFit\HealthStar v6.0 Server Edition\Data\MICROFIT_HealthStar_Data.mdf'.

where HSVM is computer name & MICROFIT is instance on SQL server.

datetime diff query syntax

Hi.
I'm trying but not getting correct results.

I have two tables
one with app, msg, time
(varchar,datetime,varchar)

app1 start 2006-04-03 13:33:36.000
app1 stuff 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000
app1 start 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 stuff 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app3 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app3 end 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000

and another with dr watson crash info
(varchar, datetime)
app1 2006-04-03 13:33:36.000
app2 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app3 2006-04-03 13:33:36.000

I'm trying to make a query that will allow
me to see what entries in the first table
occurred wtihin, say, a minute, or maybe 40
seconds of any of the entries in the second
table.

I want all the entries in the second table to
be present, so I know it has to be some sort
of join, probably an outer join.

my syntax is giving me bad results, probably
because I'm just out of practice.

can someone tell me how to put a query together
so I see the data I'm looking for?
Thanks
Jeff

Jeff KishJeff Kish wrote:
> Hi.
> I'm trying but not getting correct results.
(snip)

There are a couple of different ways to do this. This one may not be
the best. It's just the first thing that popped into my mind. Hope it
helps. Your sample data was all the same timestamp. I created sample
data where a crash occurs within one minute of an entry for app1 and
another crash within a minute of an entry for app3. App2 is output in
the results becuase you specifically requested that.

Christopher Secord

create table AppMessage (
App char(4),
MsgType char(5),
MsgDate datetime
)
create table DRWatsonCrash (
App char(4),
CrashDate datetime
)

insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')

insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')

select AppMessage.App as Application, MsgType, MsgDate
from AppMessage, DrWatsonCrash
where AppMessage.App = DRWatsonCrash.App
and CrashDate between dateadd(minute,-1,MsgDate) and
dateadd(minute,1,MsgDate)
union all
select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
from DRWatsonCrash
order by Application, MsgDate|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have two tables
> one with app, msg, time
> (varchar,datetime,varchar)
> app1 start 2006-04-03 13:33:36.000
> app1 stuff 2006-04-03 13:33:36.000
> app1 end 2006-04-03 13:33:36.000
> and another with dr watson crash info
> (varchar, datetime)
> app1 2006-04-03 13:33:36.000
> app2 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app3 2006-04-03 13:33:36.000
>
> I'm trying to make a query that will allow
> me to see what entries in the first table
> occurred wtihin, say, a minute, or maybe 40
> seconds of any of the entries in the second
> table.
> I want all the entries in the second table to
> be present, so I know it has to be some sort
> of join, probably an outer join.

There is a standard recommendation for this sort of posts, and that is
that you post:

o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it very easy to copy and paste into a query tool to develop a
tested solution.

With the information you have given, I can only give a non-tested solution,
which is also is just a guess of what you are looking for.

SELECT w.app1, w.datetimecol, o.event, o.datetimecol
FROM drwatson w
LEFT JOIN othertable o
ON w.app = o.app
AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 5 Apr 2006 21:42:37 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have two tables
<snip>
>There is a standard recommendation for this sort of posts, and that is
>that you post:
>o CREATE TABLE statments for your tables.
>o INSERT statements with sample data.
>o The desired output given the sample.
>This makes it very easy to copy and paste into a query tool to develop a
>tested solution.
I understand. I'll remember this in the future.
>With the information you have given, I can only give a non-tested solution,
>which is also is just a guess of what you are looking for.
> SELECT w.app1, w.datetimecol, o.event, o.datetimecol
> FROM drwatson w
> LEFT JOIN othertable o
> ON w.app = o.app
> AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40

and also the other message reply said...

>There are a couple of different ways to do this. This one may not be
>the best. It's just the first thing that popped into my mind. Hope it
>helps. Your sample data was all the same timestamp. I created sample
Yes, I was in a hurry and was careless. Normally the data is very
much just as you thought below.
>data where a crash occurs within one minute of an entry for app1 and
>another crash within a minute of an entry for app3. App2 is output in
>the results becuase you specifically requested that.
>Christopher Secord
>create table AppMessage (
>App char(4),
>MsgType char(5),
>MsgDate datetime
>)
>create table DRWatsonCrash (
>App char(4),
>CrashDate datetime
>)
>insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
>insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
>insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
>insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
>insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
>insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
>insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
>insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')
>insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
>insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
>insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')
>
>select AppMessage.App as Application, MsgType, MsgDate
>from AppMessage, DrWatsonCrash
>where AppMessage.App = DRWatsonCrash.App
>and CrashDate between dateadd(minute,-1,MsgDate) and
>dateadd(minute,1,MsgDate)
>union all
>select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
>from DRWatsonCrash
>order by Application, MsgDate

Thanks much. I'll try both solutions.
I appreciate the feedback.
Jeff