Backup Error:Set Property Devices To Accomplish This Action.
Aug 20, 2007
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.
hi, I usually back up the database to a file within the sql server box. then use a tape backup to back up the whole directory of the sql server in the d: drive.
Is this right way to do or there is a better way ?
I have taken backups and the backup devices are stored in C: drive and now i want them to be moved onto the other drive.So how can i do it without creating new devices and scheduling them again.
i need to initialise the backup devices as the the backup files have grown so big thats its occupy hard drive space , each device is for @ day for all databse we use , what do i do
Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.
Hope someone can help as the thought of doing all of this individually for each database scares me silly!!! :)
I am using EMS SQL Manager for SQL Server (www.sqlmanager.net). I am trying to learn how to do backups of my database.
I am up to this step -> http://www.sqlmanager.net/products/mssql/manager/documentation/hs21640.html
I don't think I actually understand what a device is, how it's created, where it resides, etc. I would like to backup my remote datasource to my local computer's harddrive.
Can someone please explain how this 'device' works and how I should set up?
Hello, I am using EM to administer SQL 6.5 SP5a and have a few dBs that do not show backup history by expanding the backup device. The server is regestered as SA and MSDB does contain the the correct data in the sysbackuphistory table. MSDB DBCCs OK and other dBs show history. I manage multiple SQL 6.5 servers but this is the first time I've seen this and can find no info in the various KBs or NGs.. Anyone have any clues? Thanks, Jack
I am trying to backup a VLDB (800+gig) to multiple devices. I am using 4 disk drives. Assuming all devices have the same performance, should the 4 devices fill equally? (IE 200gig per drive) If so, what would might happen if one of the 4 drives only has 100 gig free with the other 3 having 300 gig free each?
Hi there,I need to take a backup with the following scanerio in mind:At T1 I take the FULL BACK UP of the database called BACK_1_FULL.BACK inc:Then at T2 I would like to take only the differential BACKUP of theTransaction log called BACK_1_TRANSLOG.BACK and at T3 I would like totakeonly the differential BACKUP of the Transaction log calledBACK_2_TRANSLOG.BACK. At T4 my system fails and I need to bring thedatabaseto a point where my T3 state is in. I tried taking the backups in DEVICEbutI always get an error from SQL Server Enterprise manager such asVolume on Device 'Device2' is not part of a family media set. BACKUPWITH NOFORMAT can be used to form a new media set. BACKUP LOG is terminatingabnormally.If someone can give suggestions on how to backup and restore in thissituation it will be really helpful.ThanksJohn*** Sent via Developersdex http://www.developersdex.com ***
Hi, I've to restore a bk into a new DB, because the old one has a lot of devices created on different places and phisical disks. Im not sure to do that, cause Ive heard some problems with this action.
The old Db has 10 devices of data and 10 devices of log, and I want to create only one device of data (sum of all the data devices) and only one device of log (sum of all log devices) Is it possible or not?
I have a database size of 9.8TB and I backup it to 30 backup devices. Each one has 110GB after backup compression.I tried to restore these files to standby server via 100MbE network but it always failed. My colleague told me this never happen before and I said yes because I have done this before a lot of times when backup devices are significantly smaller.He said the only way is to copy backup files locally and restore locally. But I am trying another method, I create more: 64(maximum) backup devices and try to restore via network again.SQL server version is Microsoft SQL Server 2012 (SP1)
- 11.0.3401.0 (X64) Jan 9 2014 13:22:15 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) .
I'm trying to restore a database using SSMS GUI. In "Select backup devices" window if I click the Add button there the window and the whole process hangs like it will never work.
I tried to clear backup history, but that didn't solve it.
I tried with a couple of other databases, but same thing. It was working fine!
According to SQL Server 2005 Books Online, the RetainDays property specifies the number of days that must elapse before a backup set can be overwritten.
I do not quite understand the implication of this property. Say, if I set RetainDays = 1, is it mean the backup set from yesterday MAY BE overwritten? If it is 'may be', how does SQL Server determines whether to overwrite or not? I tried RetainDays = 1 to backup a dummy database, but my test.bak keeps growing, the old files do not seem to be overwritten. Besides, what is the implication of accepting the default RetainDays = 0?
I just installled the Visual web developer - express addition. When I try to add aSQL database to my project it gives the following error - "User does not have permission to perfor this action" . Can someone please help?
Have run about six downlowds and all fail with same problem. The installer seems to be missing a file. all other parts of Viz studio Exp install but NOT the sql server exp.
Am I the only one getting this ? and if so what is the problem?. All other Visual Dev Express parts have instald OK.
Run on XP home SP2. As I posted this I noticed the compiler time stamp is 4 days ahead of real time ???
From the install log:
Complete: ParseBootstrapOptionsAction at: 2006/9/22 15:34:19, returned false Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run: Could not parse command line due to datastore exception. Source File Name: utillibpersisthelpers.cpp Compiler Timestamp: Wed Oct 26 16:38:20 2005 Function Name: writeEncryptedString Source Line Number: 124 ---------------------------------------------------------- writeEncryptedString() failed Source File Name: utillibpersisthelpers.cpp Compiler Timestamp: Wed Oct 26 16:38:20 2005 Function Name: writeEncryptedString Source Line Number: 123
I have a table and one of the fields is a datetime and named "Finish." What I would like to do is when the value in Finish has expired ( FINISH < NOW ) to call a procedure and change the status to of this record. I was thinking to set a MS SQL timer/delay on each row - but that does not seem pratical or is it?
I'm a new user to SSIS and am trying to figure out something that I suspect may be very basic, but I'm having a hard time figuring it out.
I have a single table of "events" generated by an application. I will get a daily feed of these events. Events may be like so:
Event A Created Event A Modified Event A Property A Created Event B Created Event C Created Event B Property A Modified Event A Property B Created Event C Voided Event A Property A Closed Event A Closed
In the end I want to create two tables like so:
Table 1: Event A / Closed Event B / Modified
(Event C is not here because it was voided, so any prior records of C are gone).
Table 2: Event A / Property A / Closed Event A / Property B / Created
(Only events with properties would be in this table).
In essence, I'm collapsing the transaction-based feed into normalized event-based and property-based tables, with only the latest "status" stored. These tables would be used to generate various other reports.
Obviously, as usual, the true situation is more complex than this but this simplified example covers my basic problem and I hope it can help someone to point me in the right direction.
"Events" can span over a day. It isn't practical to recreate the two sub-tables every day from scratch using SQL (and we don't want to do a complex query against the massive source table).
I would like to iterate over the new batch of events one record at a time. If I get an Event Created, then I will create a row in Table 1. If I get an Event Property Created, I will create a row in Table 2. Voids would cause a delete in both tables. Modifies of Events or Properties would cause the appropriate update in either table (Creates will always come before Modifies in the feed).
I tried using a Conditional Split, but I found that it appears to not go record-by-record, but instead prepares a list of records for each condition and then processes it in parallel... That was quite a shock. I expected it to go record-by-record and process things in order because I depend on "Create" to create the records that "Modifies" will update.
So my question is, is there a way to do what I am looking for in SSIS? Am I missing a simple setting to make Conditional Split process records in order?
I am new to SSIS and I have been playing with it for the last couple of days. I would like to know if anybody can give me some feedback or point me in the right direction ( best by looking at an example ) to acomplish the following :
I have a table called Policy ( free foxpro table ). This table contain policy insurance information and also customer information. I would like to upload and normalize this table to an existing "PolicyAttributes" SQL Server 2005 table. Also I would like to create a new record in the Customer table if there exist a customer in the policy Table, thus separating the customer information from Policy table ( normalizing the table ).
The policy table has a field called CustomerName_1 if it is empty then this policy does not have any cutomer, but if it has a name then I would like to insert a new record into the SQL Server Customer table and then get the unique number from the newly created record in Customer table and update it in the SQL Server Policy table ( let say CustomerID ). This way I can keep the relationship PolicyAttibutes --->Customer on the CustomerID field.
So basically here are the logical steps:
1.- Read Policy records from foxpro table 2.- insert record to SQL Server PolicyAttributes table 3.- for each customer name in the foxpro Policy table insert a new record in the SQL Server Customer table. 4.- Get the unique number from the new Customer record ( Primary Key of Customer record) and update this number in the PolicyAttibutes field called CustomerID ( see above step 2 ). This way I can keep my PolicyAttributes---->Customer relationship.
Also the foxpro Policy table can contain more than one customer. It can have up to 4 customers. So if there are 4 customers then I will have to insert 4 records in the customer table. CustomerName_1 CustomerName_2 CustomerName_3 CustomerName_4
Any help is greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!.
I need to write a package the does the following, and not sure the best approach:
The package will read a list of stored procedure names stored in a table. Then, for each stored procedure name, I need to run the stored procedure and save the output to a named file.
That is, run sp1 and output to file sp1.txt. That is, I need the name to reflect the name of the stored procedure.
Also, each time I create the new output file, I need to delete the old named file.
I'm sure this is a fairly straightforward task, but not sure how to do the "dynamic" file naming part.
Hi folks,This is my first post on the SQL forum and maybe it's a dummy question but... i didn't figure it out.Also, i'm not sure if it's the right place for this type of questions, excuse me if i'm on the wrong spot. Here we go: I'd like to have on my web sites some articles and bellow each article a list of "Related Articles".I started with something like this:
After that, i noticed that i have an "One to Many" relationship between the 2 tables (fields ArticleID) but, if i try to create another relationship like ArticleRelated.ArtigleRelatedID -> Articles.ArticleID it will looks like a circle between the 2 tables and i'm not sure if it's the best way (even if it will work). Before somebody ask "Wy do i need this third relationship", besides other good reasons, i'm using "LINQ To SQL" and i just drag and drop the tables to the designer window and it automatically create classes and colections based on the existent relationship.I appreciate any help.
I'm looking to build a menu / sitemap out of my 2005 SQL Server database, an example of my table structure is:
ID PageTitle ParentID ======================================= 1 Home 0 2 About Us 1 3 Contact Us 1 4 About Us / Sub 1 2 5 About Us / Sub 2 2 6 About Us / LVL 3 5
So essentially this menu should display as follows: Home About Us About Us / Sub 1 About Us / Sub 2 About Us / LVL 3 Contact Us
Is it possible to write a query that would output menu in that specific order?
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
I have tried and tried but I get all types of errors.Someone I hope has done this and perhaps can explain in more detail how to get this done. I have my Store Procedure.... I added output to the data I will be needing. at the bottom I added my code....I need to some how run this procedure...Get all the info in the select statement and display it in a form.I want to use a variable for each field...Ihave no clue why my code does not work... CREATE procedure dbo.App_Login_NET ( @LoginName nvarchar(15), @Password NvarChar(15) ) as select UserName OUTPUT, UserPassword OUTPUT, UserClinic OUTPUT, UserTester OUTPUT from Clinic_users where UserName = @LoginName and UserPassword = @Password GO
When I type in a 'correct Username/Password' it gives me this error Procedure or function Appt_Login_NET has too many arguments specified. I just want to contain the values in separte variables for later use...Also how do you use rowcount????
my Code
Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest") Dim cmd As New String("Appt_Login_NET") Dim mycommand As New SqlCommand(cmd, con)
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder). The SQL Server Express error log is clean and indicates full functionality. Everything is running locally. No VWD installation errors.
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
I'm trying to find if there is a combination of dataflow transformations that will produce the following result
SELECT
period,
project,
task,
employee = CASE
when empid in (SELECT DISTINCT empid FROM EmpTable) then empid
else 'Deleted Employee'
end
FROM ProjectTable
I know I can create a dataflow task with this query as a data source and then send it to a destination, but I was wondering if that is the best way to do it or if there was a better way to do this using the data transformations available in SSIS.
Hello, am trying to run a SP in my class against my database. However, I get the error below. The connection string is in the web.config file as thus <connectionStrings> <add name="cell_ConnectionString" connectionString="Data Source=xxx.xxx.xxx.xxx,2433;Network Library=DBMSSOCN;Initial Catalog=day;User ID=day;Password=jdhje5rhydgd;" providerName="System.Data.SqlClient"/> </connectionStrings> and the connection string name is called in my class as thus 1 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cell_ConnectionString"]); 2 3 SqlCommand command = new SqlCommand("cell_ConnectionString", con); 4 command.CommandType = CommandType.StoredProcedure; 5
What is wrong here ? thanks Ehi
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.Source Error:
Line 49: command.CommandType = CommandType.StoredProcedure; Line 50: Line 51: con.Open(); Line 52: Line 53: command.Parameters.Add(new SqlParameter("@csv", SqlDbType.VarChar, 8000, "recipients")); Source File: c:inetpubwwwrootizevwwwrootwelcomesms.aspx.cs Line: 51
Hi guys, I'm getting this error:System.InvalidOperationException: The ConnectionString property has not been initialized when I'm trying to connect to an SQL database using the following code: Dim mySelectQuery As String = "SELECT [tabl_id] FROM [myTable]"Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("myConnString"))Dim myCommand As New SqlCommand(mySelectQuery, myConnection)myConnection.Open()Dim myReader As SqlDataReadermyReader = myCommand.ExecuteReader()While myReader.Read() Response.Write(myReader("tabl_id") & "<br/>")End WhilemyConnection.Close() and in web.config: <appSettings/> <connectionStrings> <add name="myConnString" connectionString="Data Source=100.100.100.100;Initial Catalog=myDatabase;User ID=me;Password=password" providerName="System.Data.SqlClient"/> </connectionStrings> If I place the actual connection string inside instead of trying to reference the web.config setting it works, but using 'System.Configuration.ConfigurationManager.AppSettings' returns the error. Is there something I'm doing wrong?
Dear All,(No aswers on access newsgroups)Access2000.adp connected to SQL-server 2000MainForm unbound: seachform on companies meeting a complex combination ofcriteria.SubFrom and ListBox to display the searchresult ( 2 outputs to test what'sbest).Searchresult is stored in a creatable ADO-recordset (see code) and then1. Converted to string (GetString) to be set as valuelist for a listbox.2. bound to the subform.The Listbox shows the resultdata correctly but is limited in length.The subform shows the records but all fields are empty or displays #errorHow should I bound the controls correctly to display the fieldvalues?If I let them unbound the show up empty.If I set them to the recordsets' fieldnames it show #error.On my Notebook using MSDE there is no errormessageOn my Desktop i get error 7965 not a valid recordset property.CAN'T FIND WHAT'S WRONG! Cursortype ??FilipCODE'Store result in creatable ADO-recordset: Add fieldsWith ResultRSWith .Fields.Append "CompID", adInteger.Append "CompName", adVarChar, 255.Append "BasicPrice", adVarChar, 255End With.CursorLocation = adUseClient.CursorType = adOpenStatic.OpenEnd With[color=blue][color=green]>> Code for Search[/color][/color]'Set Rowsource Listbox and subformIf ResultRS.RecordCount > 0 ThenstrSearchResult = ResultRS.GetString(adClipString, , ";", ";")Me.Formulier1.Form.Visible = TrueSet Me.Formulier1.Form.Recordset = ResultRSMe.CmbSearchResult.Visible = TrueMe.CmbSearchResult.RowSource = strSearchResultEnd IfSearchEnd:'Clear memoryIf rs.State = adStateOpen Thenrs.CloseSet rs = NothingEnd IfIf ResultRS.State = adStateOpen ThenResultRS.CloseSet ResultRS = NothingEnd If
