Automate nightly database data transfer between SQL environments on different servers

[awaria]

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

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[awaria]

(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.

 

[fredericofonseca]

for those sizes I would just do backup/restore.

backup with compression onto 4 files for performance.

example

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-statistics-maintenance.html

- this is a MUST)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886