Back & Restore of Multiple Databases

Hey all,

I was wondering if I could get some help on something thats got me really stuck!

At the moment we have in our product some SQL scripts that will backup & restore a database whenever required.

We've now added another database (an audit database) so we need to change those scripts to include this database.

The backup script seems fairly easy to change as ive added another 'BACKUP DATABASE' line that points to the same file.

The problem I am having is that when it comes to restore the 2 databases I get a whole load of errors, most of them complaining that the '.mdf' & 'log' files can't be overritten because they are already in use by the previous database (since I restore the 'data' database first and then the 'audit' database)

I've tried the 'MOVE' command but it doesn't seem to support the 'RESTORE' option?

Is there anyway I can get the backup to retain the '.mdf' & '.log' file locations so that the restore simply puts them back in the same place or is there another command that can help with my situation?

Thanks in advance!

It sounds like the 'data' database has references and links to the 'audit' database.

Try restoring the 'audit' database first, and then the 'data' database.

If I'm completely 'off base', let me know and we will try other tactics to help you.

|||I think you can use the REPLACE option of RESTORE to do this.|||

if I understand Properlly then try this

Go to Enterprise Manager,

select your server and right click your database,

then you see a option back up

just do backup

|||

Thanks everyone, but I sorted it myself.

The problem was that SQL didn't know which of the databases to restore so I had to use the 'PARTIAL, FILE = 1' extra commands in order to instruct SQL which database to restore and from what position.

AWE and multiple instance

I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
installed SP4 and the AWE hotfix.
I have 3 instances running on the box:
If I have 1 instance using 7GB and the other 2 are using 4GB each, does
AWE need to be enabled on the instancese which are set to 4GB?
Thanks!
They won't be able to use the 4GB unless you enable AWE for them.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traceable1" <tracykc@.gmail.com> wrote in message
news:1141139567.679350.285320@.z34g2000cwc.googlegr oups.com...
I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
installed SP4 and the AWE hotfix.
I have 3 instances running on the box:
If I have 1 instance using 7GB and the other 2 are using 4GB each, does
AWE need to be enabled on the instancese which are set to 4GB?
Thanks!
|||Unless of course they are running on Win2K3 x64. In that case, the SQL2000
instance should be able use up to 4GB without enabling AWE.
Linchi
"Tom Moreau" wrote:

> They won't be able to use the 4GB unless you enable AWE for them.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "traceable1" <tracykc@.gmail.com> wrote in message
> news:1141139567.679350.285320@.z34g2000cwc.googlegr oups.com...
> I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
> installed SP4 and the AWE hotfix.
> I have 3 instances running on the box:
> If I have 1 instance using 7GB and the other 2 are using 4GB each, does
> AWE need to be enabled on the instancese which are set to 4GB?
> Thanks!
>
|||I just assumed he was on 32-bit hardware, but you raise a good point.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:07C2D7BC-D085-4C5A-BA3D-ED599E571785@.microsoft.com...
Unless of course they are running on Win2K3 x64. In that case, the SQL2000
instance should be able use up to 4GB without enabling AWE.
Linchi
"Tom Moreau" wrote:

> They won't be able to use the 4GB unless you enable AWE for them.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "traceable1" <tracykc@.gmail.com> wrote in message
> news:1141139567.679350.285320@.z34g2000cwc.googlegr oups.com...
> I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
> installed SP4 and the AWE hotfix.
> I have 3 instances running on the box:
> If I have 1 instance using 7GB and the other 2 are using 4GB each, does
> AWE need to be enabled on the instancese which are set to 4GB?
> Thanks!
>
|||One interesting question at that point...
What if had 16GB of RAM and would like to run first instance with 10GB RAM
and another 4 instances with 1GB of RAM each. (all 32 bit).
It's clear that the first one would need AWE, but what about the other 4?
2 of them wouldn't need AWE and they could alocate RAM dynamically right (3
with /3GB in boot.ini)?
But witch two? Is that important?
Would the instances without AWE have to start first and then the rest or
something like that?
Am I thinking in the right way here?
Tom K.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uObHIyKPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I just assumed he was on 32-bit hardware, but you raise a good point.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:07C2D7BC-D085-4C5A-BA3D-ED599E571785@.microsoft.com...
> Unless of course they are running on Win2K3 x64. In that case, the SQL2000
> instance should be able use up to 4GB without enabling AWE.
> Linchi
> "Tom Moreau" wrote:
>
|||That's an odd requirement. IIRC, up to about 12GB (per instance), you'd go
with both /3GB and /PAE. However, if you have those others that want 1GB,
I'd be sure to limit them explicitly to 1GB. Since they don't use AWE, then
if they start first and then the big instance starts up, I believe the
smaller ones would give up memory (if they're not already limited) to the
one requesting the AWE memory. Once it has its AWE memory, it won't give it
up.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom" <mcseman@.hotmail.com> wrote in message
news:u40kZ9KPGHA.648@.TK2MSFTNGP14.phx.gbl...
One interesting question at that point...
What if had 16GB of RAM and would like to run first instance with 10GB RAM
and another 4 instances with 1GB of RAM each. (all 32 bit).
It's clear that the first one would need AWE, but what about the other 4?
2 of them wouldn't need AWE and they could alocate RAM dynamically right (3
with /3GB in boot.ini)?
But witch two? Is that important?
Would the instances without AWE have to start first and then the rest or
something like that?
Am I thinking in the right way here?
Tom K.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uObHIyKPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I just assumed he was on 32-bit hardware, but you raise a good point.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:07C2D7BC-D085-4C5A-BA3D-ED599E571785@.microsoft.com...
> Unless of course they are running on Win2K3 x64. In that case, the SQL2000
> instance should be able use up to 4GB without enabling AWE.
> Linchi
> "Tom Moreau" wrote:
>

Avoid multiple packages to run concurrently

Is there any way to avoid multiple packages to run concurrently on the same SSIS server ?

There is nothing built in to allow this (unless you can somehow restrict the number of instances of dtexec.exe that are allowed). You would have to build functionality into all your packages that (e.g.) places a zero-byte file onto the hard drive in a particular place when it starts and removes it when its finished. Each package should check to see if this file exists before it starts.

You could also check jaegd's suggestion here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1811745&SiteID=1

-Jamie

|||Thanks for your answer Jamie.

Averaging on Multiple Levels.

Hello,

I am extremely new at MDX and Analysis Services.

I have read through chapter 7 of the fast track to MDX.It is a wonderful book.But it talks a lot about sales / time data.I know this is the way 99% of people will use cubes.I am trying to use a cube for another purpose and am having a hard time moving concepts on how we do things.I am the IT person for a Truss Plant in Northern Wisconsin.

This business is a job shop, 99% of the production is custom built trusses. Quoting the jobs for this type of business is always a struggle I would like to provide valuable information to management on how much it costs to build the trusses.I know this is an almost impossible task as the rules change constantly, but I believe we can use analysis services to help us find patterns in the data.I am hoping it has the capability to average measures on multiple levels without having to know what levels we want beforehand.

We have been collecting production times for about a year now. We don’t know exactly what we are looking for as of yet, but we have some ideas.I am thinking we need to start with pieces completed per man hour. But I would like to see how this changes based on all kinds of different dimensions (Truss Properties).

We have a measure called run_time_per_piece (Lumber pieces in the truss) . I would like to analyze what the average is based on different combinations of dimensions / hierarchies.I have tried using the standard AverageofChildren, but this is not working as I would think it should.I was expecting it to Average all returned values at each level no matter what the level is.One issue here is I don’t know what the hierarchies are going to be ahead of time. We only have a one measure we are looking at, at any one time but we don’t know what the hierarchies could be.

I would appreciate it if anyone could point me in the right direction. Am I using the right product for what I am trying to do?If so, how would I write the MDX statement to get the cube to average on all levels correctly.

Thanks in Advance.

Leo

Hi Leo,

If you browse the "Average Sales Amount" calculated measure in the Adventure Works cube, does it behave as you want, across hierarchies and levels? It is defined as:

Create Member CurrentCube.[Measures].[Average Sales Amount]

As [Measures].[Sales Amount]

/

[Measures].[Order Count],

Format_String = "Currency" ;

This is a common pattern for computing averages which work across all hierarchies, so it may help.

Averages with multi-select

I am trying to write an MDX calculation that calculates the average policy premium across multiple policies. It works fine when selecting one carrier. However, the avg function is basically based on the count and sum functions (at least that is what I read). When you select one carrier the average is based on the following:

Policy A: 2000

Policy B: 1000

Policy C: 3000

Average: 2000

Lets say I multi-select another carrier and the second carrier has the exact same amounts for the policies

the total policy amounts would be :

Policy A: 4000

Policy B: 2000

Policy C: 6000

Average: 2000

The summation (12000) should be divided by the number of policies * the number of carriers (6).

However, I wind up getting 4000 as my average. Any thoughts?

Here is the MDX I am using:

Total Policy Premiums:

sum(existing [POLICY].[POLICY #],([POLICY].[VEHICLE #].&[1],[measures].[TOTAL POLICY PREM]))

Avg:

avg(existing [POLICY].[Policy].children,[Measures].[Total Policy Premiums])

Any insight would be greatly appreciated.

You could do this for example:

AVG ( Existing CrossJoin( [Carrier].[Carrier].members, [Policy].[Policy #].members ),

[Measures].[TOTAL POLICY PREM] )

Average can be computed in multiple ways, so you need to think through on what exactly you are computing. In the MDX above, combinations of carrier and policy that do not exist or are null will not be included in the computation.