Post reply

Table backup in SQL Server 2005

  • July 27, 2011 at 6:35 am

    #245035

    Hi,

    I have a table in SQL Server 2005, I have to take this table native backup and have to send to the customer so that she can restore this table native backup on her existing database. I dont know how can I take single table native backup and how will she can restore this single table native backup on her existing database ?

    Thanks in advance.

  • July 27, 2011 at 6:37 am

    #1359165

    Can you tell us the table size and number of records ?

    Cheers,
    - Win.

    " Have a great day "

  • July 27, 2011 at 6:42 am

    #1359173

    You can use Import-Export Wizard, I think...

    Or maybe export to a Flat File...and then import back on the customer's side...

  • July 27, 2011 at 6:45 am

    #1359177

    BCP out.

  • July 27, 2011 at 6:46 am

    #1359178

    Table have 33 million rows.

  • July 27, 2011 at 6:48 am

    #1359184

    beejug1983 (7/27/2011)

    Table have 33 million rows.

    BCP OUT!

    Or do select into [2nd db].dbo.tablename

    Backup that db, have them do the reverse. I still think BCP is the faster and simpler way to go!

  • July 27, 2011 at 6:48 am

    #1359183

    Yes, export / import the data will be fine for you, as said above, and easy too...

    Else there are other ways as well.

    - Win.

    Cheers,
    - Win.

    " Have a great day "

  • July 27, 2011 at 6:52 am

    #1359189

    User does not want to create another database. And if it possible with BCP can you please brief it, I never used this utility. How can I use this BCP to backup of single table and then restore on another database ?

  • July 27, 2011 at 6:55 am

    #1359191

    From : http://msdn.microsoft.com/en-us/library/ms162802.aspx

    bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -T -c

    BCP in is pretty much just as easy.

  • July 27, 2011 at 7:18 am

    #1359220

    hi,

    Below error is occurring while executing bcp -

    C:\>bcp export1.dbo.tab1 out new.dat -T -c

    SQLState = 08001, NativeError = 17

    Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or acc

    SQLState = 01000, NativeError = 2

    Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

  • July 27, 2011 at 7:22 am

    #1359228

    Did you include the switch and specify the server?

    What exact syntax did you specify?

    Refer to the following link.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25809

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 27, 2011 at 7:24 am

    #1359235

    Here's a sample of what I have in production :

    exec master..xp_cmdshell 'bcp "SELECT CHAR(34) + GL + CHAR(34), CHAR(34) + dtMatrix.Departement + CHAR(34), CHAR(34) + dtMatrix.Etablissement + CHAR(34), CASE WHEN SUM(Montant) > 0 THEN SUM(Montant) ELSE NULL END AS Debit, CASE WHEN SUM(Montant) < 0 THEN SUM(Montant) * -1 ELSE NULL END AS Credit FROM ##GL LEFT OUTER JOIN (SELECT ''100RDOD SS'' AS Code, ''40-R ET D'' AS

    ...

    queryout \\fordiafs\public\ceridian\output.csv -T -c -t,'

    It's all on 1 line in the code

  • July 27, 2011 at 7:45 am

    #1359254

    after taking backup of single table in a file how can I restore that file in a database ?

  • July 27, 2011 at 7:48 am

    #1359263

    BCP IN.

    When do you plan to read the article I sent you which contains all the info and exemples you can ever need?

  • July 27, 2011 at 8:16 am

    #1359291

    Thank you for your documents.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply