When I do a RESTORE command, can I use a variable for the TO location?

DECLARE @dataPath NVARCHAR(512), @logPath NVARCHAR(512);
SELECT @dataPath = SERVERPROPERTY('InstanceDefaultDataPath') + "dbName.mdf",
       @logPath  = SERVERPROPERTY('InstanceDefaultLogPath') + "dbName_log.ldf";
RESTORE DATABASE [dbName] FROM DISK = N'backupFilepath' WITH FILE = 1, MOVE N'dbName' TO @dataPath,  MOVE N'dbName_log' TO @logPath,  NOUNLOAD,  REPLACE,  STATS = 5

only with dynamic sql afaik

Okay, well I'm doing it in powershell for automation so I'll just make two calls in the same connection. String building is obviously fast so it'll basically roundtrip A -> B

Cool, thanks

Good morning

Morning

@jcolebrand @sp_BlitzErik We're successfully using this in production:

  RESTORE DATABASE SyncImport
  FROM
    DISK = @BackupName
  WITH
    FILE = 1,
    MOVE @dataname TO @datapath,
    MOVE @logname TO @logpath,
    NOUNLOAD,
    REPLACE,
    STATS = 5
  ;

@AndriyM Nice

According to the docs it's string literals only. docs.microsoft.com/en-us/sql/t-sql/statements/…

I had several things to send in so I wasn't really worried about doing it dynamically. I'm going to test it in the form I have tomorrow but I'll ask my DBA and Data Architect if they wanna go that way or the way I have it. They don't own the process, I do. But I still want their input.

I use dynamic SQL , also so does sp_DatabaseRestore

Here, just to make y'alls eyes bleed:

The SP that does this is declaring a table variable, reading the names and paths from RESTORE FILELISTONLY, modifying the paths and invoking the fellow above

It's awesome

So like I said, I'm testing this functionality tomorrow as it's 1am and the primary test case for one setup worked. I'm trying to get it working for case#2

well, no, I'm modifying what was working for #2, and I'm doing it via proof before I do it in fact

Because I obviously hate myself

I understand how this works: NAME = N'$databaseName', but what about this: NAME = N'$($databaseName)_log'? Should there be two $? I understand what needs to be done, I'm only a little confused about the syntax

In powershell, when you're inside "here" the replace syntax does some funny stuff.

If I had "$databaseName_log" it would look for the literal variable $databaseName_log which isn't my name

Yes, that I understand

So instead, I'm forcing it to evaluate the variable and inject it into the script. $($variable)

I could build the name like this instead "{0}_log" -f $databaseName

That's more C#ish syntax for format string

Did I answer the question or was that too vague?

I think in Unix/Linux it would be just $(databaseName)_log, although my memory may be failing me there

Yes

That's a thing about the PowerShell syntax

It won't evaluate like that

And if it does, that's news to me lol

Well, I've got my answer, thanks!

haha, you're welcome

Until I've read an official (and probably more detailed) explanation/description, I think I will be happy to go with this one: the $(...) syntax is specifically designed to delimit a variable name where there's a need for that (like your scenario), and the variable itself still needs to be referenced with its own $. But I'm guessing the $(...) will also let you have more than just a variable delimited like that, you can probably put an expression there.

You absolutely can

I didn't want to go into the intricacies of how it can be used

And you could probably use it with the earlier instance, like this: NAME = N'$($databaseName)', except there was no need to do that in that case

Correct sir

@AndriyM What happens if you have multiple backup files?

@hot2use Nothing, because I don't :)

But I guess I'll have to think of something when that starts happening

I'm currently in the process of developing something similar for automatic restores from a certain path. (Think mass restore of database from multiple files)

Oh, sorry, did you mean when there are multiple backup files for the same DB? Because that is the case here.

Yes, like MyDB_Backup_File_20180403_083200_01.bak, MyDB_Backup_File_20180403_083200_02.bak, MyDB_Backup_File_20180403_083200_03.bak for the same one database at the same time.

I forgot to tell, this SP is also invoking xp_cmdshell (yes, I know) to read the name of the first backup in the directory before doing everything else. They are all easily sortable, so the "first" works very well for us.

Other processes decide how often to call this SP

I do something like this (so far):

Hmm, is my code being displayed?

I can't see any code

Unable to insert code currently. Stackexchange must thinking I'm trying to inject code.

gist.github.com/JohnKNess/1140468c900b094ebb7ad9d7ac8bf37b

@AndriyM There you go

It's not finished yet. Currently WIP.

Is there something missing from the 3rd line? (insert into ...)

Nope. Works just fine.

We are doing just EXECUTE xp_cmdshell N'dir "MyDir\*.bak" /b /on /s'; to read the first name. And I'm alone to blame for the whole xp_cmdshell thing, even though I didn't like to do it like that. But I just didn't think of something else instead, like xp_dirtree.

The insert into... references the exec sys.xp_dirtree.. on the next line :-)

You can execute it against your C:\Windows` directory for a test drive. :-)

@hot2use It's the lack of semicolons that confuses people like 'cube :)

@AndriyM Ah, I see.

We are also using xp_cmdshell once again at the end of that SP, to rename the processed backup, so that the next file name is read when the SP is called subsequently

I suspect there may be a dedicated xp_nnn for that operation too

@hot2use I don't have a C:\Windows drive ;)

@ypercubeᵀᴹ strike

@AndriyM Not sure

@ypercubeᵀᴹ You should get one!

Can't beat a good c:\windows directory

I find myself behaving as though I'm addicted to C:\Windows. With every new PC I buy, I try and make sure I get me some C:\Windows

@PaulWhite I may have one but 'll have to restart and boot to windows to access it.

Ah well that is acceptable

The 2nd laptop died, I have a phone call (date) with a technician to see if it can be revived

Perhaps it lacked sufficient c:\windows

nah, it's hardware related

@ypercubeᵀᴹ You could get a /home/ypercube/.wine/drive_c

hackers gonna hack

@AndriyM Isn't that a bad idea if the file is part of the/a backup set?

Or do you rename them back to the original?

@hot2use We change the extension

Or, more accurately, we add an extension, so that it becomes nnn.bak.old

It remains in the same dir to be later archived (manual process so far)

@TomV I have /media/userxxxx/Windows7_OS/

You could probably automate that just by thinking the word 'PowerShell'

@ypercubeᵀᴹ we use someone similar to this: amazon.com/AGPtek-Drive-Adapter-Converter-External/dp/… at least you can easily check and copy disk contents.

@PaulWhite It probably works only when you are one with this Shell of Power

@AndriyM By Example – Undocumented SQL Stored Procedures – Drive Folder and File Manipulation

morning and evening

@hot2use Don't forget, ensure SQL Server service account has permissions (if you do it properly and remove local admin)

@gbn correct

@hot2use Doesn't cover our needs but unfortunately I can't find anything that does.

@AndriyM I'm new here, so there's probably a good reason you're not doing this that I don't understand, but I've been querying msdb.dbo.backupset to get the file locations of the most recent full backup, then all the log backups that occurred after that, and then iterating over the results to do a fairly automated point in time DB restore.

Just thought I'd throw that out there as a potentially simpler (or more reliable) approach than going off the file name.

Morning evening

@jadarnel27 That's probably how it should be normally. I'm not sure if that applies to our situation, though. What I described above was part of our custom-made data replication process. Basically, backups are received from another server, each is restored to/as a staging DB and then the data is imported into the main DB.

Ah, so you'd need to query msdb on the databases / servers from which you received the backups - which maybe your custom replication process doesn't have access to?

Nifty solution you've come up with, regardless.

@JackDouglas is this the usual view on mobile?

How can I press Run?

@McNets yes it doesn't work on mobile at all really

you can't edit the fields either — the ACE editor doesn't seem to work

cya folks

For a transaction log file, is it weird that I see high io stalls per read (disk latency) during hourly log backups, and low disk latency during CHECKDB runs? It seems weird to me. High and low being relative here (20-120ms vs 1-2ms).

CHECKDB is reading way more bytes (1.5 GB) than the log backups (20 MB), so my current theory is that has something to do with it.

Would that be a good question on the actual site? I'm not really having a problem, as they both complete in a reasonable amount of time. I'm more just curious about the odd-seeming difference in numbers.

@jadarnel27 checkdb doesn't really do much with the log file

Assuming you are checking and not repairing

@TomV Oh, really? Hm, maybe my assumption about what thing is reading my whole log file at midnight is incorrect. Time to look at the job schedule again.

Something is reading almost the whole log file around midnight, at least according to sys.dm_io_virtual_file_stats.

@jadarnel27 reads or writes at midnight?

@TomV Reads.

Looking at the job schedule, it's got to be CHECKDB. We're using Ola's [dbo].[DatabaseIntegrityCheck] sproc to run it. The output is being logged to a table, and there's no indication that errors were encountered or repairs were done.

Or at least, I think it's got to be CHECKDB. Maybe there's some other rogue, non Agent job process doing this nonsense at midnight.

Ah you could be right there is a log reading part

Never noticed it was that intensive

sqlskills.com/blogs/paul/…

> Once DBCC CHECKDB has read through all the allocated pages in the database, it reads through all the active transaction log – from the LSN of the ‘BEGIN TRAN’ log record of the oldest transaction that is active at the time the database scan started, to the LSN at the time the database scan stops.

@TomV Looking at that epic post, I think that paragraph is describing how CHECKDB worked in SQL 2000 and earlier.

A bit farther down, it indicates that this is used for getting a consistent view of the database: docs.microsoft.com/en-us/sql/t-sql/database-console-commands/…

I don't really know anything about snapshots. I don't see why they would read the log file, except maybe to perform recovery on the snapshot, which doesn't seem like it would read the whole log.

Yeah I only skimmed it on mobile. That seems a lot more reasonable and might explain my initial gut feeling

Database-ing is hard.

@jadarnel27 No, the servers don't even see each other. In short, it was one database at first but some remote locations started having issues connecting to the app server network (this project is using Citrix app servers). Eventually a decision was made to set up another DB server for one such location to allow them to work comfortably, and have both servers synchronise with each other. My boss was pretty determined to go with a custom replication solution, so here we are.

@AndriyM Aha! That makes sense.

@TomV Looks like Database Snapshots are not possible on databases that contain a MEMORY_OPTIMIZED_DATA filegroup (which mine does). So maybe CHECKDB falls back to using the initial method you pointed out from Paul Randal's blog.

@jadarnel27 that seems a reasonable question to ask on main with the details you have so far

Cool. Thanks for your thoughts (on the question, and its suitability for the main site), @TomV.

@jadarnel27 could you replicate the problem on a test system you think? Maybe if you could exclude any app activity and other jobs so people don't start guessing about other jobs in the comment section

That could make a really good question actually

I may not be the one with the answers but I'm not shy to offer a bounty for a good question if it doesn't get attention

@TomV I'll play around with it this evening on a VM, see if I can get the same results.

Good idea.

@jadarnel27 my first one this year

Congrats! Ahead of your metrics from last year I hope?

@jadarnel27 yeah I average 3 good ideas per year. Could make it four this year if I'm lucky

'thinking it was a good idea' is another metric I score a lot higher on

Haha it's good to have goals.

So, follow up to my SSRS issue yesterday. If I set "Do not timeout report" on, the report downloads in ~15 minutes. If I set the timeout to two hours, it just spins and fails after two hours. WTF?

Previously the timeout was set to 30 minutes. It would spin and fail after 30 minutes.

@Forrest Which ones? :-o

Hello to all!

One question:

SELECT-INTO works with a SELECT which returns two or more rows?

SELECT a
  INTO lv_a
  FROM table
 WHERE ...

(Oracle 11g)

how is lv_a defined?

you might need to use bulk insert or something. been a while

@JoeObbish It's a local variable: lv_a VARCHAR2(60);

I only need one value... Or the first value.

make the query return the first row only and there should be no problem

@JoeObbish fetch only one row

WHERE ROWNUM < 2;

What is better?

WHERE ROWNUM < 2;

WHERE ROWNUM = 1;

WHERE ROWNUM <= 1;

If it needs to be one row, and the first row at that, WHERE ROWNUM = 1 expresses the intent better than any other option.