April 21, 2020 at 8:17 pm
Hi,
2. While trying to restore, is there a script available for me to restore all databases from the backup location rather than go through one by one manually. Web searching gave plenty of responses, but it still seems like searching for needle in haystack.
PS. Coming from Oracle world, learning SS 🙂
Thanks, Ram
April 21, 2020 at 9:44 pm
Network drive letters are mapped per user, not per machine. So mapping the drive with "net use" will map the drive for you not the SQL Server Service account. You will want (ie need) to use the UNC path for the backups instead of drive letters UNLESS the drive is mapped to the machine (your SAN admins should know how to do that).
For question 2, you need to tell SQL what you want to restore and how you want to restore it. There are no magic shortcuts for that to restore everything automatically for you. What you can do is build up TSQL script that will go through the backup location and give you a set of restore scripts. As you have all of the files (all 50 of them I assume) in a single location, there are multiple ways you can handle it. You could use powershell to put them into CSV (or command prompt to dump them to a text file) then use excel to build up a query for it (one of the things I use excel for). your new SQL instance has no idea about any of those backup files so scripting it from TSQL will be a bit more tricky, and I think the excel approach is likely the easiest. If you don't have excel, libreoffice or any other "spreadsheet" tool will work. Chances are though, since this is a new server, you are going to need a bunch of specific syntax for each backup file (such as where the database and log will reside on disk) that will be different for each of those 50 databases.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 21, 2020 at 10:56 pm
So mapping the drive with "net use" will map the drive for you not the SQL Server Service account.
You're absolutely correct about "net use" assigning according to the current user. There's a nifty little trick you can do, though. Run the "net use" command through xp_CmdShell and it'll work as expected unless something else is horribly wrong.
I do agree that using UNC paths is a much better idea though.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2020 at 2:35 pm
Yep; xp_cmdshell would solve that, but you'd need to enable it and some auditors don't like having that enabled.
UNC paths are likely going to be the safest and most reliable option.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 22, 2020 at 4:57 pm
It's amazing to me how the visceral fear from the /90s and early 2Ks about xp_CmdShell still continues. Disabling xp_CmdShell does nothing for security except provide a 3ms speedbump in attack software if someone "gets in" with sysadmin privs. I have an hour long lecture on why such a supposed security "Best Practice" is actually a bad thing. There's a ton of people that think they're "safe" if they have it turned off and don't take the steps necessary to keep an intruder out. It's just crazy.
Having it disabled also removes a lot of capability from DBAs. And, people forget that any sysadmin can turn it on and they also forget that even with it enable, ONLY sysadmins and special stored procedures can actually use it... well, unless someone was stupid enough to grant non-system admin people permission to use it directly.
Like I said... rule about keeping xp_CmdShell disabled is both useless and crazy.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2020 at 5:06 pm
I do agree with you Jeff, I have xp_cmdshell enabled in a lot of places. You just have to be careful with it. It is very easy to turn it on and not have things configured correctly and allow more access than you intended OR allow operations to be performed AS a service account which may have more permissions than it should on a server.
The way I deal with xp_cmdshell though is to turn it on in the stored procedure that needs it and turn it off when I'm done. Kind of like even though I am an admin on my machine, I don't just run everything with admin permissions. Turn the feature on when it is needed and turn it off when I'm done.
I'm not saying don't use it, I'm just saying be careful with it. I've had some auditors ask why it is on and make notes on security audits related to xp_cmdshell being turned on. Mind you, I've seen auditors flag some pretty dumb things. "login from previous domain still exists on SQL instance"... yeah, it's still there, but the domain is removed. We just forgot to clean up the old logins after making new ones and migrating to the new domain. Kept them around to make sure we didn't miss any permissions and got audited prior to cleaning it. Not like they could log in, but got flagged for that and need to fix it before next years audit. And yet they can miss some more obvious ones like "sa account is enabled".
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply