0

I'm struggling to find a suitable solution to this. I have a fairly large SQL Server 2005 database containing 250+ tables (many with key constraints) and a whole bunch of data.

I need to essentially copy all of these tables and the data and the constraints exactly from one database to another. I'm basically duplicating database A (SQL Server 2005) to produce an exact copy database B (SQL Server 2008) on a different domain so we end up with two completely identical databases running in parallel, each with their own identical data to begin with.

I just need to get the tables and the data intact from A to B. To use the log-in credentials for database B and it should work.

All the operations on SQL Server 2008 (currently in use)

Having two cases

  1. I've also tried right clicking database A in Management Studio and going to Tasks > Generate scripts. But when I do this and run the SQL scripts on database B I get a whole load of errors to do with foreign keys etc. As it imports the content. It seems like it's doing the right thing, but can't handle the different keys/relationships.

  2. If I copied the content without this then it will copied successfully have no 'IDENTITY' and no 'Constriants', but I need whole things Identity as well as constraints.

Case (1):

Error 0xc0202009: Data Flow Task 30: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "

Violation of PRIMARY KEY constraint 'PK_TFL'. Cannot insert duplicate key in object 'dbo.TFEATURE_LOC'.".

Error 0xc0209029: Data Flow Task 30: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (343)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (343)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error 0xc0047022: Data Flow Task 30: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 148 - TFEATURE_LOC" (330) failed with error code 0xC0209029 while processing input "Destination Input" (343). 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. There may be error messages posted before this with more information about the failure.

Error 0xc02020c4: Data Flow Task 30: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error 0xc0047038: Data Flow Task 30: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 146 - TEXT_US_CA_MX_CITY" (109) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error 0xc02020c4: Data Flow Task 30: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error 0xc0047038: Data Flow Task 30: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 147 - TEXT_US_CA_ZIP_CODE" (172) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error 0xc02020c4: Data Flow Task 30: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error 0xc0047038: Data Flow Task 30: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 148 - TFEATURE_LOC" (240) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Improve this question
5
  • if you can to connect from mssql2008 to 2005 then do it and try to restore from database to an empty database in mssql2008( I remember that how I did with my old databases) / or you can instal mssql2012 and to restore the mssql2005 backup Commented Dec 30, 2013 at 11:21
  • thnx @HellBaby , but i have to do with 2008 Commented Dec 30, 2013 at 11:25
  • then try 1st alternative. restoring .bak files from old version of sql servers doesn't work(until mssql2012) except generated scripts. So you need to connect to 2005 from the 2008 one and to try restoring from that database Commented Dec 30, 2013 at 11:28
  • @HellBaby: this statement is utterly wrong - you CAN restore an older version (2005) onto a newer (2008) server without ANY problems! - you just cannot do the other way around Commented Dec 30, 2013 at 11:32
  • Thnx... but when I do that it is creating a .bak file on 2005 server, and I have no permission to create anything on that server. How will I create .bak on my local server 2008 Commented Dec 30, 2013 at 11:39

3 Answers 3

Reset to default
3

You can use backup and restore. Right click on the SQL Server 2005 database and select Tasks --> Backup and then add a destination (file path where you want to save it). It will create a .bak file. Then go to SQL Server 2008 and right and select restore and select the .bak file.

Details on how to restore below:

http://www.howtogeek.com/50354/restoring-a-sql-database-backup-using-sql-server-management-studio/

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@HellBaby: it WILL work because you CAN go from an older version (2005) to a newer (2008) - you just cannot go the other way (form 2008 down to 2005)
Yes you can go from old version to new.
Thnx... but when I used to do that it is creating a .bak file on 2005 server, and I have no permission to create anything on that server. How will I create .bak on my local server.
sure... that's why I never succeed to do it deploying my company website databases from 2005 to 2008 except the direct connection or generated script
@gopaldangi Look at this... You can back up DB to a shared location... howtogeek.com/51788/… .You will have to make sure the account under which SQL Server service runs has access to network share.
|
0

Using management studio the procedure can be done as follows

  1. right click on the Databases container within object explorer
  2. from context menu select Restore database
  3. Specify To Database as either a new or existing database
  4. Specify Source for restore as from device
  5. Select Backup media as File
  6. Click the Add button and browse to the location of the BAK file

refer

You'll need to specify the WITH REPLACE option to overwrite the existing adventure_second database with a backup taken from a different database.

Click option menu and tick Overwrite the existing database(With replace)

Improve this answer

1 Comment

pls tell me that can we make a Backup of database which reside at remote Server 2005(no admin rights) to our local Server 2008
0

I use a number of different methods, depending entirely on what the application is. Here's a list for you to check out:

  • Use Backup and restore
    • Make sure you name the new database and files correctly.
    • This method sometimes creates complications with database users etc, but it's reliable enough as long as you know what you're doing. I generally only do this for dev / test systems, rather than production.
    • You can do this via MSSQLSMS or via T-SQL
  • Use SSIS 'Transfer Database Task' to copy the database. You can set up a package pretty quickly in SSIS.
  • Use the "Copy Database" command in MSSQLSMS. I'm not sure if this is available in SQL 2005 or 2008, as I only have 2012 installed on my machine at the moment:

Copy Database command

  • Use 'Generate Scripts'
    • You can generate scripts with various options by going to "Advanced" on the final page of the "Generate and Publish Scripts" wizard. Here, you can select "Schema And Data" for the Types of data to script.

enter image description here

Improve this answer

10 Comments

thnkx...I am unable to execute generated script it is giving error while open in SSMS, the error is like -"the template specified cannot be found. please check that the full path is
I have no idea what that error is about, I've never seen it before. If your database already exists, you might want to also choose "DROP Objects" in the advanced generation options too.
Okey.. is it because of script file size(approx 9GB),I am unable to change file formate as well in .txt,calc,impress,ms word all.
OK, if you have no filesystem access to the SQL 2005 box, then the ONLY option available to you is to use the Generate Scripts option to generate the schema ONLY (this should generate a considerably smaller script file than your 9GB one), run that on the new box (make sure the script also creates the database and the tables don't exist) then use SSIS to get the data from the SQL 2005 box to the SQL 2008 box. This would be greatly simplified if you did this via backup + restore, and had access to the file system on both boxes.
thnks so much...... but when I do this way,I got many issues of foreign key and primary key dependency while copying data using SSIS over existing database which is schema only .
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.