<div>
Have you considered partition switching?<br />
<br />
Maybe see this previous answer: <a href="https://www.experts-exchange.com/questions/28297008/Table-Partition-switch-in-and-out.html" rel="ugc">https://www.experts-exchange.com/questions/28297008/Table-Partition-switch-in-and-out.html</a>
</div>


<div>
and this one: <a href="https://www.experts-exchange.com/questions/28412426/script-rotate-automatically-sql-server-partition.html" rel="ugc">https://www.experts-exchange.com/questions/28412426/script-rotate-automatically-sql-server-partition.html</a><br />
<br />
Basically, you use the ALTER TABLE SWITCH statement.<br />
<br />
Or am I missing the point of the question?
</div>


<div>
ScottPletcher- The Software vendor setup the database 2years ago and no one has touched it since. About a year ago we hired a DBA and his manager told him not to touch the database (you break it, you bought it mentality).<br />
<br />
Phillip Burton - Altering the production tables is not an option.
</div>


<div>
OK, that provides some background, but it doesn't answer these qs:<br />
<br />
Is your existing sales table <i>clustered</i> on sales date?<br />
<br />
If not, can you create a nonclustered index on sales date?<br />
<br />
If you can't even add a nonclustered index, I don't know how you're reasonably quickly going to pull each day's data to archive it as it reaches the threshold date, 6 mos or whatever it is.
</div>


<div>
At least you can use the partition switching to import the new data into the (read only) BI database, which will speed to importing up.<br />
<br />
As for the export, if you have to use kid gloves and go carefully, so be it - Scott's answer for that bit is a good one.
</div>


<div>
<blockquote>
we hired a DBA and his manager told him not to touch the database (you break it, you bought it mentality).
</blockquote>
The worst nightmare for a DBA, but I've been there and did that already. He can't change the schema but he can touch the data, right?<br />
I personally like the partition idea but if you can't do that then I recommend to implement a <a href="http://msdn.microsoft.com/library/ms151832.aspx" rel="ugc">Snapshot Replication</a>. Mind that this solution is not good for very large databases since kills the performance. Do you want to backup all data or some tables only?
</div>


<div>
Vitor Montalvão - The business can't decide what data is important at this point so they want the entire Database backed up.
</div>


<div>
Well, the business can't decide what data is important and a DBA can't touch the database. So, you are running out of options here.<br />
How big is the database?
</div>


<div>
&gt;&gt; At least you can use the partition switching to import the new data into the (read only) BI database, which will speed to importing up. &lt;&lt;<br />
<br />
I don't see how that would even be possible. &nbsp;Partition switches must be within the same filegroup. &nbsp;How, then, could you possibly do partition switching between different dbs??
</div>


<div>
Please correct me if I'm wrong, but:<br />
<br />
1. In Source DB, create another table (Table 2) in Source DB to switch into,<br />
2. Partition Switch into Table 2 in Source DB.<br />
3. Use SSIS to export that into the Target DB into Table 2.<br />
4. Partition Switch into Table in Target DB.<br />
<br />
Obviously, not in this case if he can't touch the Source DB.
</div>


<div>
Because if it's a partition per day, then it may make queries quicker?
</div>


<div>
<div class="content wysiwyg-content">
1.5TB MSSQL2k8r2 with 2years of sales data. &nbsp;<br />
<br />
We want to do a one time back up and restore of the entire database to a (read only) BI database. Then maintain only 6 months of data in our production environment, with data older than 6 months being archived in a (read only) BI database. <br />
<br />
How would you recommend to setup the daily differential updates for new entries from Production db to BI db, only backing up the Inserts and updates and not the delete statements. Then delete the copied data from production db.
</div>
</div>


1.5TB MSSQL2k8r2 with 2years of sales data.  

We want to do a one time back up and restore of the entire database to a (read only) BI database. Then maintain only 6 months of data in our production environment, with data older than 6 months being archived in a (read only) BI database. 

How would you recommend to setup the daily differential updates for new entries from Production db to BI db, only backing up the Inserts and updates and not the delete statements. Then delete the copied data from production db.

BI Archive

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.