Empty database and reload from an image from another server

Question

Empty database and reload from an image from another server

madferdy2009

SSC Rookie

Points: 46
More actions
August 12, 2013 at 3:31 am

#395281

Good morning
I needed to make a stored procedure for maintaining a database SQLServer 2008. The idea is to automate the deletion of records in the tables and load table by table from another database. You have something to guide me?
regards

Viewing 4 posts - 1 through 4 (of 4 total)

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

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 1

madferdy2009 (8/12/2013)
Good morning
I needed to make a stored procedure for maintaining a database SQLServer 2008. The idea is to automate the deletion of records in the tables and load table by table from another database. You have something to guide me?
regards

So you want to dump all the data in target database and replace it with the data from the source database? Three questions: 1) Are the databases on different instances? 2) Do the databases have the same schema, and 3) do you need to keep objects (like stored procedures and functions) in the target database that don't exist in the source database. If the answers are 1) Yes, 2) Yes, and 3) No, you may want to consider backing up the source database and restoring it on the target server rather than moving the data with T-SQL.

If you can't do the backup/restore method and you can access the source database from the target database (either because they're on the same instance or as a linked server), you could write a stored procedure that selects the names of the tables you want to process from sys.tables into a cursor and then write dynamic SQL that first truncates the current table and then inserts the data from the source table.

Jason Wolfkill

madferdy2009 SSC Rookie Points: 46 More actions · Answer 2

I explain very briefly.

The situation is as follows:

I have to migrate a database SQL SERVER 2000 (DB2000) to SQL Server 2008 (DB2008).

The DB2008 DB2000 be a replica, just configured and optimized for 2008. I have to mantenter objects: stored procedures, functions, views, etc..

I have to do testing, and when you give the nod ... delete test records DB2008, DB2000 and load stop in DB2008 DB2000.

The idea is to do what you propose in case 3. Instead of using sys.tables it dynamically with a table that refers to the tables you want to load.

Gretting

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 3

madferdy2009 (8/13/2013)
I explain very briefly.
The situation is as follows:
I have to migrate a database SQL SERVER 2000 (DB2000) to SQL Server 2008 (DB2008).
The DB2008 DB2000 be a replica, just configured and optimized for 2008. I have to mantenter objects: stored procedures, functions, views, etc..
I have to do testing, and when you give the nod ... delete test records DB2008, DB2000 and load stop in DB2008 DB2000.
The idea is to do what you propose in case 3. Instead of using sys.tables it dynamically with a table that refers to the tables you want to load.
Gretting

Yes, you can create a table that holds the names of the tables you want to process, then populate a cursor from that table and create dynamic SQL that executes the TRUNCATE and INSERT . . . SELECT statements for each table. Do you need more specific help?

Jason Wolfkill