Identifying last backup and restoring to QA

Question

Identifying last backup and restoring to QA

ramana3327

SSCoach

Points: 19722
More actions
February 22, 2016 at 4:22 pm

#316243

Hi,
We have Production server and backup job is running daily 9 pm for all dbs to the network path.
I have a requirement to restore daily one of the database in QA from Production using the latest backup.
The backp has timestamp on that how can I achieve this without manually?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

ramana3327 (2/22/2016)
Hi,
We have Production server and backup job is running daily 9 pm for all dbs to the network path.
I have a requirement to restore daily one of the database in QA from Production using the latest backup.
The backp has timestamp on that how can I achieve this without manually?

Are all the user names, linked servers, synonyms, passthrough views, 3 and 4 part naming conventions, etc, etc exactly the same and do you have any PII or other sensitive information that needs to be obfuscated or encrypted? Don't just say no to the PII stuff... make bloody sure you don't before you even think about doing this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 2

I have to map some users and give read permissions in QA env which doesn't exists in the Prod.

I verified the linked server. I tested the connection. Looks they are working fine. We don't need to encrypt any data.

Is there any script to find that they are using any 4 partname in the sp or views

ramana3327 SSCoach Points: 19722 More actions · Answer 3

I am getting some issue with linked servers.

I can see the linked server is created and when I tested it is working fine.

But when I tried to run the select statement from linked server it failed. The error is showing doesn't contain the table.

When I verified the linked server I can't able to see the database under the catalogs.

How can I add that database to that existing linked server

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

ramana3327 (2/23/2016)
I am getting some issue with linked servers.
I can see the linked server is created and when I tested it is working fine.
But when I tried to run the select statement from linked server it failed. The error is showing doesn't contain the table.
When I verified the linked server I can't able to see the database under the catalogs.
How can I add that database to that existing linked server

Find out which login has been assigned for the link server on the remote end and give that user the necessary privs on the database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 5

This what happened.

We are using the option that Be made using this security Context option.

After the refresh I missed to map the refreshed db to that login. After mapping the login to the refreshed db, I can able to see the all dbs under catalogs for that linked server.

ramana3327 SSCoach Points: 19722 More actions · Answer 6

After the refresh, I need to map the couple of logins as users.

My main concern is how to identify and restore the last full backup automatically?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

ramana3327 (2/23/2016)
After the refresh, I need to map the couple of logins as users.
My main concern is how to identify and restore the last full backup automatically?

Fixing the logins can be done with SQL as a part of your script.

The backup history is stored in msdb and can be queried. See https://msdn.microsoft.com/en-us/library/ms188062.aspx.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions