Sfrgttk

What are substitutions for coconut in curry?

Is there a term for accumulated dirt on the outside of your hands and feet?

Calculate the frequency of characters in a string

Is honey really a supersaturated solution? Does heating to un-crystalize redissolve it or melt it?

What is the term when voters “dishonestly” choose something that they do not want to choose?

Should I use acronyms in dialogues before telling the readers what it stands for in fiction?

What does Deadpool mean by "left the house in that shirt"?

Help rendering a complicated sum/product formula

How does 取材で訪れた integrate into this sentence?

Is there a hypothetical scenario that would make Earth uninhabitable for humans, but not for (the majority of) other animals?

I got the following comment from a reputed math journal. What does it mean?

Why are there no stars visible in cislunar space?

How to define limit operations in general topological spaces? Are nets able to do this?

Is it insecure to send a password in a `curl` command?

Print a physical multiplication table

Suggestions on how to spend Shaabath (constructively) alone

Wrapping homogeneous Python objects

Why is indicated airspeed rather than ground speed used during the takeoff roll?

Optimising a list searching algorithm

Practical application of matrices and determinants

Pronounciation of the combination "st" in spanish accents

Bash - pair each line of file

Unfrosted light bulb

Maths symbols and unicode-math input inside siunitx commands

Restore user db from UNC

Does the SQL Server Backup Database Command also backup views from a Database?Create database before restore multiple filesRestore databases from prod to test server with data over X daysBackup SQL Server database on UNC path using SAI can backup my SQL Server 2008 R2 but can't restore from the very same folderAutomate SQL Server restore from Prod to DevHow do I recover from a failed SQL Restore?Double Concurrent Restore from one backup for 3 node AG?How to create a differential backup considering the base the previous differential backup in SQL Server?How to create SQL Agent job to automatically restore a database from latest backups?

0

I need to create a job that restores all user databases from UNC to my SQL Server.

1. All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.




2. What is the right way to do this? Build steps to all databases separately?

I need a suggestion on how to build it.

sql-server backup restore

share|improve this question

edited May 6 '15 at 18:49

Tom V

13.9k74778

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

bumped to the homepage by Community♦ 8 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com May 6 '15 at 16:00

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Are you restoring to another server? To the originating server? If you have access to the server that was backed up, in the msdb database the record of backups is maintaned in the 5 dbo.backup... tables.
  
  – RLF
  May 6 '15 at 19:30
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you automating this or is it a 1 time thing?
  
  – Ali Razeghi
  May 6 '15 at 21:13
  

  
  
  
  

add a comment | 

0

I need to create a job that restores all user databases from UNC to my SQL Server.

1. All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.




2. What is the right way to do this? Build steps to all databases separately?

I need a suggestion on how to build it.

sql-server backup restore

share|improve this question

edited May 6 '15 at 18:49

Tom V

13.9k74778

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

bumped to the homepage by Community♦ 8 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com May 6 '15 at 16:00

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Are you restoring to another server? To the originating server? If you have access to the server that was backed up, in the msdb database the record of backups is maintaned in the 5 dbo.backup... tables.
  
  – RLF
  May 6 '15 at 19:30
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you automating this or is it a 1 time thing?
  
  – Ali Razeghi
  May 6 '15 at 21:13
  

  
  
  
  

add a comment | 

I need to create a job that restores all user databases from UNC to my SQL Server.

1. All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.




2. What is the right way to do this? Build steps to all databases separately?

I need a suggestion on how to build it.

sql-server backup restore

share|improve this question

edited May 6 '15 at 18:49

Tom V

13.9k74778

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

share|improve this question

edited May 6 '15 at 18:49

Tom V

13.9k74778

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

share|improve this question

edited May 6 '15 at 18:49

Tom V

13.9k74778

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

edited May 6 '15 at 18:49

Tom V

13.9k74778

edited May 6 '15 at 18:49

Tom V

13.9k74778

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

asked May 6 '15 at 15:30

Regev KesselRegev Kessel

1

1 Answer
1

active

oldest

votes

0

If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'

Declare @BackupFile NVARCHAR(1024)

select @BackupFile =  bf.physical_device_name from

(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date, 

            bm.physical_device_name   

            FROM SOURCESERVER.master.dbo.sysdatabases AS sd 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id

          where sd.name = @database

          GROUP BY 

            bm.physical_device_name) bf

Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'

execute SP_EXECUTESQL @RestoreStatement;

Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)

DECLARE @cmdline varchar(2000)

DECLARE @ReturnCode nvarchar(max)

CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))

SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\serverBackup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'

INSERT #tempTable

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

select @BackupFile = (select top 1 cmdShellOutput from #tempTable)

DROP TABLE #tempTable

share|improve this answer

edited May 7 '15 at 1:10

answered May 7 '15 at 0:39

SpörriSpörri

3,589722

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f100775%2frestore-user-db-from-unc%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'

Declare @BackupFile NVARCHAR(1024)

select @BackupFile =  bf.physical_device_name from

(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date, 

            bm.physical_device_name   

            FROM SOURCESERVER.master.dbo.sysdatabases AS sd 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id

          where sd.name = @database

          GROUP BY 

            bm.physical_device_name) bf

Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'

execute SP_EXECUTESQL @RestoreStatement;

Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)

DECLARE @cmdline varchar(2000)

DECLARE @ReturnCode nvarchar(max)

CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))

SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\serverBackup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'

INSERT #tempTable

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

select @BackupFile = (select top 1 cmdShellOutput from #tempTable)

DROP TABLE #tempTable

share|improve this answer

edited May 7 '15 at 1:10

answered May 7 '15 at 0:39

SpörriSpörri

3,589722

add a comment | 

0

If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'

Declare @BackupFile NVARCHAR(1024)

select @BackupFile =  bf.physical_device_name from

(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date, 

            bm.physical_device_name   

            FROM SOURCESERVER.master.dbo.sysdatabases AS sd 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id

          where sd.name = @database

          GROUP BY 

            bm.physical_device_name) bf

Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'

execute SP_EXECUTESQL @RestoreStatement;

Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)

DECLARE @cmdline varchar(2000)

DECLARE @ReturnCode nvarchar(max)

CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))

SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\serverBackup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'

INSERT #tempTable

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

select @BackupFile = (select top 1 cmdShellOutput from #tempTable)

DROP TABLE #tempTable

share|improve this answer

edited May 7 '15 at 1:10

answered May 7 '15 at 0:39

SpörriSpörri

3,589722

add a comment | 

If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'

Declare @BackupFile NVARCHAR(1024)

select @BackupFile =  bf.physical_device_name from

(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date, 

            bm.physical_device_name   

            FROM SOURCESERVER.master.dbo.sysdatabases AS sd 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id

          where sd.name = @database

          GROUP BY 

            bm.physical_device_name) bf

Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'

execute SP_EXECUTESQL @RestoreStatement;

Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)

DECLARE @cmdline varchar(2000)

DECLARE @ReturnCode nvarchar(max)

CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))

SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\serverBackup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'

INSERT #tempTable

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

select @BackupFile = (select top 1 cmdShellOutput from #tempTable)

DROP TABLE #tempTable

share|improve this answer

edited May 7 '15 at 1:10

answered May 7 '15 at 0:39

SpörriSpörri

3,589722

Declare @Database sysname = 'a'

Declare @BackupFile NVARCHAR(1024)

select @BackupFile =  bf.physical_device_name from

(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date, 

            bm.physical_device_name   

            FROM SOURCESERVER.master.dbo.sysdatabases AS sd 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name 

          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id

          where sd.name = @database

          GROUP BY 

            bm.physical_device_name) bf

Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'

execute SP_EXECUTESQL @RestoreStatement;

Declare @BackupFile NVARCHAR(1024)

DECLARE @cmdline varchar(2000)

DECLARE @ReturnCode nvarchar(max)

CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))

SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\serverBackup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'

INSERT #tempTable

EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

select @BackupFile = (select top 1 cmdShellOutput from #tempTable)

DROP TABLE #tempTable

share|improve this answer

edited May 7 '15 at 1:10

answered May 7 '15 at 0:39

SpörriSpörri

3,589722

answered May 7 '15 at 0:39

SpörriSpörri

3,589722

answered May 7 '15 at 0:39

SpörriSpörri

3,589722