February 19, 2017 at 10:14 pm
Hi all!
This has been a problem sice my first SQL DB's in 2005.
When i want trestore from device, it's really a matter of luck about the file explorer opens immideately, or it takes very, very long time.
Just now, I have time to ask for help AND go down and find a cup of coffee.
Is there any solution?
Best regards
Edvard Korsbæk
Microsoft SQL Server Management Studio 13.0.16106.4
Microsoft Analysis Services Client Tools 13.0.1700.441
Microsoft Data Access Components (MDAC) 10.0.14393.0
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.14393.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.14393
February 20, 2017 at 3:27 am
Use T-SQL and find the latest full-D and differentail -I and list of log -L and restore one by one. GUI may take some time.
SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.has_bulk_logged_data,b.is_copy_only,f.mirror
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name='test'
--AND B.type='L'
ORDER BY b.backup_finish_date DESC
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 20, 2017 at 11:54 am
GUIs can be problematic. As mentioned above the only reliably efficient way to perform a lot of Tasks is to use TSQL. It takes time but produces the required results and (in your case) is likely to produce the fastest results.
February 20, 2017 at 2:06 pm
really - i do not think, that TSQL is an option.
What i get is an database.bak as a download, and imports it as a restore.
And it's when i click 'Select file', it takes 5 to 15 minutes on the bad days.
On the good days, it's a matter of seconds.
I really do not understand the difference, and I do not understand the time for an simple API call.
Best regards
Edvard Korsbæk
February 20, 2017 at 2:12 pm
Edvard Korsbæk - Monday, February 20, 2017 2:06 PMreally - i do not think, that TSQL is an option.What i get is an database.bak as a download, and imports it as a restore.
And it's when i click 'Select file', it takes 5 to 15 minutes on the bad days.
On the good days, it's a matter of seconds.I really do not understand the difference, and I do not understand the time for an simple API call.
Best regards
Edvard Korsbæk
Where is the device you are restoring from? Is it a local drive on the server?
Sue
February 20, 2017 at 2:26 pm
Edvard Korsbæk - Monday, February 20, 2017 2:06 PMreally - i do not think, that TSQL is an option.What i get is an database.bak as a download, and imports it as a restore.
And it's when i click 'Select file', it takes 5 to 15 minutes on the bad days.
On the good days, it's a matter of seconds.I really do not understand the difference, and I do not understand the time for an simple API call.
Best regards
Edvard Korsbæk
I have to respectfully disagree when you say TSQL is not an option. It is the perfect option.
1. Every backup and restore action can be scripted and saved as a parameterised stored procedure that you can Change the database name for on each execution
2. GUIs are rarely (if ever) better than scripted Statements. If you don't believe me run a complex profiler trace in the GUI, the same as a server-side trace and compare processor and memory usage for both.
3. With a script you reduce the margin for human error during the restore because you are doing nothing more than running a script that is tested and configured appropriately. How often during a GUI Task have you clicked something incorrectly and had to repeat the whole procedure again
That is just three examples that sprang immediately to mind......
February 20, 2017 at 7:23 pm
Sue_H - Monday, February 20, 2017 2:11 PMEdvard Korsbæk - Monday, February 20, 2017 2:06 PMreally - i do not think, that TSQL is an option.What i get is an database.bak as a download, and imports it as a restore.
And it's when i click 'Select file', it takes 5 to 15 minutes on the bad days.
On the good days, it's a matter of seconds.I really do not understand the difference, and I do not understand the time for an simple API call.
Best regards
Edvard Korsbæk
Where is the device you are restoring from? Is it a local drive on the server?
Sue
Hi
Forgive my missing knowledge.
yes, the devices I am restoring from is on a local derive.
That could be : D:\bbbackup_2016\RC\december\easyplan_drift_2016-12-01.bak
Which should restore dbo.easyplan_drift.
And that can be done in TSQL?
Of cause i could do it as a two step process:
Copy to a given place and name on local drive - i.e. to D:\Backup\backup.bak
run a stored procedure with name of the DB to be restored as parameter.
I found the syntax in:
https://msdn.microsoft.com/en-us/library/ms186858.aspx
Thanks!
Made my life easier!
Best regards
Edvard Korsbæk
February 20, 2017 at 8:52 pm
Edvard Korsbæk - Monday, February 20, 2017 7:23 PMHi
Forgive my missing knowledge.yes, the devices I am restoring from is on a local derive.
That could be : D:\bbbackup_2016\RC\december\easyplan_drift_2016-12-01.bak
Which should restore dbo.easyplan_drift.
And that can be done in TSQL?
Of cause i could do it as a two step process:
Copy to a given place and name on local drive - i.e. to D:\Backup\backup.bak
run a stored procedure with name of the DB to be restored as parameter.I found the syntax in:
https://msdn.microsoft.com/en-us/library/ms186858.aspx
Thanks!
Made my life easier!
Best regards
Edvard Korsbæk
Yes that can be done in T-SQL. And often it is easy and can be better than using the other way. It's just needing to get used to doing it. Then you feel better about restoring using T-SQL.
The example and that article you found are good. Have you tried to restore following that? You may want to look at the part that starts with:
E. Copying a database using BACKUP and RESTORE
In that example, they first have the backup and you can skip that one.
The next step which is RESTORE FILELISTONLY is so that you can restore the files to the correct location. So if you had the location like you wrote, it would be something like:
RESTORE FILELISTONLY
FROM DISK = 'D:\bbbackup_2016\RC\december\easyplan_drift_2016-12-01.bak'
That will show you the logical names and then the physical names which is where each of the files go. So then when you restore, an example of what it might look like in T-SQL would be:
RESTORE DATABASE easyplan_drift
FROM DISK = 'D:\bbbackup_2016\RC\december\easyplan_drift_2016-12-01.bak'
WITH MOVE 'easyplan_drift_LogicalFileName for data file' to 'D:\YourPathTo\Data\YourDatabaseName.mdf',
MOVE 'easyplan_drift LogicalFileName for log' to 'D:\YourPathTo\Data\YourDatabaseName.mdf'
It won't be exactly like that as you need to get the logical file names from the RESTORE FILELISTONLY and then use the with move so the files go to the correct directory. You are just typing the same thing as the other way of restoring. When you have to go across the network, it can be slow if you have connectivity issues or a not so good network. You can do it that way but that might be what was making it slow for you. I'm guessing that might be what was happening for you with the network being slow. It's good to know this way of doing it.
Sue
February 20, 2017 at 9:34 pm
Have you tried - Not yet. Its 5:32 AM here in Denmark!
I need to find some Keywwords and how to use them:
WITH REPLACE i think.
And how to be sure, it does not backup the tail first.
But, yes, this was a new world to me.
Best regards
Edvard
February 20, 2017 at 10:02 pm
This was removed by the editor as SPAM
February 21, 2017 at 1:04 am
Its restoring now - Thats good!
I will have to make it into a stored procedure.
RESTORE FILELISTONLY
FROM DISK = 'D:\BBBACKUP_2017\RC\FEBRUAR\easyplan-drift-170216-after.bak'
Is the first part, and i can of cause replace 'D:\BBBACKUP_2017\RC\FEBRUAR\easyplan-drift-170216-after.bak' with an variable.
Then i need to put the two different logicalnames from this query in a variable - How to?
Best regards
Edvard Korsbæk
February 21, 2017 at 3:08 am
Edvard Korsbæk - Tuesday, February 21, 2017 1:04 AMThen i need to put the two different logicalnames from this query in a variable - How to?
Create a temp table with the same columns as RESTORE FILELISTONLY, then insert the results into the table. You can then query the table to get the results you want into the variables.
John
February 21, 2017 at 5:05 am
I am a beginner, sorry!
The table skuld be declared as
DECLARE @FillistOnlyTable TABLE (LogicalName VARCHAR(255),PkysicalName VARCHAR(255),.....)
But how iI do insert from the result of
RESTORE FILELISTONLY
FROM DISK = 'D:\BBBACKUP_2017\RC\FEBRUAR\easyplan-drift-170216-after.bak'
to @FillistOnlyTable - My different trials with SELECT has not worked.
Best regards
Edvard Korsbæk
February 21, 2017 at 5:08 am
INSERT INTO @FillistOnlyTable
RESTORE FILELISTONLY
FROM DISK = 'D:\BBBACKUP_2017\RC\FEBRUAR\easyplan-drift-170216-after.bak'
John
February 21, 2017 at 5:14 am
John Mitchell-245523 - Tuesday, February 21, 2017 5:08 AMINSERT INTO @FillistOnlyTable
RESTORE FILELISTONLY
FROM DISK = 'D:\BBBACKUP_2017\RC\FEBRUAR\easyplan-drift-170216-after.bak'John
That was Easy!
Do you have an easy way to declare the @FileListOnlyTable (Something like script as...)
But anyway, Thanks!
Edvard
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply