Backup Error:Set property Devices to accomplish this action.

I am getting an error, "Set property Devices to accomplish this action." when doing a backup with SQL Server 2005 Express when I try to do a full (or Differential) backup through the SQL Server Management Studio. I'm using the backup defaults in the GUI and was able to back up the database a couple of days ago without any problem. Today (second time) it just comes up with this error and I have been able to find anything that talks about what it might be.

I tired to look at the script by using the "Script action to new query, file, clipboard, etc." but it fails before it creates the script in any destination. I've rebooted the server and that didn't help.

I found one similar post on this forum but it had no resolution. Any direction on this problem would be greatly appreciated.

Thank you,

Steve

Some components cannot be installed on an alternative location and have to be installed in the %programfiles% directory. If this one is located on the system drive you will not be able to install it on an alternative drive.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I did specify the data directory to be in a location other than the program's default but all the code is in the program directory. I will uninstall and reinstall everything in it's default location if that is the problem.

Steve

|||

What do you mean by "all the code" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

In the SQL setup you are given the option to store the SQL application ("The Code") in one location and the Data in another. I let the application install in the default location in the program directory and changed the data to be stored in a non-standard directory (ie. c:\data\mssql).

sr

|||

That should work for you.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I uninstalled everything that had to do with SQL Server 2005 Express and reinstalled. The backup works now but what a way to fix a problem, huh?

Thanks for the replies,

Steve

|||

I spoke too soon... I was able to do the backup a couple of times and now the same error is back!!

Have any more suggestions?

Steve

Backup Error:Set property Devices to accomplish this action.

I am getting an error, "Set property Devices to accomplish this action." when doing a backup with SQL Server 2005 Express when I try to do a full (or Differential) backup through the SQL Server Management Studio. I'm using the backup defaults in the GUI and was able to back up the database a couple of days ago without any problem. Today (second time) it just comes up with this error and I have been able to find anything that talks about what it might be.

I tired to look at the script by using the "Script action to new query, file, clipboard, etc." but it fails before it creates the script in any destination. I've rebooted the server and that didn't help.

I found one similar post on this forum but it had no resolution. Any direction on this problem would be greatly appreciated.

Thank you,

Steve

Some components cannot be installed on an alternative location and have to be installed in the %programfiles% directory. If this one is located on the system drive you will not be able to install it on an alternative drive.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I did specify the data directory to be in a location other than the program's default but all the code is in the program directory. I will uninstall and reinstall everything in it's default location if that is the problem.

Steve

|||

What do you mean by "all the code" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

In the SQL setup you are given the option to store the SQL application ("The Code") in one location and the Data in another. I let the application install in the default location in the program directory and changed the data to be stored in a non-standard directory (ie. c:\data\mssql).

sr

|||

That should work for you.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I uninstalled everything that had to do with SQL Server 2005 Express and reinstalled. The backup works now but what a way to fix a problem, huh?

Thanks for the replies,

Steve

|||

I spoke too soon... I was able to do the backup a couple of times and now the same error is back!!

Have any more suggestions?

Steve

Backup databases programatically.

Hello

1. Sql Express supports SMO?
2. If so How can I make a backup of a database programatically?
ThanksI found how do it. but still waiting for the other answers.

Its so easy, I love Microsoft Tools.

using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
class Program
{
static void Main()
{
Server svr = new Server();
Backup bkp = new Backup();
bkp.Action = BackupActionType.Database;
bkp.Database = "AdventureWorks";
bkp.DeviceType = DeviceType.File;
bkp.Devices.Add(@."c:\SMOTest.bak");
bkp.SqlBackup(svr);
}
}
}

|||SQL express does indeed support SMO.|||Thanks|||

I am using the example code with minor modifications and can't get around the unable to connect error.... Has anyone run into this issue? I have tried everything I can think of at this point and it just won't connect to the local instance.

|||

You do know that you can overload the Server instantiation to direct it to the server you want to connect to, don't you?

Example:

Dim srv As Server
srv = New Server("MyLapTop\Express")

In VB.Net you can also set the property in the ServerConnection object:

Dim srv As Server
Dim srvConn As ServerConnection

srv = New Server

srvConn = srv.ConnectionContext
srvConn.LoginSecure = True
srvConn.ServerInstance = "MyLapTop\Express"

Either one should work for you.

Backup and Restore using SQL Server with Merge Replication

We have a project where we are replicating filtered data sets to SQL Express
clients. We are thinking of a back and restore approach that uses SQL
Express. The a SQL Express Instance would receive all data thru replication
then that data set would be backed up. We are pursuing the approach because
of distance and location of the central replication server and logistics of
backing it up. Or managers have requested Microsoftâ's documented or
undocumented view on this approach. If there is any online documentation
that outlines a process like this or a document that illustrates that this
can be done and is a feasible back and restore approach, please post back.
Thanks in advance.
--
Alexander L. Wykel
MIT ProfessionalsHi Alexander,
I am not sure if I totally understand your meaning. Based on your
description, I understand that you have a project which can replicate
filtered data sets to your SQL Express clients and that you would like to
know of a backup and restore approach for your SQL Express clients so that
they can backup the data set after they receive all data through
replication function in your project. So your question here is that you
would like to know a method for backing up your data set in your SQL
Express client, right?
If I have misunderstood, please let me know.
Unfortunately there is no backup method at partial data set level in SQL
Server 2005, but only at file level or database level. Regarding this, you
can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
At SQL Server side, I think that you may consider the following two ways:
1. Use incremental database backup for your SQL Express client.
2. Create those tables on a dedicated file and then only backup your
database on that file.
Of course if your client database is not large, you may directly perform a
full database backup. This depends on your database size and performance
requirement.
You may refer to the following articles:
Copying Databases with Backup and Restore
http://msdn2.microsoft.com/en-us/library/ms190436.aspx
Backing Up and Restoring Databases in SQL Server
http://technet.microsoft.com/en-us/library/ms187048.aspx
Strategies for Backing Up and Restoring Merge Replication
http://technet.microsoft.com/en-us/library/ms152497.aspx
Backing Up and Restoring Replicated Databases
http://technet.microsoft.com/en-us/library/ms151152.aspx
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Sorry Charles,
My outline of the project details left al lot of unresolved issues for you
to have to piece together. The process is simple, for now forget about the
individual clients whom are receiving filtered data sets. What our primary
aim is to replicate the entire data set from the publisher to a local
Subscriber SQL Express instance. Our Corporate network is distributed and
our disaster recovery center is located off site (which we intend on
implementing via hot spare), so we would like to have local copy of the
entire data set (the data that is really important to our organization, to
use to rebuild the server while itâ's down and the hot spare is in use) not
the replication and publisher subscriber information, that we would have to
rebuild. If worst comes to worst we can rebuild that from the main
replicated data set which will be managed locally.
I guess the critical path question is can SQL Express server in this
capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
an SQL Express Subscriber Instance? I have heard of Replication Database
Mirroring techniques:
â'The Publisher and Distributor must be Microsoft SQL Server 2005.
Subscribers can be SQL Server 2005 or a previous versionâ'
â'Replication supports mirroring the publication database for merge
replication and for transactional replication with read-only Subscribers or
queued updating Subscribers. Immediate updating Subscribers, Oracle
Publishers, Publishers in a peer-to-peer topology, and republishing are not
supported.â'
SQL Server 2005 Books On-line (September 2007)
Replication and Database Mirroring
- http://msdn2.microsoft.com/en-us/library/ms151799.aspx
Not sure if this means we can use this technology. From what Iâ'm reading it
looks like it does, could you clarify it for me please?
--
Alexander L. Wykel
MIT Professionals
"Charles Wang[MSFT]" wrote:
> Hi Alexander,
> I am not sure if I totally understand your meaning. Based on your
> description, I understand that you have a project which can replicate
> filtered data sets to your SQL Express clients and that you would like to
> know of a backup and restore approach for your SQL Express clients so that
> they can backup the data set after they receive all data through
> replication function in your project. So your question here is that you
> would like to know a method for backing up your data set in your SQL
> Express client, right?
> If I have misunderstood, please let me know.
> Unfortunately there is no backup method at partial data set level in SQL
> Server 2005, but only at file level or database level. Regarding this, you
> can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> At SQL Server side, I think that you may consider the following two ways:
> 1. Use incremental database backup for your SQL Express client.
> 2. Create those tables on a dedicated file and then only backup your
> database on that file.
> Of course if your client database is not large, you may directly perform a
> full database backup. This depends on your database size and performance
> requirement.
> You may refer to the following articles:
> Copying Databases with Backup and Restore
> http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> Backing Up and Restoring Databases in SQL Server
> http://technet.microsoft.com/en-us/library/ms187048.aspx
> Strategies for Backing Up and Restoring Merge Replication
> http://technet.microsoft.com/en-us/library/ms152497.aspx
> Backing Up and Restoring Replicated Databases
> http://technet.microsoft.com/en-us/library/ms151152.aspx
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================>
>
>
>
>
>
>|||I have one minor correction, the site we intend to replicate the full data
set is off site (SQL Express Subscriber). This allows us to incrementally
send data as it arrives as apposed to sending large backup distributions.
Then the main back up would then proceed from the off site location. We
intend to back up the MDF file as apposed to creating a backup, then if the
main location goes down we send the attach the MDF and rebuild the Publisher
Data Set from the full copy of the Subscriber MDF data set.
--
Alexander L. Wykel
MIT Professionals
"Alexander Wykel" wrote:
> Sorry Charles,
> My outline of the project details left al lot of unresolved issues for you
> to have to piece together. The process is simple, for now forget about the
> individual clients whom are receiving filtered data sets. What our primary
> aim is to replicate the entire data set from the publisher to a local
> Subscriber SQL Express instance. Our Corporate network is distributed and
> our disaster recovery center is located off site (which we intend on
> implementing via hot spare), so we would like to have local copy of the
> entire data set (the data that is really important to our organization, to
> use to rebuild the server while itâ's down and the hot spare is in use) not
> the replication and publisher subscriber information, that we would have to
> rebuild. If worst comes to worst we can rebuild that from the main
> replicated data set which will be managed locally.
> I guess the critical path question is can SQL Express server in this
> capacity for our needs? Can you rebuild a SQL Publisher Server Instance from
> an SQL Express Subscriber Instance? I have heard of Replication Database
> Mirroring techniques:
> â'The Publisher and Distributor must be Microsoft SQL Server 2005.
> Subscribers can be SQL Server 2005 or a previous versionâ'
> â'Replication supports mirroring the publication database for merge
> replication and for transactional replication with read-only Subscribers or
> queued updating Subscribers. Immediate updating Subscribers, Oracle
> Publishers, Publishers in a peer-to-peer topology, and republishing are not
> supported.â'
> SQL Server 2005 Books On-line (September 2007)
> Replication and Database Mirroring
> - http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> Not sure if this means we can use this technology. From what Iâ'm reading it
> looks like it does, could you clarify it for me please?
> --
> Alexander L. Wykel
> MIT Professionals
>
> "Charles Wang[MSFT]" wrote:
> > Hi Alexander,
> > I am not sure if I totally understand your meaning. Based on your
> > description, I understand that you have a project which can replicate
> > filtered data sets to your SQL Express clients and that you would like to
> > know of a backup and restore approach for your SQL Express clients so that
> > they can backup the data set after they receive all data through
> > replication function in your project. So your question here is that you
> > would like to know a method for backing up your data set in your SQL
> > Express client, right?
> > If I have misunderstood, please let me know.
> >
> > Unfortunately there is no backup method at partial data set level in SQL
> > Server 2005, but only at file level or database level. Regarding this, you
> > can refer to BACKUP DATABASE statement in SQL Server 2005 Books Online.
> >
> > At SQL Server side, I think that you may consider the following two ways:
> > 1. Use incremental database backup for your SQL Express client.
> > 2. Create those tables on a dedicated file and then only backup your
> > database on that file.
> >
> > Of course if your client database is not large, you may directly perform a
> > full database backup. This depends on your database size and performance
> > requirement.
> >
> > You may refer to the following articles:
> > Copying Databases with Backup and Restore
> > http://msdn2.microsoft.com/en-us/library/ms190436.aspx
> >
> > Backing Up and Restoring Databases in SQL Server
> > http://technet.microsoft.com/en-us/library/ms187048.aspx
> >
> > Strategies for Backing Up and Restoring Merge Replication
> > http://technet.microsoft.com/en-us/library/ms152497.aspx
> >
> > Backing Up and Restoring Replicated Databases
> > http://technet.microsoft.com/en-us/library/ms151152.aspx
> >
> > If you have any other questions or concerns, please feel free to let me
> > know. Have a nice day!
> >
> > Best regards,
> > Charles Wang
> > Microsoft Online Community Support
> > =========================================================> > Delighting our customers is our #1 priority. We welcome your
> > comments and suggestions about how we can improve the
> > support we provide to you. Please feel free to let my manager
> > know what you think of the level of service provided. You can
> > send feedback directly to my manager at: msdnmg@.microsoft.com.
> > =========================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > =========================================================> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Hi Alexander,
Thanks for your response.
Indeed Database Mirroring can provide high availability (automatic
failover) at database level, however it does not support SQL Server Express
Edition. Only SQL Server 2005 Standard/Enterprise Edition can support this
feature.
For SQL Server Express Edition, you can use SQL Server replication to
replicate data from your publisher to your subscriber. However do you
really need merge replication? If the data change on your subscriber need
not be merged to your publisher, I recommend that you use Transactional
replication so that you can win the best performance among the existing
three types of SQL Server replication.
Though transactional replication has very high performance for replicating
data, actually it is also not a real time replication as well as other type
of replication, so if you want to use detach/attach methods to recover your
database on your publisher, there might be still at a risk of a little part
data loss. Since the data is very critical to your organization, I
recommend that you may consider the following solutions for your publisher
server: SQL Server failover cluster, or Database Mirroring, or Replication
together with Database Mirroring, or Replication together with failover
cluster.
With SQL Server failover cluster or Database Mirroring for your publisher
servers (two or more nodes), when the primary publisher is down, the
secondary publisher will automatically failover to be the primary
publisher. By using replication together with the failover function, it
brings you an additional level of security safeguard. You can distribute
your data to a different location. If some emergent situations happen, you
can recover your publisher by using this data, though there might be a
little data loss.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Backup and restore of mdf database

Hi,

I've got a following problem: I have a VB 2005 application that uses SQL Server 2005 Express as DB engine. This application has it's own database stored in mdf file, e.g. invoices.mdf with corresponding log file.

I'd like to give users opportunity to backup and restore this DB file from this application (there won't be a system administrator, only end-users). Unfortunatelly after I connect to this database (using user instance of SQL Server), I'm unable to read or copy it, despite the fact, that I close all connections. And I'm sure that all connections are closed.

Database files become accessible for reading after I close the whole application.

I've tried to detach this database using sp_detach_db prcedure, but it can't be done with database I'm connected to.

I will be grateful for any help.

Tomasz

hi Tomasz,

obviously at least 1 connection is still open referencing the database you are dealing with..

try changing the database context to the master system database (that can be done in .Net via the workingConnection.ChangeDatabase("master") statement...)

a workaround to "orphaned" connections could even be an alter database statement to close all pending connections, like

USE master;

GO

ALTER DATABASE dbName

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO-- and the counterpart is

ALTER DATABASE dbName

SET MULTI_USER WITH ROLLBACK IMMEDIATE;

but I'd prefer to find and close all pending connections...

regards

|||

We just had a couple threads about this topic in the forum over the last week. I've summarized how to use SMO to handle Backup and Restore of User Instance database in my blog. Backup should work with connection open but restore is not so forgiving.

Mike

|||

Hi,

thanks for this advice, I will try today to implement backup/restore approach according to the blog.

I'm 100% sure that I close all connections to the DB. I've made a very simple application which just connects to the database and immediatly closes this one an single connection. After that mdf file remains locked until I close the application. It seems, that there is some "stealth" connection, which I can't close directly. You can replicate this behaviour by creating a new application which on start does:

Dim lCon As New SqlClient.SqlConnection(sConnStr)

lCon.Open()

lCon.Close()

lCon.Dispose() 'even that won't help

And then try to copy mdf file to other location while this application is still running.

greetings

Tomasz

|||

Hi,

I've got the same problem as Tomasz. I read all that was written and I was wondering if there isn't a command capable of closing all connection to the database file (.mdf). I just need to upload it to a remote location not restore it.

Thanks in advance

Backup and Restore Databases Through XML in SQL Express 2005

Can we backup to XML file and restore from XML file in SQL Express 2005?

Thanks in advance.

No, you can't.

Best regards,

Eugene Kogan,

Technical Lead,

Microsoft SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Any other alternatives other than XML?|||

Please read articles about BACKUP and RESTORE in SQL Server 2005 Books Online. Also, take a look at "Backing Up and Restoring Databases" article.

Best regards,

Eugene Kogan,

Technical Lead,

SQL Server Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

Backup and restore database from local drive to hosting server

Hello all,

I'm new to SQL server and I've just seutp a website using the new Visual Web Developer and SQL 2005 Express. I've got my website out on the hosting site server but I need to copy my database file to the hosting site's sql 2005 server. I asked my provider how to do this and they advised to use SQL Server Mgmt Stuido and to perform a backup of my local database file and then do a restore to their server. First they said to create a database on their server using a web based control panel provided with the site controls (this would be a temporary database overwritten by my local copy on the restore - I'm assuming). Then I could do my backup and restore.

I've not been successful doing this. I can connect to their server and my temporary database and can also backup my local database. I cannot, however see how to restore my backup copy to the remote site's sql server. When I choose to restore a database on the remote site, I am only provided with a file browser with folders the SQL Server instance can see. These obviously are not any of my local files so I can't use my local backup. I can't backup my file directly to the server because of the same issue, my local SQL instance can only see my local files, not the remote site directories.

I am probably overlooking something obvious but I really could use any input offered.

Thanks in advance for any help.

Regards,
Scott

Hi all:

Update to this issue, I finally resolved it. I just decided to recreate the database manually on the server using scripts. I connected in SQL Server Mgmt Studio to my remote db server and ran all the scripts I created from my local database. This took a while but I was able to finally get it finished and it now works great.

Backup 2005 Express

Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
Samuel
Express doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>

Backup 2005 Express

Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>

Backup 2005 Express

Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 200
5 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it w
ill backup every day
> Any advice?
> Samuel
>

Backup

I am very new to SQL Express. A database is located on a local machine. When I try to do a backup I get the following error:

Backup failed for server ‘server name’

(Microsoft.sql server.Express.smo)

Additional information:

Set property Devices to accomplish this action.

(Microsoft.sql server.Express.smo)

Their IT tecnicians insits that I have full administrative rights.

I am lost for a solution.

Would it be wise to shrink the database, detach from the database and copy both the mdf and idf to a different machine? I have never done this procedure before.

Please help.

Could you please send over the executed script ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I am having same problem.
I am connected to sql server 2000 instance.
When trying to backup the database i got the same error
Any idea why ?|||

Which Gui are you using ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Sql Server Management Studio|||

Could you please then post the resulting Backup SQL command which is available through the script functionality within the Backup UI.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Did anyone get a resolution to this? I'm having the same problem. I uninstalled and reinstalled SQL Server 2005 Express and then reinstalled and it worked fine. I was able to back up the database for two days. Then the error started coming up again and now I'm getting the "Set property Devices to accomplish this action." error again.

I have no clue what property device it's talking about or where to set them. Any help would be greatly appreciated!

Thanks,

Steve

Backup

I am very new to SQL Express. A database is located on a local machine. When I try to do a backup I get the following error:

Backup failed for server ‘server name’

(Microsoft.sql server.Express.smo)

Additional information:

Set property Devices to accomplish this action.

(Microsoft.sql server.Express.smo)

Their IT tecnicians insits that I have full administrative rights.

I am lost for a solution.

Would it be wise to shrink the database, detach from the database and copy both the mdf and idf to a different machine? I have never done this procedure before.

Please help.

Could you please send over the executed script ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I am having same problem.
I am connected to sql server 2000 instance.
When trying to backup the database i got the same error
Any idea why ?|||

Which Gui are you using ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Sql Server Management Studio|||

Could you please then post the resulting Backup SQL command which is available through the script functionality within the Backup UI.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Did anyone get a resolution to this? I'm having the same problem. I uninstalled and reinstalled SQL Server 2005 Express and then reinstalled and it worked fine. I was able to back up the database for two days. Then the error started coming up again and now I'm getting the "Set property Devices to accomplish this action." error again.

I have no clue what property device it's talking about or where to set them. Any help would be greatly appreciated!

Thanks,

Steve

Backup

I am very new to SQL Express. A database is located on a local machine. When I try to do a backup I get the following error:

Backup failed for server ‘server name’

(Microsoft.sql server.Express.smo)

Additional information:

Set property Devices to accomplish this action.

(Microsoft.sql server.Express.smo)

Their IT tecnicians insits that I have full administrative rights.

I am lost for a solution.

Would it be wise to shrink the database, detach from the database and copy both the mdf and idf to a different machine? I have never done this procedure before.

Please help.

Could you please send over the executed script ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I am having same problem.
I am connected to sql server 2000 instance.
When trying to backup the database i got the same error
Any idea why ?|||

Which Gui are you using ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Sql Server Management Studio|||

Could you please then post the resulting Backup SQL command which is available through the script functionality within the Backup UI.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Did anyone get a resolution to this? I'm having the same problem. I uninstalled and reinstalled SQL Server 2005 Express and then reinstalled and it worked fine. I was able to back up the database for two days. Then the error started coming up again and now I'm getting the "Set property Devices to accomplish this action." error again.

I have no clue what property device it's talking about or where to set them. Any help would be greatly appreciated!

Thanks,

Steve

Backingng Up a Database that is Attached

Hi,

I was trying to figure out how to back up a database that has been attached to the SQL Express instance. I found on the web this tutorial and tried to use it, however it seems like it cannot find the database. The DB is attached by the web application using AttachDbFilename in the connectionstring.

http://www.sqldbatips.com/showarticle.asp?ID=27

Any ideas?

thanks

If you are doing personal development you download the eval version and install it as a named instance and register the Express so you can manage it with Management Studio. It is good for 180 days but if you are developing full application buy the developer edition it cost $60 on the web and do the same. And one more thing the database is not attached to Express it is created in Express which is a full RDBMS(relational database management system) without the GUI tools. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

Hi,

I actually do have the the developer edition, and when I used the management studio to connect to SQL Express server (on a different machine) I was still unable to see/view this database thats was attached by the web app. I know the database was attached, because the web app was functioning properly and displaying/inserting data in the database.

|||If you have the developer edition you just right click at the top of management studio to register the Express and the Express becomes local to you. Then you use the backup and restore wizard to backup your databases in the Express. I have registered 68 SQL Server 2000/7.0 in one box. If you have more questions post again. Hope this helps.|||

Hi,

The problem is I do not see this databse in the pulldown in the wizard. I see all the other databses on the server, but none of the databses that the web app atached through AttachDbFilename.

Am I missing something?

tony

|||Well I think it is because you used just half of the database which is the data file, so check the Microsoft SQL Server folder under programs and check the data sub folder to see if the LDF(log data file) is there. If not create a blank database with same name and do INSERT INTO to recreate the database and delete the existing ones of all the attached databases because you need the log file and you also need to backup your databases. And no you are not missing anything Microsoft is using RDBMS(relational database management system) as file based database. So the SQL Server wizards may not be seeing all the attached databases because they are missing the log file. Another option is to right click on the database to see if backup is enabled. Hope this helps.