I drop and recreate a database snapshot for reporting purposes at the end of a DW loading process.
I need to create some indexes to improve query performance.
Where I should create the index, on the originale db or on the snapshot db ?
Cosimo
If I remember from an article, you can not do this on a snapshot database. So I guess you should do this on the source DB. Please check it though.|||SOLVED
I create the index on the source db.
In the paste I have used SQL2000 to create db from the Query Analyser. In SQL2005 I can't see Query Analzyer but found under programs --> Microsoft SQL Server 2005 --> Configuration Tools --> Reporting Services Configuration --> Database Setup. It creates a database and I can connect to it from VS.NET. Here is my dilema, I was provided SQL scripts to create tables, stored procs etc. Where and how do I run these scripts to create these things in my newly create db.
Also how do I remove a db in SQL2005?
You can run your scripts from the command line via sqlcmd.exe or what I suggest you really do is download SQL Server Management Studio - Express Edition, that is a lightweight replacement for QA and EM, but for Express|||Excellent suggestion... SQL Server Management Studio was exactly what I was looking for.
Thanks!
This is what the query text looks like:
restore database model from
Disk='c:\JQJ\mydump\model.bak'
with replace
I get this error:
The backup of the system database on device c:\JQJ\mydump\model.bak cannot be restored because it was created by a
different version of the server
(134217922) than this server (134218488).
The other server product version is: 8.00.194(RTM)
The server on my machine is 8.00.760(SP3)
1.How can I make this restore work? Seems to me I have the later version, so I should be able to handle it. Mine is a
10-user, and the other is enterprise.
2.Also I need to be able to force a different path and even database name: The source server where I create the backup file
may have a different location than I have on my machine, so what parameters can I give the command so that it will use the
location I specifiy? The backup file will have the location information relative to the source server, which I can not
always use. This means I will need to programatically extract that location information on my machine. (I can't hard code
it, since other people with different installation setups will use my program on their machines).
3.The database name on the source server may be ABC, but I may want to restore over XYZ. How can I force that? Perhaps it
would be the same parameter I need for 2.
Thanks,
JonJon Jacobs (JonJacobsAtcomcast.net) writes:
> I attempt to back up a database on one server and restore it on my local
> machine.
> This is what the query text looks like:
> restore database model from
> Disk='c:\JQJ\mydump\model.bak'
> with replace
> I get this error:
> The backup of the system database on device c:\JQJ\mydump\model.bak
> cannot be restored because it was created by a different version of the
> server (134217922) than this server (134218488).
> The other server product version is: 8.00.194(RTM)
> The server on my machine is 8.00.760(SP3)
> 1.How can I make this restore work? Seems to me I have the later
> version, so I should be able to handle it. Mine is a 10-user, and the
> other is enterprise.
I would expect that it is possible to restore a backup from SQL 2000
RTM on SQL 2000 SP3, but it is very clear that it is not possible in
this case. But then again, you are not restoring any database - you
are restoring model. That's a system database, so I assume it is special.
Question: are you restoring model, because you really need a copy of model
from the other box on your machine? Or did you just pick model as a test
case? In the former case, the easiest may be to script any user objects
you have in model, and run the script. I would not really expect that
you have any data in model. If you are just testing, try Northwind or
pubs instead.
> 2.Also I need to be able to force a different path and even database
> name: The source server where I create the backup file may have a
> different location than I have on my machine, so what parameters can I
> give the command so that it will use the location I specifiy?
You use MOVE:
RESTORE DATABASE mydatase FROM disk = 'C:\temp\mydump.bak'
WITH MOVE 'mydatabase_data' TO 'e:\mylocation\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'f:\myloglocation\mydatabase.ldf',
REPLACE
It does not seem that you can use variable in place of the device
and paths, but you could build the BACKUP command dynamically and
execute with EXEC(@.sql).
> 3.The database name on the source server may be ABC, but I may want to
> restore over XYZ. How can I force that? Perhaps it would be the same
> parameter I need for 2.
RESTORE DATABASE XYZ ...
There is no law that says that the database you restore to must have
the same name as the source database.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Question: are you restoring model, because you really need a copy of model
>from the other box on your machine? Or did you just pick model as a test
>case? In the former case, the easiest may be to script any user objects
>you have in model, and run the script. I would not really expect that
>you have any data in model. If you are just testing, try Northwind or
>pubs instead.
Yes, Model was just for testing. I will try another database.
>You use MOVE:
> RESTORE DATABASE mydatase FROM disk = 'C:\temp\mydump.bak'
> WITH MOVE 'mydatabase_data' TO 'e:\mylocation\mydatabase.mdf',
> MOVE 'mydatabase_log' TO 'f:\myloglocation\mydatabase.ldf',
> REPLACE
Excellent. I will put that to use.
>It does not seem that you can use variable in place of the device
>and paths, but you could build the BACKUP command dynamically and
>execute with EXEC(@.sql).
I will need to build the command dynamically as you say. But first I need to extract the destination path. How?
>RESTORE DATABASE XYZ ...
>There is no law that says that the database you restore to must have
>the same name as the source database.
I've been trying that to no avail. Well, maybe it will work after I get the other issues resolved, but so far, the error
messages complain about the original database name.
Thank you very, very much.
Jon|||Jon Jacobs (JonJacobsAtcomcast.net) writes:
> I will need to build the command dynamically as you say. But first I
> need to extract the destination path. How?
Well, I silently passed over that question, since I can't really tell
from a distance where you want to have your databases.
But assuming that you want the database in the default location of the
server.
This information is stored in the registry of the server, and you can
retrieve it with xp_regread. However, this is a undocumented and
unsupported function. (Note also that with SQL 2000 SP4, the function
will be restricted to access to SQL Server own values.)
A somewhat more roundabout way of doing it, is this:
CREATE DATABASE temp
SELECT @.path = filename FROM sysdatabases WHERE name = 'temp'
SELECT @.path = replace(@.path, '//', '/')
SELECT @.path = substring(@.path, 1, len(@.path) - len('temp.mdf'))
DROP DATABASE temp
>>RESTORE DATABASE XYZ ...
>>
>>There is no law that says that the database you restore to must have
>>the same name as the source database.
> I've been trying that to no avail. Well, maybe it will work after I get
> the other issues resolved, but so far, the error messages complain about
> the original database name.
You to use WITH REPLACE in this case. Since you already had this in
your example, I did not mention this.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I execute the following query in the Access application,
SELECT customerName From customers Where stateCode = 'CA'
My question:
Who (Access, SQL Server or the ODBC provider) selects based on the state code?
Does SQL Server only return customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let Access select the customerNames where stateCode = 'CA'
OR
Does SQL Server return ALL rows and let the ODBC provider select and return only the appropriate rows to Access?
Thanks for your helpThe cleanest way to determine this is to run the query while SQL Profiler is running, and see what gets passed to SQL Server. My guess is that Access only asks for the rows where stateCode is CA.|||Agree with D.R. However, from what I remember it depends on the ODBC driver. If the remote server (in this case SQL) implements a specific part of the ODBC capabiliies then it will do the query on the server, otherwise the client has to do it. I'm pretty sure SQL will implement that capability.
I have a database table. The table has number of fields. Out of those fields one is Company and
another is DateTime. The table has thousands of records. I want to get the most recent
record for each company. In order to do that I am using the following query
SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE Company=X.Company)
ORDER BY Company
Note: There is only one record exists for the given company on a given date
The problem is that this query is very slow. Am I doing something wrong or there could be another
alternative way to improve it ?
Thanks in advance
KDV
David Sceppa
Microsoft
I've got a column in one table called PRODID, and each value in this column references 1 or more values, say OrderDates, and I was wondering whether it's possible to write a query that returns all the values in the PRODID along with the latest OrderDate of the relevant PRODID value into the following result set:
PRODID OrderDate
1 "Latest Date"
2 "Latest Date"
And so on. Latest Date refers to the DateTime shown under OrderDates.
All help appreciatedSELECT P.ProductID, MAX(O.OrderDate)
FROM Orders O
INNER JOIN Products P ON O.ProductID = P.ProductID
GROUP BY P.ProductID|||Thanks for that, but I was just wondering is there any way to modify that query so that if any values for ProdID do not have an orderDate, the result set also returns these ProdIds but with a null value for the OrderDate?|||Actually ignore my last post, I managed to modify it to suit my eact needs, thanks for showing me the code though
i have a table which has a list of features and if they are selected i store the value yes, otherwise no . i want to be able to display a list of the features from the tables which have the value yes ! is this possible?yes xcept you dont need quotes around the column name..
SELECT * FROM FEATURES WHERE VALUE = 'YES'
hth|||Cheers but I worded the problem badly, VALUE isnt the name of the column, their are a number of different columns, each with different names, and i only want to display the column name if the value is yes ! any idea's?|||you wound then need to use CASE statement...check BOL for xact syntax but its something like
...
CASE
WHEN
colname='yes' then colname
ELSE NULL
hth|||what is BOL?|||BOL = Books Online
It is SQL Server's documentation.
