Assistance - script to make copy of production db and replace Test Db on same SQL Server 2008

Question

Assistance - script to make copy of production db and replace Test Db on same SQL Server 2008

Mile Higher Than Sea Level

SSCarpal Tunnel

Points: 4828
More actions
May 10, 2012 at 10:05 am

#255804

Need help or direction on how to develop a script or automated process for the following:
A production Database A1 exist. On the same server, a database used for testing exist (A1T)
The data file names are exactly the same where the Test DB has a "T" at the end.
I can manually, full backup the Production DB and then manually restore from device that copy to the Test Database - manually editing the file location to add the "T" at the end.
Is there a way to automate this process?
And, what would be the best way? T-sql or some other method?
My front-end client is MS Access 2010 and it can run T-SQL scripts for example.
Any pointers, suggestion or advice would be greatly appreciated.
Production --> copy -->replace Test DB - DB & file names same but have T at end e.g. DBT

Viewing 5 posts - 1 through 4 (of 4 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Yep, it's very common thing to do. It can be done entirely in T-SQL and scheduled as an agent job.

All you need to do is backup the database to the file system using the BACKUP syntax, then restore it with the RESTORE syntax. You use the MOVE clause to specify different filenames. You can even use the backup/restore GUI in SSMS to generate your scripts.

ankur_libra Hall of Fame Points: 3999 More actions · Answer 2

Hi,

Please do take care of the security settings (Logins, database roles, permissions) need to preserved on the newly refershed database. Restoring backup doesnot include permissions and security settings..

With regards,

Ankur

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 3

Thank you both for your excellent advice and tips.

I may post my results later to garner additional suggestions.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

ankur_libra (5/10/2012)
Please do take care of the security settings (Logins, database roles, permissions) need to preserved on the newly refershed database. Restoring backup doesnot include permissions and security settings..

...Depends. Database permissions exist within the database, it's the links to server-wide logins that become orphaned when moving servers. In this case, it's a restore back to the same server, so not sure there's much to worry about...