How do I backup/restore specific SQL tables?

I would like to backup and restore specific GP tables in SQL. What is the 
best way to do this? Is the best way to use the Import and Export utility in 
SQL?

I would like to backup specific POP tables and be able to restore the POP 
tables if something goes wrong while running some other scripts to fix an 
issue.

So I was planning to backup (export) the specific POP table to a CSV flat 
file using DTS in SQL. Then if need be, restore (import) the POP data in the 
CSV flat file back into the original table. Would this work?

When doing the backup (export) should I only backup the specific damaged 
records within the POP table or should I backup the entire table? The reason 
I ask is when doing the restore (import), if I restore the entire table won't 
that cause duplicate records in the POP table? For example, suppose I have 
two POs, PO001 (good PO) and PO002 (bad PO). My goal is to fix the PO002 
record using some scripts. However, prior to running my script, I want to 
take a backup in case something goes wrong. So should I backup entire table 
(which inclues PO001 & PO002) OR should I just do a select statement to only 
backup PO002? Because say if I need to restore (import) won't the import 
append to data in existing table? So if PO001 is already in the table and if 
I import PO001 in my restore, won't that be duplicate?

Directions on how to backup & restore specific tables in SQL would be 
appreciated very much. Thanks 
0
6/13/2008 10:39:03 PM
greatplains 29623 articles. 6 followers. Follow

3 Replies
626 Views

Similar Articles

[PageSpeed] 6

On Jun 13, 5:39 pm, GPLoveHateRelationship
<GPLoveHateRelations...@discussions.microsoft.com> wrote:
> I would like to backup and restore specific GP tables in SQL. What is the
> best way to do this? Is the best way to use the Import and Export utility in
> SQL?
>
> I would like to backup specific POP tables and be able to restore the POP
> tables if something goes wrong while running some other scripts to fix an
> issue.
>
> So I was planning to backup (export) the specific POP table to a CSV flat
> file using DTS in SQL. Then if need be, restore (import) the POP data in the
> CSV flat file back into the original table. Would this work?
>
> When doing the backup (export) should I only backup the specific damaged
> records within the POP table or should I backup the entire table? The reason
> I ask is when doing the restore (import), if I restore the entire table won't
> that cause duplicate records in the POP table? For example, suppose I have
> two POs, PO001 (good PO) and PO002 (bad PO). My goal is to fix the PO002
> record using some scripts. However, prior to running my script, I want to
> take a backup in case something goes wrong. So should I backup entire table
> (which inclues PO001 & PO002) OR should I just do a select statement to only
> backup PO002? Because say if I need to restore (import) won't the import
> append to data in existing table? So if PO001 is already in the table and if
> I import PO001 in my restore, won't that be duplicate?
>
> Directions on how to backup & restore specific tables in SQL would be
> appreciated very much. Thanks

Generally it is easier to just backup restore the database. But then
again - below are the steps in case you want to try them

Try this on a test database first -
1. Take the backup of relevant tables
-- Taking a Backup
select * into POP10100_Backup from POP10100

2. Delete from POP10100

3. Insert into POP10100(Column Names - All Except the identity/dex row
id) select (Column Names - All Except the identity/dex row id)  from
POP10100_Backup

OR You can also turn identity_insert on and try it.
OR You can use DTS to import into the table - just make sure you
select dex_row_id to ignore on import.

Hope that helps!

Jivtesh
Dynamics Blog : http://www.jivtesh.com/
0
jivtesh (113)
6/14/2008 8:46:13 AM
Just dont take the short cut of dropping the original table and renaming your 
backup.  The Indexes will be wrong!  Follow Jivtesh's instructions exactly.
-- 
Richard L. Whaley
Author / Consultant / MVP
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at 
http://www.AccoladePublications.com



"Jivtesh" wrote:

> On Jun 13, 5:39 pm, GPLoveHateRelationship
> <GPLoveHateRelations...@discussions.microsoft.com> wrote:
> > I would like to backup and restore specific GP tables in SQL. What is the
> > best way to do this? Is the best way to use the Import and Export utility in
> > SQL?
> >
> > I would like to backup specific POP tables and be able to restore the POP
> > tables if something goes wrong while running some other scripts to fix an
> > issue.
> >
> > So I was planning to backup (export) the specific POP table to a CSV flat
> > file using DTS in SQL. Then if need be, restore (import) the POP data in the
> > CSV flat file back into the original table. Would this work?
> >
> > When doing the backup (export) should I only backup the specific damaged
> > records within the POP table or should I backup the entire table? The reason
> > I ask is when doing the restore (import), if I restore the entire table won't
> > that cause duplicate records in the POP table? For example, suppose I have
> > two POs, PO001 (good PO) and PO002 (bad PO). My goal is to fix the PO002
> > record using some scripts. However, prior to running my script, I want to
> > take a backup in case something goes wrong. So should I backup entire table
> > (which inclues PO001 & PO002) OR should I just do a select statement to only
> > backup PO002? Because say if I need to restore (import) won't the import
> > append to data in existing table? So if PO001 is already in the table and if
> > I import PO001 in my restore, won't that be duplicate?
> >
> > Directions on how to backup & restore specific tables in SQL would be
> > appreciated very much. Thanks
> 
> Generally it is easier to just backup restore the database. But then
> again - below are the steps in case you want to try them
> 
> Try this on a test database first -
> 1. Take the backup of relevant tables
> -- Taking a Backup
> select * into POP10100_Backup from POP10100
> 
> 2. Delete from POP10100
> 
> 3. Insert into POP10100(Column Names - All Except the identity/dex row
> id) select (Column Names - All Except the identity/dex row id)  from
> POP10100_Backup
> 
> OR You can also turn identity_insert on and try it.
> OR You can use DTS to import into the table - just make sure you
> select dex_row_id to ignore on import.
> 
> Hope that helps!
> 
> Jivtesh
> Dynamics Blog : http://www.jivtesh.com/
> 
0
info4071 (3005)
6/14/2008 5:35:01 PM
GPLHRelationship,

If you are only interested in backing up the data in those tables, rather 
than the table structure itself, you can use Microsoft SQL Server Integration 
Services (formerly known as DTS) to export the data to a CSV file. 

If you are familiar with neither, you can also resort to old fashioned SQL 
Bulk Copy utility (BCP.EXE) to export your data to a CSV file as follows:

BCP two..sometable OUT somefile.csv /c /t , /r \n /SYourServerName 
/USomeUserID /PSomeUserPassword

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


"GPLoveHateRelationship" wrote:

> I would like to backup and restore specific GP tables in SQL. What is the 
> best way to do this? Is the best way to use the Import and Export utility in 
> SQL?
> 
> I would like to backup specific POP tables and be able to restore the POP 
> tables if something goes wrong while running some other scripts to fix an 
> issue.
> 
> So I was planning to backup (export) the specific POP table to a CSV flat 
> file using DTS in SQL. Then if need be, restore (import) the POP data in the 
> CSV flat file back into the original table. Would this work?
> 
> When doing the backup (export) should I only backup the specific damaged 
> records within the POP table or should I backup the entire table? The reason 
> I ask is when doing the restore (import), if I restore the entire table won't 
> that cause duplicate records in the POP table? For example, suppose I have 
> two POs, PO001 (good PO) and PO002 (bad PO). My goal is to fix the PO002 
> record using some scripts. However, prior to running my script, I want to 
> take a backup in case something goes wrong. So should I backup entire table 
> (which inclues PO001 & PO002) OR should I just do a select statement to only 
> backup PO002? Because say if I need to restore (import) won't the import 
> append to data in existing table? So if PO001 is already in the table and if 
> I import PO001 in my restore, won't that be duplicate?
> 
> Directions on how to backup & restore specific tables in SQL would be 
> appreciated very much. Thanks 
0
MarianoGomez (3440)
6/15/2008 4:07:01 PM
Reply:

Similar Artilces:

Restore Mailboxe
Hi to all is there any way to restore from database a mailbox in exchange2003 that was deleted ? thanks Assuming you have backups look at the recovery storage groups http://www.microsoft.com/technet/prodtechnol/exchange/2003/library/ue2k3rsg.mspx simon "Microsoft" <essam@bk.co.il> wrote in message news:OQOsck1mGHA.4216@TK2MSFTNGP04.phx.gbl... > Hi to all > > is there any way to restore from database a mailbox in exchange2003 that > was deleted ? > > > thanks > ...

Forwarding a specific e-mail to an external e-mail!
The owner of the company would like to forward an e-mail received from a specific customer's e-mail address to his external e-mail - This is the process - Our customer (cust@123.com) sends and e-mail to the owner of the company (owner@company.com). Whenever the owner of the company receives e-mail from our customer cust@123.com, the e-mail should automatically be forward to the owner's external e-mail (owner@yahoo.com) How do I do this? I tried a forwarding rule and it didn't work. Do you have any other suggestions on how to accomplish this? Thank you in advance for you...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Excel / VBA / SQL DB
Anybody done any work with Excel / VBA / SQL DB? Can you give me some pointers on how I could do the following all in one VBA routine: 1) From a cell variable value (ie, user enters a customer code), I query table A and put the data into a worksheet starting from say cell A1. 2) A blank row is created after the last data line in point 1 above. 3) Using the same cell variable, query table B and put the data into the worksheet starting from the row after the blank row in point 2 above. and so on. Like I've said before I work with ASP all day and know about ADO connections, commands and...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Backup Money 2007 to DVD
I can backup Money 2007 H&B via CD using C:\Documents and Settings\Chris\Local Settings\Application Data\Microsoft\CD Burning\MY Money Backup but I want to backup with DVD. How do I do this?????? When exited from Money, use your favorite burning software and burn a DVD from the file? "Chris109" <Chris109@discussions.microsoft.com> wrote in message news:64AEF5D0-105A-4392-BA6D-369A7F31FEF8@microsoft.com... >I can backup Money 2007 H&B via CD using C:\Documents and > Settings\Chris\Local Settings\Application Data\Microsoft\CD Burning\MY > Money > Backup...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

I need help restoring Public Folders
Hi I've been having issues getting Public Folder replication going between 2 Exchange 2003 servers. Prior to doing any work I performed a Backup using Veritas Backup Exec and this completed and verified correctly. I screwed things up and managed to delete some of the public folders on my original server. I'm not 100% sure what I did but it is a mess and the only backup I have is the Exchange backup I created before doing any work. The public folders contain a number of Contact Lists and a very important Event Calendar. I have restored the public folders but when the restore c...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Paper settings change for specific printer
I just installed access 2007 and my application uses reports to print labels. Specifically a Dymo turbo 400 and 30252 address labels. when i was using acess 2003 i specified to use a specific printer and everything was great. and when i copied updated FrontEnds to my networked computers every thing transfered and the labels printed fine however with 07 the "Specifc printer" has a different page size.. i fixed all the reports on my master FE but the changes didnt stay when i copied FE to other computers and im not sure where to go. i installed all the printers from the s...

Online Restore using NT Backup has no edb.chk or edb.log files
I have a single site with four servers running Exchange 5.5 SP4 on NT4 SP6a. I am using an internal 35/70 Compaq DLT. When I back up two servers at the same time using online method, I am missing the edb.log and and edb.chk files when trying to restore the db's. Is there a known issue for this? Thanks, Jim When you make online backup, you are backing up the database content perse, the logs files will be skipped because ntbackup cannot back up open files. I recomend you to adquire a third party backup software with open files and exchange database options, like Veritas to ensu...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Can I set SQL mirroring across WAN?
We have a 100 Mb dedicated DR link to another data center. Want to set up a SQL 2008 database to use mirroring from primary to a standby server at the remote data center. I did a ping test and here is the result. ping -n 100 -l 5120 <target IP> Ping statistics for <target IP> Packets: Sent = 100, Received = 99, Lost = 1 (1% loss), Approximate round trip times in milli-seconds: Minimum = 8ms, Maximum = 19ms, Average = 11ms is the network latency good enough for database mirroring? Does anyone know the min. requirement? Field experience will be fine too....

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

Error while backup SystemState via DPM 2010
Hi.. we backup the SystemState of our Server via DPM 2010. Since we're upgrade from DPM 2007 RTM to DPM 2010RC (now we use DPM 2010 RTM) on the Server DPM is hosted a eventlog error occured every day after the backup is finished: Source: FilterManager, Event ID: 3 "Filter Manager failed to attach to volume '\Device\HarddiskVolume....'. This volume will be unavailable for filtering until a reboot. The final status was 0xc03a001c." Server Version: Windows Server 2008 R2 x64 any ideas? Alex ...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Backup a users mailbox in Exchange 2003
We have recently terminated a couple employees, and I would like to make a copy of their mailboxes. How do I go about doing this? -- Chuck On Mon, 30 Oct 2006 06:36:02 -0800, chb12 <chb12@discussions.microsoft.com> wrote: >We have recently terminated a couple employees, and I would like to make a >copy of their mailboxes. How do I go about doing this? Exmerge is always a good option. What I normally do is open their mailbox on my outlook, then create a PST (name it the users name). Once the PST is created I copy the contents of their mailbox into the PST. From there you ...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...