INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Automate nightly database data transfer between SQL environments on different servers

Automate nightly database data transfer between SQL environments on different servers

Automate nightly database data transfer between SQL environments on different servers

(OP)
I am looking for recommendations for a strategy to automate a nightly database update of a source DB from one server environment to a destination server environment.

For reporting purpose, mgmt wants a nightly data refresh of a "reporting" server database(s)
so users will develop Crystal reports against the reporting server, not the production database server.

replication? (transactional?, log shipping, database copy, scripting. . .

Appreciate your input on strategy to accomplish this in most efficient and automated solution that requires no manual intervention.

Thanks, awaria

RE: Automate nightly database data transfer between SQL environments on different servers

what is the size of database?
what is the size of the biggest tables? (anything with more than 20-30 million rows or more than 20GB table size)
how many tables in total?

and what is the nightly window size that you can have to do this copy?

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Automate nightly database data transfer between SQL environments on different servers

(OP)
(2) Databases, one abut 40GB and one about 5GB
I have about a 2 1/2 to 3 hour maintenance window available
The largest table has about 6M records.


RE: Automate nightly database data transfer between SQL environments on different servers

for those sizes I would just do backup/restore.

backup with compression onto 4 files for performance.

example

CODE

USE [master]
BACKUP DATABASE [AdventureWorksDW2008R2] TO  
DISK = N'C:\AdventureWorksDW2008R2-pt1.bak',  
DISK = N'C:\AdventureWorksDW2008R2-pt2.bak',  
DISK = N'C:\AdventureWorksDW2008R2-pt3.bak',  
DISK = N'C:\AdventureWorksDW2008R2-pt4.bak' 
WITH  COPY_ONLY, FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO


USE [master]
RESTORE DATABASE [AdventureWorksDW2008R2] FROM  
DISK = N'C:\AdventureWorksDW2008R2-pt1.bak',  
DISK = N'C:\AdventureWorksDW2008R2-pt2.bak',  
DISK = N'C:\AdventureWorksDW2008R2-pt3.bak',  
DISK = N'C:\AdventureWorksDW2008R2-pt4.bak'  
WITH  FILE = 1,  
MOVE N'AdventureWorksDW2008R2_Data' TO N'C:\sql_server_data\AdventureWorksDW2008R2_Data1.mdf',  
MOVE N'AdventureWorksDW2008R2_Log' TO N'C:\sql_server_data\AdventureWorksDW2008R2_Log1.LDF',  
NOUNLOAD,  REPLACE,  STATS = 10

GO 

once restore is done do cleaning of logins/permissions as required.

Ideally the backup should be done after index/statistics maintenance is performed (you do do it don't you? if not see https://ola.hallengren.com/sql-server-index-and-st... - this is a MUST)

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close