Best Approach to Copy Data between Databases on same server

Question

Best Approach to Copy Data between Databases on same server

j mangual

Ten Centuries

Points: 1197
More actions
September 11, 2006 at 10:10 am

#90483

I receieved a request to create a copy of a database within the same instance on the same server for reporting purposes. Data for the newdb must be copied every night. Developer(consultant hired to assist us to set up SQL environment) states that performing a snapshot replication is better that using DTS. I'm not familiar with either of these procedures and will like to know this forum opinions. I'm looking for advantages/disadvantages of using one versus the other, including performance issues.
At the long run the consultant will leave and those staying behind will support the environment.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sindhigrl Default port Points: 1448 More actions · Answer 1

My own opinion is that for what you want to accomplish replication might be overkill. We have a reporting database on the same server as production as well as one on another server, and we use a simple backup and restore to achieve this. I have scripted a job that backs up the database and another job that restores it to the test database. It works well.

Thanks!

j mangual Ten Centuries Points: 1197 More actions · Answer 2

However, the restore needs to be on the same isnatnce, same server of the original database using a new name.

sindhigrl Default port Points: 1448 More actions · Answer 3

That's exactly what we do also. I'm just saying that if you don't need replication, I wouldn't use it. If the data doesn't have to be current by the minute, there's no reason to add more complexity. I have a job that backups the prod database to '\\dataserver1\G$\mssql\Backups\LiveDB.bak' and then have another job that restores the prod backup to the reporting db:

RESTORE DATABASE [Reporting]

FROM DISK = '\\dataserver1\G$\mssql\Backups\LiveDB.bak'

WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE ,

MOVE 'data01' TO '\\dataserver1\K$\mssql\Data\reporting.MDF',

MOVE 'log01' TO '\\dataserver1\L$\mssql\Log\reporting.LDF'

Then the second step in my restore job fixes broken logins and adds logins for the reporting db:

Use reporting

exec sp_change_users_login 'auto_fix', N'aolson'

GO

exec sp_change_users_login 'auto_fix', N'psmith'

this works well in our environment, unless someone knows of a better way to do this.

Thanks!

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 4

If you're using SQL Server 2005 I'd consider using database snapshots. I've been experimenting with scheduled created database snapshots for reporting purposes and have found they work surprisingly well.

j mangual Ten Centuries Points: 1197 More actions · Answer 5

j mangual

Ten Centuries

Points: 1197

September 11, 2006 at 11:30 am

#659730

The environment is SQL 2000

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 6

Then I'd go with Anita's technique above which will produce similar results.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

The backup/restore method works, but if it is a large database you will spend a lot of time copying data every day. The reporting database will only be refreshed once a day, so if you need more accurate reporting it is not ideal. If your source database is in Simple recovery mode, this is the best choice because replication and log shipping are ruled out.

Replication would work, and is not very complicated, but does get in your way if you want to make any changes to the source database. Creating the snapshot every night is practically the same as writing a backup. Since you're already creating a backup anyway, you might as well restore that instead.

I would recommend taking a look at log shipping. Schedule a job to do a RESTORE LOG ... WITH STANDBY to the reporting database for all transaction logs created during the day. You don't need Enterprise edition to do this. This method minimizes the amount of data that must be restored. You stated that this is to be run nightly. If that is because the reporting database must be static all day so afternoon reports match morning reports, then restore all the logs once each night. If you would like the reporting database to be more up to date, you can schedule the log shipping to run after every log backup with very little performance impact during the day. Log shipping every 15 minutes (or even more frequently) is perfectly reasonable, a backup/restore every 15 minutes is not.

DTS would not be a terrible option, depending on what kind of package you write and how much activity there is on your server at night when you run it. It will probably be slower and put more of a load on your server if it is busy 24/7.

Brian Cohen Newbie Points: 1 More actions · Answer 8

I have the similar situation and I chose to use the Backup/Restore method. DTS was too slow becasue it verifies every single object in the db. Replication likewise was just too detailed and slow. All my user wanted was the database copied so he could use it in the morning for testing.

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 9

If your database is huge the backup and restore is going to take some time.But the same is the case of "snapshot" Replication. DTS is not the way to do it.

Have u considered Log Shipping? That might have the advantage of Replication and Backup/Restore method.

Thanks

Sreejith

j mangual Ten Centuries Points: 1197 More actions · Answer 10

Thank You all for your suggestions. I proceed to create a job to run after the nightly backup to restore the newdatabase using the old database backup file. Then I also remove the roles and users since it is only a reporting db and no updates to the data should be taking place. I'm wating for the developer with the script to create role + grants to the tables the end users will be accessing and I will add it to the job.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 11

One side effect of log shipping is that the destination database is read-only. You can't change roles or permissions, but they can't make any updates. But if you need completely different security in the two databases, your backup/restore method with security scripting is probably the best method.