Yes... lots and lots of TRN files. We do nightly FULL backups, no DIFs, and have TRN files every 15-30 minutes depending on the database and the instance.
I'm a bit unconventional because I've proven to myself that certain best practices aren't and a lot of them are based on fear and old wives tales from the past. For example, I know that having xp_CmdShell turned off does nothing to improve security. It only gives people that may not know better the nice warm fuzzies. Unless someone has sysadmin privs, they can't use it when it's enabled and if it's disabled, only people with sysadmin privs can enable it. That means if an attacker can get in with sysadmin privs, they can enable it and use it with no problem. The key is to keep the bad guys from breaking in with sysadmin privs and to NOT give non-sysadmin users the privs to execute it directly. I have an hour long presentation on the subject which ends with how DBAs and stored procedures can use it safely along with some education on what DOS Injection is. There are some really cool things you can do with it. For example, the error reporting generated in error files by either BULK INSERT or BCP absolutely suck. Doing a little trick with a combination of xp_CmdShell and SQLCmd actually captures useful error reporting from a BULK INSERT where, normally, that information would only be available to a human that's actively watching the process. Ironically, the use of xp_CmdShell has increased security quite a bit when it comes to ETL. Because Bulk Insert relies on the user's privs and such things are frequently run manually by certain people or other systems (WebMethods, for example), we were either having to give users/other systems extraordinary privs on the source file system or give them write privs at the OS level so they could copy files to the SQL Server box and we all know the problems that brings with memory usage not to mention using valuable disk space on the SQL Server for such junk. It really got crazy when they started doing exports to files. Now, they just execute a well written stored procedure that has the privs to use xp_CmdShell to get the job done. The users/systems actually have no privs to do any of it directly. They can't even do a DIR to see the file names directly.
That being said, I capture the results of a DIR command in a table where life is made easy and I also tickle the registry (again, using what's available in T-SQL) to get the default paths for restores if the database has been dropped or is new to the server. I also auto-magically repoint synonyms in the code based on the environment (Dev, QA, UAT, Staging, etc) and the name of the database, which changes depending on the environment it's in and reestablish the correct users and roles for databases that existed during the restore.
There are actually 348 lines of code to do all of that but half of that is embedded documentation in the form of comments and blank lines for the sake of readability.
Shifting gears a bit, what do you consider a line of code to be in PowerShell? I'm looking at your great examples (thanks for posting those) and, while each is technically only a single line because of the CrLf locations, in SQL Server each "piped" filter/command you have on those lines would be a separate line in SQL Server if you use the formatting conventions that I use. I'm thinking that if I were to single-line many of the simple SELECTs that I use and get rid of the blank lines that I use for formatting and drop all of the comments, I might actually have quite a bit less than 200 lines of code.
If someone absolutely loathes the use of xp_CmdShell, either a simple batch file or the use of xp_DirTree can help and, most certainly, the proper call to MSDB of the source server will do the trick for such restores. I'm still a bit confused as to why someone would allow the use of PowerShell with extraordinary privs and not xp_CmdShell with similar privs but more tightly controlled because general users can't actually use it directly nor do they need to be provided with elevated privs to any box at the OS level.
I certainly can't and won't speak for anyone never mind everyone but I've simply just not needed PowerShell to do the extraordinary things that can be done with PowerShell... and I still pass all the audits we have to go through every bloody year.
is pronounced "ree-bar
" and is a "Modenism
" for R
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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)