t-SQL Solution to backup and restore

Question

t-SQL Solution to backup and restore

river1

SSC-Insane

Points: 24300
More actions
November 7, 2017 at 12:47 am

#409749

Dear all,
I was requested by my company to create a solution that is able to backup a database from PROD into a destination folder and create another one that is able to restore that database from that same folder into below environments like (dev or test).
Probably this as already been developed by someone. Do you know if there is any free solution already developed with this cahracteristics?
Thank you

Viewing 15 posts - 1 through 15 (of 41 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

river1 - Tuesday, November 7, 2017 12:47 AM
Dear all,
I was requested by my company to create a solution that is able to backup a database from PROD into a destination folder and create another one that is able to restore that database from that same folder into below environments like (dev or test).
Probably this as already been developed by someone. Do you know if there is any free solution already developed with this cahracteristics?
Thank you

Look up one of two solutions, Ola Holengren or Minion Backup. Minion has a free and a paid version. Ola's scripts are just free. I prefer Minion.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

As for the restore part, yes, it's been done before, but it's only a few lines of code. Just query the backupset and backupmediafamily tables on the source server to get the most recent backup, then restore it on the destination server. You'll need to choose a way of making sure there are no users in the destination database before you restore over it.

John

river1 SSC-Insane Points: 24300 More actions · Answer 3

John Mitchell-245523 - Tuesday, November 7, 2017 2:23 AM
As for the restore part, yes, it's been done before, but it's only a few lines of code. Just query the backupset and backupmediafamily tables on the source server to get the most recent backup, then restore it on the destination server. You'll need to choose a way of making sure there are no users in the destination database before you restore over it.
John

Thank you John for your reply. You say "Just query the backupset and backupmediafamily tables on the source server to get the most recent backup" there is No way to just see this by lloking at the files inside a directory?

What I want is:

Restore database DBName from \\servername\G$\ but I am want just to restore the most recent full backup. Can I look at the files instead of qury the server it self? If only in server, can you please let me know what is the table? thank you

river1 SSC-Insane Points: 24300 More actions · Answer 4

What You are suggesting is that I query in the PROD server a table or a view that have all the infromation about the backups and then take that one. But can I look instead to the files? is there a way to see what is the most recent file inside a folder? If not, when you refer to query the PROD , what is the table or view? thank you

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

river1 - Wednesday, November 8, 2017 1:56 AM
What You are suggesting is that I query in the PROD server a table or a view that have all the infromation about the backups and then take that one. But can I look instead to the files? is there a way to see what is the most recent file inside a folder? If not, when you refer to query the PROD , what is the table or view? thank you

Yes, but bear in mind that this method is not as reliable. What if the most recent backup of the database was made to a different folder? What if someone made a backup of the database to a file with a misleading name - for example a full backup to the file MyDatabase.TRN? If you decide to do this anyway, you can use xp_cmdshell to run a dir command and dump the results into a temp table. Or you can use Powershell or even SSIS.

when you refer to query the PROD , what is the table or view?

"Just query the backupset and backupmediafamily tables on the source server to get the most recent backup". They're in msdb.

John

river1 SSC-Insane Points: 24300 More actions · Answer 6

I think I will use the method you suggested: backupset and backupmediafamily
But why two tables? can't I find all the infromation in just one table?

river1 SSC-Insane Points: 24300 More actions · Answer 7

Let me also put another question. To be able to query the PROD server from DEV and Test servers I need to have a linked server or make an openquery . There is no other way, correct?

like:

select * from [PRODServerName].msdb.dbo.backupset

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

river1 - Wednesday, November 8, 2017 2:57 AM
But why two tables? can't I find all the infromation in just one table?

Because MSDB is designed in such a way that you need to look at two tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

river1 - Wednesday, November 8, 2017 3:18 AM
Let me also put another question. To be able to query the PROD server from DEV and Test servers I need to have a linked server or make an openquery . There is no other way, correct?

OPENROWSET
Powershell
SSIS
Regularly bcp the information out from the prod server to a location that the dev and test servers can access

John

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

river1 - Tuesday, November 7, 2017 12:47 AM
Dear all,
I was requested by my company to create a solution that is able to backup a database from PROD into a destination folder and create another one that is able to restore that database from that same folder into below environments like (dev or test).
Probably this as already been developed by someone. Do you know if there is any free solution already developed with this cahracteristics?
Thank you

If there's any PII in the production databases, then it takes a whole lot more than a simple backup and restore. You MUST protect PII.

--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)

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 11

are you using a 3th party backup solution or native SQLserver backups ?

Plug in into your existing backup infrastructure !
Doing that you can use it to test your DRP with regards to the database restore(s) needed. ( timing / completeness / accessibility / usability / .... )

As a shortcut you may think only restores of full backups would be sufficient and it may even be a starting point, but don't be fooled, you will rather quick actually need PIT-restores.

We have put in place naming conventions for folders, files, locations, ... to facilitate such usage for all our instances and DEV-instances service accounts are only granted read access to the non-dev folders at the safe-zone.

We use powershell + SMO to assemble the requested files and perform the restores ( including checkdb, and authorisation sync at the target instance plus additional scripts for PII if needed )

If you want a system that actually makes a backup on the fly and restores that to your target location, have a look at dbatools.io

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

river1 SSC-Insane Points: 24300 More actions · Answer 12

Hello Guys,

Than you very much for your answers.

Now before restore is done, I need to guaranty that no connections are in place.

I was thinking about: ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This WITH ROLLBACK IMMEDIATE will kill all the connections, correct?

Thank you

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 13

river1 - Wednesday, November 8, 2017 7:32 AM
Hello Guys,
Than you very much for your answers.
Now before restore is done, I need to guaranty that no connections are in place.
I was thinking about: ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This WITH ROLLBACK IMMEDIATE will kill all the connections, correct?
Thank you

Correct

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

There's nothing to stop you trying these things out... but yes, it will.

John