2

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?

  • It sounds to me like you need to design and implement a proper pruning plan(not just wipe all data). from there use replication to store any data you actually need to keep to a reporting instance. If you want to only have the data active as and when you need it its a bit more problematic as yes you'd need to do a restore for each week, you may want to look at BCP for the tables you actually want data from and use that to export / import data – Ste Bov Nov 3 '15 at 9:35
  • 1
    I'd like to understand why you think rows in the log table cause processing against other tables to slow down? What size, exactly, is "too big?" – Michael Green Nov 3 '15 at 10:03
  • A simple solution would be to write a stored procedure which transfers the data and removes it from the original table; then create a Job to execute the stored procedure once a week. You can create an SSIS task to do it as well, but I tend to go to SSIS when the task is more complex than basic copying. – Mr.Brownstone May 6 '16 at 0:59
  • Why not just have a LogOld table in the existing database and move data over? – paparazzo Aug 15 '16 at 16:43
1

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.

0

When you perform a restore you have to specify the database name the backup will be restored as. If a DB of that name exists it will be overwritten and all data previously in it will be lost.

If you only need to transfer rows from the log table there are various ways to do this. If the two DBs can see one another over your network a simple INSERT statement using three-part or four-part name will suffice. Otherwise the bcp command line utility writes a table to file. It can also load the file to the destination, or a staging table if that is preferred.

The "proper" solution, to my mind, is an ETL job. SSIS is the tool of choice in the world of SQL Server. It will not be difficult to learn how to do a data copy like this.

There are other features which will automatically move data from one DB to another, such as mirroring, replication and log shipping. They are solutions for high availability, however, and are excessive for this scenario. You would have to ensure writes but not deletes are synchronised, for example.

0

Other than backup/restore, I would suggest that you do partitions on your log table (based on loading date), and each partition can be on a different physical file. The advantage here are:

  1. You keep your old data, and your new data is loaded into a different file, thus minimizing the potential I/O workload on the old data file (if your partition file can be in a different disk i/o system, for example)
  2. You can easily drop your old data by dropping a partition containing the oldest data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.