Copy Data between two databases I have the following situation. An application writes logs into SQL Server 2012 Standard edition database. The incoming data are too big to leave in the operational DB because the application also reads from this DB and it is time critical. So we need to delete the log entries which are older than a week.

But in some cases we need the old data. So my idea is to create a second database from a backup of the original and restore it every week before we delete the data. An already existing external reporting tool is ready to read data from two databases so our problem would be solved.

Is this possible - restoring and attaching data to another DB with the same schema - or do I need to create a new database each week?
There are 2 good options that you can think of implementing based on your comfort level :


Incremental Loads - using SSIS


Pros:


You can schedule SSIS package as and when required.
You can do it for selected tables.
Depending on the frequency of data transfer, the data to be transferred would be less.

Cons:


Would require a bit of learning if you are new to SSIS.


Backup Restore (sample script .. change it as per your needs) :


Pros :


You get a copy of your database as per your schedule.

Cons:


Entire database has to be restored. Requires more disk space.




Your application should use 3 part (dbname.schema.tableName).

Also, make sure that Instant file initialization is enabled to cutdown the restore time.