• bubby - Monday, February 12, 2018 11:53 AM

    Requirements:
    - need to have data available more than 1 year
    - want to create new server,
    - want to export the data copies to this new server using ....please suggest best practice
    Help is highly appreciated,

    Thanks,

    You want to create a new server and want to have more data available.

    Because of your requirements i can see you need:
    -A new server (obviously)
    -More space for storing the data (obviously)

    For your third and last requirement i suggest you use if you dont want to use built in features of SQL Server like replication or log shipping. 
    1)SQL server integration services that updates all your tables or x tables x times per day, this is good because you can transform however you want your data to your report server.
    2)I don't know if you backup your production database every night and if you do just restore every morning your night backup on your report server, if you dont want to do this see 1st choice.