Problem with restoring a database

Hello!
I use SQL Server 2005. I have a backup taken from a computer that had the
following definition when I started SQL Server
- Server type: Database Engine
- Server name: UHTDEMO5
-Authentication: Windows Authentication
-Username: UHTDEMO5\anvd
The backup file is called backup_2008_01_22.bak.
So I was sitting on server name UHTDEMO5 when the backup on database Utcas
was taken.
I was loggin in as user anvd. The backup was successfully created.
Now to my question I want to restore this backup on my local desktop server
that is called UHT-DEMO1.
I log in locally as the administrator.
I have tried several different things.
1. I have first create a new user database with name Utcas. This works fine.
2. Now I have an empy database with name Utcas.
3. Now I try Utcas->Tasks->Restore->Database.
4. In the field To database I select Utcas
5.In the From device I select and pick my backup set which is called
backup_2008_01_22.bak
6. In the field From device I can now see my backup file.
7. I the field Select the backup sets to restore I check the check box.
8. When I now klick the Ok button I get the following error message
"Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The media set has 2 media families but only
1 is provided. All
members must be provided. (Microsoft.SqlServer.Smo)"
What do I have to do to restore the backup on my server called UHT-DEMO1.
I thought it was easy to restore a backup but it seams to be difficult.
I'm not so used to SQL Server because I have worked with Oracle before.
//TonyIt appears that your original backup was done as a striped backup - i.e.
there was more than one file created at the time of the backup. If so, then
you need to specify all of the files in the backup when you do the restore.
Check out:
http://msdn2.microsoft.com/en-us/library/ms178062.aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
Hello!
I use SQL Server 2005. I have a backup taken from a computer that had the
following definition when I started SQL Server
- Server type: Database Engine
- Server name: UHTDEMO5
-Authentication: Windows Authentication
-Username: UHTDEMO5\anvd
The backup file is called backup_2008_01_22.bak.
So I was sitting on server name UHTDEMO5 when the backup on database Utcas
was taken.
I was loggin in as user anvd. The backup was successfully created.
Now to my question I want to restore this backup on my local desktop server
that is called UHT-DEMO1.
I log in locally as the administrator.
I have tried several different things.
1. I have first create a new user database with name Utcas. This works fine.
2. Now I have an empy database with name Utcas.
3. Now I try Utcas->Tasks->Restore->Database.
4. In the field To database I select Utcas
5.In the From device I select and pick my backup set which is called
backup_2008_01_22.bak
6. In the field From device I can now see my backup file.
7. I the field Select the backup sets to restore I check the check box.
8. When I now klick the Ok button I get the following error message
"Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The media set has 2 media families but only
1 is provided. All
members must be provided. (Microsoft.SqlServer.Smo)"
What do I have to do to restore the backup on my server called UHT-DEMO1.
I thought it was easy to restore a backup but it seams to be difficult.
I'm not so used to SQL Server because I have worked with Oracle before.
//Tony|||"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> Hello!
>
> 1. I have first create a new user database with name Utcas. This works
> fine.
> 2. Now I have an empy database with name Utcas.
Just so you know, the above is not necessary.
> 3. Now I try Utcas->Tasks->Restore->Database.
> 4. In the field To database I select Utcas
> 5.In the From device I select and pick my backup set which is called
> backup_2008_01_22.bak
> 6. In the field From device I can now see my backup file.
> 7. I the field Select the backup sets to restore I check the check box.
> 8. When I now klick the Ok button I get the following error message
> "Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
> Additional information:
> System.Data.SqlClient.SqlError: The media set has 2 media families but
> only
> 1 is provided. All
> members must be provided. (Microsoft.SqlServer.Smo)"
Yeah, as Tony said, it was a striped backup (it wrote to two files at once,
in some cases this can double backup and restore speeds.)
Look for somethign like UTCAS_db_20080121-1.bak and UTCAS_db_2008021-2.back
(i.e. most likely the files will include a -1 and -2... but honestly, could
be anything).
You can also check the originating server to see what it specified (if it's
an automatic job, it should list it, otherwise, I think it's
sys.backuphistory or sys.backupmediaset or something like that.)
> What do I have to do to restore the backup on my server called UHT-DEMO1.
> I thought it was easy to restore a backup but it seams to be difficult.
> I'm not so used to SQL Server because I have worked with Oracle before.
> //Tony
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||In addition to the other posts, you might find below useful:
RESTORE LABELONLY FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I use SQL Server 2005. I have a backup taken from a computer that had the
> following definition when I started SQL Server
> - Server type: Database Engine
> - Server name: UHTDEMO5
> -Authentication: Windows Authentication
> -Username: UHTDEMO5\anvd
> The backup file is called backup_2008_01_22.bak.
> So I was sitting on server name UHTDEMO5 when the backup on database Utcas
> was taken.
> I was loggin in as user anvd. The backup was successfully created.
> Now to my question I want to restore this backup on my local desktop server
> that is called UHT-DEMO1.
> I log in locally as the administrator.
> I have tried several different things.
> 1. I have first create a new user database with name Utcas. This works fine.
> 2. Now I have an empy database with name Utcas.
> 3. Now I try Utcas->Tasks->Restore->Database.
> 4. In the field To database I select Utcas
> 5.In the From device I select and pick my backup set which is called
> backup_2008_01_22.bak
> 6. In the field From device I can now see my backup file.
> 7. I the field Select the backup sets to restore I check the check box.
> 8. When I now klick the Ok button I get the following error message
> "Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
> Additional information:
> System.Data.SqlClient.SqlError: The media set has 2 media families but only
> 1 is provided. All
> members must be provided. (Microsoft.SqlServer.Smo)"
> What do I have to do to restore the backup on my server called UHT-DEMO1.
> I thought it was easy to restore a backup but it seams to be difficult.
> I'm not so used to SQL Server because I have worked with Oracle before.
> //Tony
>

Problem with remove files older then in backup

I am using MSDE 2000 sp3 on Windows 2003 Server. I am connecting to this
MSDE from another computer using Enterprise manager. I have created a
maintenance plan that backup the DB once a day. The problem is that in the
option to "remove files older then" - the combo box where the day/week/month
should be is empty so I can not set this option.
How can I fix it ?
Thanks for your time
ra294@.hotmail.comIn the end, a maintenance plan just generates a parameter list for
xp_sqlmaint. You can modify this yourself in the job that is created. For
details on the options have a look in BOL for "sqlmaint utility" e.g. the
switch for deleting backups is
-DelBkUps <time_period> where
<time_period> ::= number[minutes | hours | days | weeks | months]
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ra294" <ra294@.hotmail.com> wrote in message
news:%23CIRutpBEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I am using MSDE 2000 sp3 on Windows 2003 Server. I am connecting to this
> MSDE from another computer using Enterprise manager. I have created a
> maintenance plan that backup the DB once a day. The problem is that in the
> option to "remove files older then" - the combo box where the
day/week/month
> should be is empty so I can not set this option.
> How can I fix it ?
> Thanks for your time
> ra294@.hotmail.com
>
>|||Thanks for the quick response.
Where is this script located ? What's the name of the file ?
ra294@.hotmail.com
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23c9MECrBEHA.1140@.TK2MSFTNGP10.phx.gbl...
> In the end, a maintenance plan just generates a parameter list for
> xp_sqlmaint. You can modify this yourself in the job that is created. For
> details on the options have a look in BOL for "sqlmaint utility" e.g. the
> switch for deleting backups is
> -DelBkUps <time_period> where
> <time_period> ::= number[minutes | hours | days | weeks | months]
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "ra294" <ra294@.hotmail.com> wrote in message
> news:%23CIRutpBEHA.3524@.TK2MSFTNGP10.phx.gbl...
the
> day/week/month
>|||Jobs can be found in the Management>SQL Server Agent>Jobs section in
Enterprise Manager. You should see a job that references your maintenance
plan name. If not you can simply create a job yourself that calls
xp_sqlmaint directly and supply the switches as detailed in BOL
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ra294" <ra294@.hotmail.com> wrote in message
news:%23%23K2E3rBEHA.140@.TK2MSFTNGP09.phx.gbl...
> Thanks for the quick response.
> Where is this script located ? What's the name of the file ?
> ra294@.hotmail.com
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23c9MECrBEHA.1140@.TK2MSFTNGP10.phx.gbl...
For
the
this
> the
>

Problem with remote index server (no result)

Hello,
from SQL Server 2005 I access a index server an another computer (cp-
tkl)
select * from openquery(IDXNAME, 'select filename from "cp-
tkl".idxc..scope()')
When I use windows authentification it works and I have a result. But
when I use SQL Server I have no result (and no error message). I try
some user mapping from the SQL User to the domain user (a user with
that works when I use windows authentication) in the security window
for the linked server, but I found no solution.
It look likes a simple authentification problem. What can I do?
Joachim
Give the everyone group full access to this registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\ContentIndex
"Joachim" <joachim.dehm@.gmx.de> wrote in message
news:1179465460.587412.54360@.n59g2000hsh.googlegro ups.com...
> Hello,
> from SQL Server 2005 I access a index server an another computer (cp-
> tkl)
> select * from openquery(IDXNAME, 'select filename from "cp-
> tkl".idxc..scope()')
> When I use windows authentification it works and I have a result. But
> when I use SQL Server I have no result (and no error message). I try
> some user mapping from the SQL User to the domain user (a user with
> that works when I use windows authentication) in the security window
> for the linked server, but I found no solution.
> It look likes a simple authentification problem. What can I do?
> Joachim
>

Problem with querry performance locally vs. network

Hi,

I work on speed-up our company SQL Server 2000. I reconstructed one querry and test it on client computer (other than server). There was no profit. I was surprised :(. I supposed several times speed-up. Next I run old and new querry directly on our SQL Server machine. The result was proper - new (after modifications) querry takes 2,5 times less time than previous.

Ok, question: why there wasn't profit of modification from client computer?

I thougth that reason is slow network. So I copy from client computer file of weight 80MB to server. This takes 10seconds. Result of my querry is 35MB and it takes 17 seconds on client computer and 6 seconds directly from server.

Best regards,
Walter Luszczyk

If the result of your query is 35MB in size then it's not particularly surprising that it takes 6 seconds for the client computer to get the results. Likely the reason it's taking longer when you copy the file to the client is that the client computer does not perform as well as the server. After your tuning, the server processed the query faster but it still had to transfer 35MB of data over the LAN.

Consider your 80MB data file. How long did it take for you do copy it from the server to the client?

With this in mind, you should consider whether you need 35MB worth of results. This is 43% of your database.

Can you work with less columns?|||

Unfortunately I cannot select smaller result - it's report ... :(

I try bit this problem from another side and my observations are:

I use SQL Profiler to solve the problem. Sending old querry yields performance: CPU:8641, Reads: 10688, Writes: 0, Duration: 12513. But new querry yields: CPU: 627, Reads: 10627, Writes: 0, Duration: 11956.

So why duration doesn't change (12513 : 11956) while CPU changes a lot (8641 : 627)?

This time result's size is only 7MB - it's really small in out network.

Regards,

Walter

|||

Duration in Profiler is not necessarily a measure of time to execute the query. It actually a measure of the time it takes to execute the query, get the result to the client, and the client to close the recordset.

For example, if you're transferring megabytes of data and your client is on a gigabit ethernet connection, your duration will be lower than if your client is on a 10 megabit ethernet connection. If you can set up a VPN connection between your client and server (connect from home) then you would see an even longer duration.

I've actually seen Microsoft Access tables opened on SQL Server that showed durations of 20-30 minutes but showed very low reads and CPU.

In your case, you need to either reduce the size of the data being returned or get a higher bandwidth connection to your server.

Some ideas:

Reduce the size of the data types. For example: Are you using nvarchar where you could use VarChar? Can you convert the output of your DateTime columns to SmallDateType?