Is there any method to back up the database to place outside of the local server system?

SQL Server Database Engine

Index ‹ SQL Server

- Previous
  - 1
    - SQL Statements with inner join too slow in 2005 runs faster in 2000 Thanks for the info. Sorry for not being clear, but by "schema of the table", I meant the "definition of the table". In other words, I'd like to know the CREATE TABLE statements for those tables. Thanks. Tag: Is there any method to back up the database to place outside of the local server system?
  - 2
    - Distinct Count Problem I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message. Can anyone help with this Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key). David Tag: Is there any method to back up the database to place outside of the local server system?
  - 3
    - sub-reports don't start in the first page Just following up on the topic of subreports. Are there any plans to inlcude the subreports header and footer when embedded in another report This is a limitation of this "feature". If this is not addressed, maybe renaming the subreport to subreportBODY since you're only really referencing the body of the subreport. And if someone from Microsoft happens to read this post, can you please provide some feedback on: The subreport "feature"/issue mentioned above. No "keep together' for table groups (seems odd that this is not available as it's a common function in Crystal, etc.). This is a big oversight in my opinion. Try creating an Accounting statement for a client that lists their invoices and the line items on the invoice. You wouldn't want any of the invoices to be split across pages but that's SSRS default way to handle this. Not being able to sum the results in a group footer. When using Jump to Report/URL, the parent report is not rendered when using the browser back button from the report you jumped/drilled to. This is a big usability issue when using Report Manager. Any plans for adding the ability to deploy reports to subfolders Only workaround now is to create multiple VS projects which is not an ideal situation. Delete and move options are shown to users in Report manager even if they don't have permission to do that. Only after the user selects delete/move do they get a permissions error. Tag: Is there any method to back up the database to place outside of the local server system?
  - 4
    - Partitionning a Table Hi guys, I am creating a program, and for that i implements function to partionate a Table (which are too big). In Oracle it's simple : ALTER TABLE myTable SPLIT PARTITION .... But for MS Sql Server 2005, I encounter some problems. Do you have an idea How can i partitionate a Table without make TSQL Functions Is it possible how can I split Tables simply. Thanks ;) Tag: Is there any method to back up the database to place outside of the local server system?
  - 5
    - Questions on attributes acting as both inputs and predictive? Hi, all experts here, Thanks a lot for your kind attention. I am having quesion about attributes acting as both inputs and predictive outputs in a mining models, I mean if we are going to predict the outputs, then I cant actually see the effects of themselves acting as inputs as well I dont really see through the points of it. Could please any experts here shed me any light on it. I am looking forward to hearing from you shortly and thanks a lot in advance. With best regards, Yours sincerely, Tag: Is there any method to back up the database to place outside of the local server system?
  - 6
    - Subtotal question Hopefully this is a simple question. We have a report with several subreports that display certain totals by a group. What we want to do now it to show a running total on the main report. Is there a way to pass data from the subreport (the subtotal) to the main report for a Grand Total Thanks for any information. Tag: Is there any method to back up the database to place outside of the local server system?
  - 7
    - How do I encapsulate Transactions in a data entry form with multiple insert and update statements? Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above. Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not. Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement. Tag: Is there any method to back up the database to place outside of the local server system?
  - 8
    - MessageSource expression causes false "max width" exception - why? Greetings, I have a package that uses a complex expression for the MessageSource property in a Send Mail task. I receive the email that is composed based on the expression and it is formatted properly. However, the application also throws an exception complaining that the width of the expression may be too wide. This is the error message (with the long expression represented by "...") from the exception: Error Code: -2147192616 Error Description: The result string for expression " ... " may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR. I tried wrapping the expression in SUBSTRING(expression, 1, 4000) to guarantee that the resolved expression could not cause an exception but that didn't help. Numbers lower than 4000 also do not solve the problem. Does anyone have a solution or advice Thanks, BCB Tag: Is there any method to back up the database to place outside of the local server system?
  - 9
    - Check for column before adding a column How can I test to see if a column exists before adding a column to a sql mobile table thanks, Luis Tag: Is there any method to back up the database to place outside of the local server system?
  - 10
    - Why is this Query Failing I am running 2 queries using a temp table My first query runs fine and creates the temp table #TEMPCOMM1 I then try to run the next query and I get the following error: Msg 245, Level 16, State 1, Line 17 Conversion failed when converting the varchar value ',' to data type int. Code Snippet Select Distinct DLSiteID , PropID , CommAnomalies = substring ( ( SELECT ',' + ErrorLevel as [text()] FROM #TEMPCOMM1 t1 WHERE t1 . DLSiteID = t2 . DLSiteID FOR XML path ( '' ), elements ), 3 , 1000 ) FROM #TEMPCOMM1 t2 Any idea on what I do to fix this problem Tag: Is there any method to back up the database to place outside of the local server system?
  - 11
    - Error executing SQL Script from VB:NET Hi. I have a complete script from a Database. But when I try to execute this script (script.txt) from VB.NET shows me many errors. Basically, the special caracters (vbreturn, vbtab, etc.) How execute this script or format the text to generate the database without problems Thanks 4all Tag: Is there any method to back up the database to place outside of the local server system?
  - 12
    - Index Fragmentation on SQL 2000 I would 'much' rather be 'beaten by one minute' that to be beaten by a baseball bat. ;-) Tag: Is there any method to back up the database to place outside of the local server system?
  - 13
    - Store and Retrive Images (JPEG/PNG/GIF/JPG) in sql server express 2005 in ASP.net through C# Hi friends plese help me for my little things. I m student and develop a website. i want to know how images /pics and video and audio files are store in Sql server2005 database. and ya i want to store whole file not just its address. i using asp page like uploadImage.aspx in that i put UploadFiles control now i m confuesd to storing Images so plese help me and give code or some good easy trick at my level And once store image or video and audio file in sql database then after how i used in my asp.net page. which control i wish to used for this files so plese this also slove it. give also code or any easy trick for fullfil my task.. i hope u all help me Thanx in advance to u all friends Tag: Is there any method to back up the database to place outside of the local server system?
  - 14
    - Cannot Apply SP2 to 2 new nodes in a 4 node cluster ? HI There We had an existing 2 node active / active cluster, 1 running a default instance of Sql Server 2005 Enterprise Edition 9.0.3152 (SP2 + Hotfixes) and the other running a named instance of the same version. We recently added 2 new nodes to the cluster, they were successfully added and we tested the cluster group failover successfuly to the new nodes. Last night we tried to install Sql Server 2005 Enterprise edition on the new nodes. I followed to proper proceudure of modifying the installation for both instances and selecting the 2 new nodes to apply them to. This went 100%. Sql Server 2005 successfully installed for both instances on the 2 new nodes, all log files were successful. We then tried to apply SP2, we tried the following: 1. We ran SP2 from the active node, but when we go to the screen to select what you want to apply SP2 too we could not select anything, if you clicked on database engine the message said that these instances were already at a later version and we could not proceed. This is how i successfully applied SP2 to the original 2 node cluster but it does not work for additional nodes to an exisitng cluster. This is also what all the documentation we could find said, refer to SP2 release notes under the topic "Failover Cluster Installation", it is also the method we found when googling. 2. We then tried what is described under SP2 release notes "Rebuild a SQL Server 2005 SP2 Failover Cluster Node". We ran SP2 from the new nodes while they were passive, but when we got the screen where you select what to apply the SP2 too we could not select database engine the message at the bottom said that SP2 must be run from the active node and that we were attempting it from the passive node, this is what we tried in step 1 described above. 3. This was a last resort. We were advised to try failing over the instance to the new node and then running SP2. Personally i thought this was a bad idea, one should never fail over a instance of sql server to a node with incompatible binary versions and secondly when we installed sql server on the new nodes a warnng popped up before hand stating that the instances were at a later version and that the new nodes must be at this version before attempting fail over. I thought that sql would not even start, to my surprie we successfully failed over the sql group to the new node, when we ran SP2 it looked good we could select the database engine on the new node to apply SP2 too, BUT after clicking next after a few seconds the SP2 installation just closed, NO INFORMAITONAL MESSAGES NO ERRORS NO WARNINGS it just closed an never came back. I had never seen this happen on a cluster before, needless to say this made me very nervous so we failed the sql group back to the original nodes and gave up. PLEASE can some tell me how to apply SP2 to 2 new nodes in a 4 node cluster all methods descibed in SP2 release notes and other documentation as descibed above in step 1 and 2 do not work ! Thank You Tag: Is there any method to back up the database to place outside of the local server system?
  - 15
    - Data transfer from ODBC to Sql Server There's nothing wrong with wanting to learn a new technology - I applaud that. I just got a little confused when you said "I don't know SSIS". Perhaps something got lost in translation. Welcome to the SSIS forum. Lets hope you can solve your problem using this great new technology. -Jamie Tag: Is there any method to back up the database to place outside of the local server system?
  - 16
    - Bulk insert error file problem I am doing a bulk insert of some data that contains a column of xml data. I get an error when trying to do the bulk insert and added the ERRORFILE option. What happens is the error file shows up while the command is running, but as soon as I get the actual error in the messages window of SQL Server Management Studio the error file disappears. The documentation indicates that the error file should contain the records that produce the error, but since the file disappears I'm not able to see the record(s) that produce the error. Please advise, any help is very much appreciated .. thanks, john I get the following in the messages window: Msg 9411, Level 16, State 1, Line 1 XML parsing: line 1, character 725, semicolon expected My bulk insert command: bulk insert Assessor . dbo . Taxes from '\\scux00\ExportSQL\exporttaxes.txt' with ( FORMATFILE = 'C:\Taxstatement2columns.xml' , MAXERRORS = 50 , ERRORFILE = 'C:\Errors.txt' ) the format file: < xml version="1.0" > <BCPFORMAT xmlns=" http://schemas.microsoft.com/sqlserver/2004/bulkload/format " xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance "> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\n" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="ParcelID" xsi:type="SQLCHAR"/> <COLUMN SOURCE="2" NAME="xmlStream" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT> Tag: Is there any method to back up the database to place outside of the local server system?
- Next
  - 1
    - Error "Connection is busy with results for another command" I have looked at other threads regarding errors similar to this, but I think mine is a bit different. I am using SQL2005 Standard Edition and my application is coded with C# using ADO.Net. OLEDB connection is used. The error occurs when the application has only one thread accessing the database. It does not happen consistently, so it is very puzzling. I am wondering if anyone could offer any tip to diagnose this. Thanks in advance! Tag: Is there any method to back up the database to place outside of the local server system?
  - 2
    - SQL2K CmdExec Proxy Account Setup I tried it every which way, including: LocalServerName \ LocalAccountName . \ LocalAccountName Domain \ DomainAccount etc. And in every case, the account used had all the local access privileges required. Tag: Is there any method to back up the database to place outside of the local server system?
  - 3
    - Cubes and Cost-Forecast You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following Code Snippet iif ( not isEmpty ([Measures].[Geplanter Gewinn]), [Measures].[Geplanter Gewinn], iif ( [Time].[Date]. PrevMember IS NULL , null , ([Measures].[Gewinn], [Time].[Date]. PrevMember ) ) ) In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following: Code Snippet iif ( not isEmpty ([Measures].[Geplanter Gewinn]) ,([Measures].[Geplanter Gewinn]) ,([Time].[Date]. PrevMember ) ) Which says 1. If Geplanter Gewinn is not empty return that 2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn. Tag: Is there any method to back up the database to place outside of the local server system?
  - 4
    - INSERTING NULL VALUES VIA STORED PROCEDURES Hi, Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this: errorParams[3].Value = (e.InnerException== null ) ( object ) DBNull .Value:( object )e.InnerException; When I do it like this ( it is a part of preparing values for insert ): errorParams[3].Value = e.InnerException no row is added. Is there any way to pass over that check : (e.InnerException== null ) ( and do it easier This the sample exception message for that issue: {System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@InnerException' , which was not supplied. Thanks, Pawe Tag: Is there any method to back up the database to place outside of the local server system?
  - 5
    - Parse Error I am getting the following error whenever we tries to parse any SQL statements in Micorsoft SQL Management Studio 2005 ��.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.�� Please help me. Svas Tag: Is there any method to back up the database to place outside of the local server system?
  - 6
    - Full text prefix search for phrase with noise word? I have data (in SQL Server 2005 SP2) that looks like: ID, Name 1, elbonia university of medical science 2, elb univ med sci 3, elbonia university, med sci 4, elbonia has universities, blah blah blah medium sized scientists And a query that looks like: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ med sci*"') I want it to get 1-3 and not 4, but it just gets 2 and 3. I think the noise word 'of' is throwing if off but if I try: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ of med sci*"') I get nothing! WTF Does a noise word break prefix searches The only message is " (0 row(s) affected)" SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science"') returns 1 but add the * for stemming: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science*"') and it also returns nothing. Thanks for looking! -Andrew Tag: Is there any method to back up the database to place outside of the local server system?
  - 7
    - SQL Server Express error when using NUnit This question is for someone experienced with both SQL Server Express and NUnit. I think the answer might be simple, but I would like to understand what is going on and how to prevent it. I have a test class with a TestFixtureSetUp section that calls a method to drop and re-build my database from scratch, including tables for the Membership and Roles provider. This code works fine. After TestFixtureSetUp runs, I have a test to verify that there are 3 records in the Membership database which were added when the database was re-built. The code looks like this: [ Test ] public void MembershipUsersTest() { int expectedNumberOfUsers = 3; MembershipUserCollection users = System.Web.Security. Membership .GetAllUsers(); Assert .AreEqual(expectedNumberOfUsers, users.Count); } When this test runs along with the TextFixtureSetUp code, I get this error: System.Data.SqlClient.SqlException : Cannot open user default database. Login failed. Login failed for user 'TERRY-COMPUTER\Terry'. However, if I re-run this test but block the TestFixtureSetUp code that re-builds the database, the test passes fine. I'm guessing it's just a simple matter of NUnit somehow locking access to the database when the database is rebuilt, but I am not understanding why this is happening. My code for re-generating the database uses a "using" construct, so the connnections should be closing properly. using ( SqlConnection conn = new SqlConnection (connectionString)) { FileInfo file = new FileInfo (fullScriptPath); string script = file.OpenText().ReadToEnd(); Server server = new Server ( new ServerConnection (conn)); server.ConnectionContext.ExecuteNonQuery(script); } Any insights into why I am getting this error and how I can avoid it and still run my Membership test right after I re-build the db One additional piece of information that might be important. The code for rebuilding the databases uses the following connection string: @"Data Source=.\SQLExpress;Integrated Security=True" Whereas the Membership provider uses the following connection string which has a hard-coded path to the database: " Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=C:\MyApps\MyAppsWeb\App_Data\MyApp.mdf " Tag: Is there any method to back up the database to place outside of the local server system?
  - 8
    - Best practice to copy a lot of data between databases Hi, I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables. Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows: Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database) Some people do some checks to see if the new data in the Test-database is correct If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database. For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up Is my current way of working good practice Thx! Tag: Is there any method to back up the database to place outside of the local server system?
  - 9
    - DTS old style and Dynamic Properties Tasks Im using embedded SS2000 DTS packages in SSIS. These legacy packages use Dynamic Properties Tasks to pick up connection settings from a named ini file. The name of that ini file needs to change occasionally. To date the only way we have of doing this is resetting all the dynamic properties to point at the new ini file. Unfortuntely there are a lot of connections and properties and there is no budget to recreate these packages in SSIS. There must be an easier way ! Does anyone know how to quickly change Dynamic Properties Tasks to look at a new ini Tag: Is there any method to back up the database to place outside of the local server system?
  - 10
    - Help plz - need query to get the following kind of output i want result to be in the required format given below: here is the result column A ---------------- 5 7 10 14 22 30 .... i want output to be in this form with a single sql statement: column A column B ---------------- ---------------- 5 2(difference b/n the current record and the next record) 7 3 10 4 14 8 22 8 30 0 can any one plz post the query which gives the above kind of result.. thnx in advance.. Tag: Is there any method to back up the database to place outside of the local server system?
  - 11
    - RAISERROR does not cause SQL task to fail - why? BlackCatBone wrote: Thanks for the replies. Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio. I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task. I don't think you can have it both ways. If the task fails, you're not going to be able to use it to capture the output parameters. I'm personally surprised that SSIS does not fail the task when you use RAISERROR in your procedure code. I would have assumed that explicitly raising an error would cause the task to fail, but it does not. It's far too late for me to dig into this. Perhaps someone else can shed some light on the "why" of this behavior. With that said, this is the approach that I would recommend if I needed to capture the error information from the stored procedure and have the package respond to the error as well: Remove the RAISERROR from the procedure. Leave the TRY CATCH block in the procedure. Have the Execute SQL task call the procedure and capture the output parameters. Have precedence constraints below the Execute SQL task that follow a "success" path or an "error" path based on the values of the output parameters - likely the error number. It's not elegant, but it should get the job done... Tag: Is there any method to back up the database to place outside of the local server system?
  - 12
    - Moving Average using Select Statement or Cursor based? Hi Kent, I do not understand what you try to tell me. I just wanted to point that this is one of the cases where cursor based solution can outperform set based one. I also wanted to let you know about this document and the possibility to help ourself voting in connect. I had no other intention. Regards, Alejandro Mesa Tag: Is there any method to back up the database to place outside of the local server system?
  - 13
    - Grouping Hi, I have a stored procedure which is returning results in the following format: No.of substances Count 1 29 2 89 3 876 .. .. 15 56 16 89 Now i need to display like this: No.of substances Count % 1 20 2 89 .. .. >=9 8766 Total All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9 How to achieve this Thanks in advance Tag: Is there any method to back up the database to place outside of the local server system?
  - 14
    - Problem with "phantom rows" sadie519590 wrote: Ok, but then how do I break up the line into columns so that I can map them to my table columns Use a script component after that Sure. Or use a derived column and use substrings. Tag: Is there any method to back up the database to place outside of the local server system?
  - 15
    - SQL Express Help Files Where do I find the Help files for SQL Express. When I click on help it says, help is not available. Tag: Is there any method to back up the database to place outside of the local server system?
  - 16
    - Design question - Foreign key indexes A rather elementary design question - is it a good idea to index foreign keys in fact tables I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time Does the cube generate all dimension members then query fact tables for them Does it query fact tables with group by's on participating dimensions I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed. Thanks in advance, John Hennesey Tag: Is there any method to back up the database to place outside of the local server system?

Helen999888

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if we could back up the databases to any place outside of the local server system As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.

Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Re: Is there any method to back up the database to place outside of the local server system?

Jerry Hung

Yes, you can use script

Code Snippet

BACKUP DATABASE [DB_NAME]
TO DISK = '\\FileServer\Backups\file_name.bak'
WITH INIT -- overwrite anything

GO

I think in SQL2005 SP2, you can specify Network Path for backup as well

Otherwise, try Maintainence Plan/Wizard as well (that's what we use)

SSMS -> Management -> create a plan -> Add "Back Up Database Task", and put the network path manually under "Folder" , under "Create a backup file for every database"

Re: Is there any method to back up the database to place outside of the local server system?

TimDBA

You'll need to make sure the service that runs mssql has access to the network drive where you are going to be placing the backup files.

Re: Is there any method to back up the database to place outside of the local server system?

Helen999888

Hi, thank you all very much for your very kind advices and help. It's been very helpful.

With best regards,

Yours sincerely,

Re: Is there any method to back up the database to place outside of the local server system?

Jerry Hung

No problem at all, we are all here to help

don't forget to mark the answer to your question, so the thread is considered "resolved"