I'm trying to save some time on a repeated task I keep getting asked to do.
We have an internal application we'll call "App".
This App has a database called "AppLive"
We have a "Test" instance of the same application, which uses the Database "AppTesting". This is so we can perform tests etc without affecting our live data.
When the users want to start playing with the Test Instance of the application, I am asked to "import the live data into the test database". They need the most current set of live data from our AppLive Database imported into the AppTesting database.
My current process is this:
Take the most recently nightly full backup of the live database. Delete the test database "Restore" the AppLive backup file into a new database called AppTesting (same name as deleted database).
This gives us the most recent nightly data into the Test database.
The problem with this is that the database is getting huge. It's about 11GB - and the backup files are remotely stored on a NAS drive.
So I have to wait 45 minutes to transfer the database backup file onto the SQL server, and then begin the import. Which again takes an absolute age.
My ideal solution to this would be:
Continue to do my nightly full backups of AppLive database (for our normal backup requirements) and for an incremental backup to me made in addition to this - so that when I have to import live data into our Test database I don't have to play with the huge 11GB file. Ideally I'd like to be able to say to MSSQL management studio "please update AppTest db with all new data from AppLive" - I don't want this scheduled though, the Test data has to stay static until we're requested to update it with the new data.
I hope this is clear enough and that someone is able to point me in the right direction.