SQL Restore or Replication? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Restore or Replication?

Author

Topic

Noelia
Starting Member

4 Posts

Posted - 2009-05-04 : 17:12:41

Hi All,

I'm creating a Reporting Database/ Staging database to reduce the load of reports on our Production Enviroment, all servers are SQl 2005 SP2

I created a copy of the production database on a different server and on top of that i created some additional FACT tables that allow me to create summary reports.

I want to be able to refresh the main tables every night but we may need to run a refresh during the day at month end processing, and i do not want to delete the additional FACT tables that i created

I was using a backup/restore approched but it's not working as i'm
deleting the additional tables that i created.

Our Production application does not allow me to configure any SQL Server replication/mirroring/log shipping apps on the production server. So i'm looking at an application that will do replication or a partial restore , have you guys know of a software that will solve the issue, it's better to do a replication or a restore?
Can i do replication from a backup ?

I'll appreciate any feedback that you can give me !!

Thanks!

Noelia

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-04 : 17:16:52

Whether or not you use replication or a restore depends on how current the data needs to be in the reporting database. We use transactional replication as we need pretty current data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-04 : 17:17:31

What do you mean the production application doesn't allow you to configure those things? The production application should have no knowledge of those things. Those are done by a DBA in Management Studio or similar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Noelia
Starting Member

4 Posts

Posted - 2009-05-05 : 09:40:02

Our contract with our ERP provider states that we can not modify the database on any shape or form , if we do ,we lose our maintenance and support, so we're not allow to store any store procedures or even create indexes so i'm pretty sure that configuring SQL replication or mirroring is not allow, that's why i'm looking for an alternative solution

Data should be refresh every day at night.

Thanks!

Noelia

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 12:46:05

You should use backup/restore method. This might help you automate it: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

I highly doubt that replication, log shipping, or database mirroring violates your contract since they do not add stored procedures, indexes, or anything to the source database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Noelia
Starting Member

4 Posts

Posted - 2009-05-05 : 15:06:18

Hi,

Thabks for the advise, if i do a backup and restore can i keep the added tables that i have on my staging database?? how can i do that?

Thanks!

Noelia

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 15:15:43

You'd need to write a script so that the script can be run after the restore is complete. You'd run the script as another job step in your restore job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Noelia
Starting Member

4 Posts

Posted - 2009-05-05 : 15:35:26

this is what i'm currently doing but i don't want to recreate the fac_tables every day after the restore.. i want to keep that data that i have and just add some row to it..

Thanks!

Noelia

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-05 : 17:12:08

But why would it be a problem to recreate them if it is entirely scripted? All other solutions won't be as efficient as what I have proposed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources