Once in 15 days i need to move the entire data from DB_live to DB_Archive using SQL Server 2005

Question

Once in 15 days i need to move the entire data from DB_live to DB_Archive using SQL Server 2005

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
July 11, 2008 at 4:30 am

#129912

Hi,
IN SQL Server 2005, i have a database in production, i want the Database which is in production to move the entire database automatically once in 15 days, where in for the first time i might have created a duplicate database of the original.
What i mean to say is,
I have a DB called DB_live in production server.(connected to application)
I have a DB Called DB_Archive in the same server (but not connected with live application)
now once in 15 days i need to move the entire data from DB_live to DB_Archive.
Please help me..

Viewing 13 posts - 1 through 12 (of 12 total)

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

Mike John SSCertifiable Points: 7186 More actions · Answer 1

I would simply use backup and restore. Assuming you take regular (say daily) production db backups you could schedule a job to restore the lastbackup to your dev db at the required intervals.

Mike John

MannySingh SSChampion Points: 12465 More actions · Answer 2

I will Mike's suggestion: nothing better n Easy than Backup and Restore.

something like this:

SINCE THIS IS THE SAME bOX AND SAME PATH TO DATA FILES, I WOULD SUGGEST THE FOLLOWING::

RESTORE FILELISTONLY FROM DISK = '.BAK PATH AND FILENAME'

GET THE LOGICAL FILE NAMES THEN USE THEM IN THE BELOW QUERY

RESTORE DATABASE [DATABASENAME] FROM DISK = '.BAK PATH AND FILENAME'

WITH REPLACE,

MOVE 'Logical Data FileName' to 'C:\etc...\NewData File Name.MDF',

MOVE 'Logical Log file Name' to 'C:\etc....\New Log Filename .LDF'

I would recommend you change the Logical DB Name to Something else after the Restore.

ALTER DATABASE [DATABASENAME]

MODIFY FILE (NAME='LogicalDataFileName', NEWNAME='NewLogicalFileName')

Maninder
www.dbanation.com

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 3

Hi,

Thanks for the input. So taking the backup and restoring will be the best way.

Through Maintenance plan i am aware of taking a backup, but how to restore the same automatically, please suggest.

With Regards

MannySingh SSChampion Points: 12465 More actions · Answer 4

You will have to list down the Logical names the first time.

then you can put the Names in a Automated Scripts in the JOBS, and WOLA! you are ready to go.

Maybe Someone has a better suggestion than this..

Maninder
www.dbanation.com

J SSCertifiable Points: 7822 More actions · Answer 5

When I hear Archive it makes me think you do not want to overwite the data in the Archive database, you just want to add to it. If you restore a full backup over the Archive databse it will overwrite all the data in the database. Just want to make sure this is not the case before you accidently lose some data.

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 6

Hi,

Thanks a lot for correcting me, what i mean to say was i need a exact replica of the live DB to be put on with some other name.

With Regards

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

Then I would definitely use the advice of the earlier post to use backup and restore. Use the restore filelistonly command to get the file information that you will need and then script out the restore appropriately. Be sure to use a different database name so as not to overwrite the online production database. I'm sure I am stating the obvious with the different name. 😀

You can get script examples from Books Online under the restore heading.

Hope this helps!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 8

dakshinamurthy-655138

SSCertifiable

Points: 5172

July 12, 2008 at 12:25 am

#841949

Hi,

Ok thanks a lot. It was really usefull

With Regards

BAD-DBA-DAB SSCommitted Points: 1856 More actions · Answer 9

hi pal,

Just create a package (SSIS) to move the data from production DB to archive DB. Schedule that package to execute once in 15 days. But ensure that you truncate the tables in archive DB before you execute the package on schedule(otherwise the entire data will get appended & hence duplicated). You could use T-SQL to truncate the data in the tables & schedule that too to execute before the other scheduled SSIS package. Hope this would have been useful to you.

Regards,

Rajesh

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 10

Hi,

Ok i will try to make use of SSIS, can we make use of copy database wizard, do the same.

With Regards

alimcitp SSC Eights! Points: 930 More actions · Answer 11

I think the best way to do this job is to set up the Log Shipping.

Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate

steveb. SSC-Forever Points: 46733 More actions · Answer 12

As others have said, I think Backup and Restore is the best option.

Log Shipping would add an unnecessary overhead to your production server if the only reason you are using it is to do 15 day refreshes of your database..