Restore user db from UNC
I need to create a job that restores all user databases from UNC to my SQL Server.
All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
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
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♦ 18 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.
add a comment |
I need to create a job that restores all user databases from UNC to my SQL Server.
All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
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
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♦ 18 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.
All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
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
edited May 6 '15 at 18:49
Tom V
13.9k74778
asked May 6 '15 at 15:30
Regev KesselRegev Kessel
1
I need to create a job that restores all user databases from UNC to my SQL Server.
All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
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
sql-server backup restore
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
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
edited May 6 '15 at 18:49
Tom V
13.9k74778
13.9k74778
asked May 6 '15 at 15:30
Regev KesselRegev Kessel
1
asked May 6 '15 at 15:30
Regev KesselRegev Kessel
1
asked May 6 '15 at 15:30
Regev KesselRegev Kessel
1
1
bumped to the homepage by Community♦ 18 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 18 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.
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 |
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
1
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 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
Are you automating this or is it a 1 time thing?
– Ali Razeghi
May 6 '15 at 21:13
add a comment |
1 Answer
1
active
oldest
votes
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
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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
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
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
answered May 7 '15 at 0:39
SpörriSpörri
3,589722
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
answered May 7 '15 at 0:39
SpörriSpörri
3,589722
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
3,589722
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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