January 28, 2016 at 6:34 am
I want to know how to restore a backup by selecting the bak file and having SQL Server identify the Differential and transaction logs associated with the bak file.
I need to do it with a Point in time or just a plain backup.
The thread below kind of reflects whet I'm trying to do.
I did a restore to another Database by generating a script and modifying it.
_________________________________________________________________________________________________________________
Re: Restoring database and logs from .bak and .trn files
The functionality is there, but it relies on having backup history in
the msdb. This is the default for the restore dialog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ngan" wrote in message
news:04493880-4F8C-473E-8DE9-B4C36C9CB890atmicrosoftdotcom...
>I was able to use the script icon to copy the transact-sql.
>
> Followup question:
>
> SQL 2000 has a GUI where it allows you to select the bak file you
> want to
> restore and it automatically knows the transaction logs that goes
> with the
> selection! No need to create a sql script.
>
> Why didn't SQL 2005 keep that feature?
>
> "ngan" wrote:
>
>> I have SQL 2005.
>>
>> I do a sql backup of the database once a day to a remote server and
>> a sql
>> backup of the transaction logs every hour to a remote server. I
>> can do a
>> test restore using the management studio, but it's tedious to
>> restore each
>> log (have to restore each log one at a time).
>>
>> I want to create a sql script that would restore the db with the
>> NoRecovery
>> option, restore the log files to the hour that I want with
>> norecovery option,
>> and finally set the db to be recovery and live.
>>
>> The location of the backup files are located at
>> \\servername\sqlbackup\databasename
>>
>> the db backup file is databasename_backup_200811132315.bak
>>
>> the log backup files are databasename_backup_200811140000.trn,
>> databasename_backup_200811140100.trn,
>> databasename_backup_200811140200.trn,
>> etc
>>
>> Does anyone know the syntax to use?
>>
>> Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 6:41 am
I found this:
Use the Management Studio in the following fashion to learn what you need:
Choose your database, right-click and select Tasks / Restore / Database.
On the Restore Database dialog, click on the three dots [...] at the end of
"To a point in time" and specify the time to which you want to restore. It
will select the full backups, differential backups, and log backups
necessary to do the job, using the NORECOVERY and RECOVERY options
appropriately.
Now, press the Script button at the top of the form to generate the script.
Examine the script to see how it all works. (Or maybe the Management Studio
is now giving you what you need.)
RLF
----------------------------------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 7:21 am
Yep. That's how you do it with the GUI. The potential issue there is that you could have out-of-stream backups of different types that the GUI might not always deal with appropriately.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2016 at 7:21 am
The following URL looks like what I want to do:
http://sqlblog.com/blogs/merrill_aldrich/archive/2013/03/15/ssms-2012-restore-gui-gotcha.aspx
Any clarifications would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 7:30 am
It looks like you have the clicks right, but the best way is to use T-SQL.
If you must use the GUI, be sure to not overwrite the production database.
January 28, 2016 at 7:36 am
Ed Wagner (1/28/2016)
It looks like you have the clicks right, but the best way is to use T-SQL.If you must use the GUI, be sure to not overwrite the production database.
I have almost used T-SQL but the transaction log backups occur every 5 minutes. That is a lot of T-SQL don't you think?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 7:44 am
My problem is that I use T-SQL but there are too many Log backup files that it seems that the GUI is by far the fastest way to resore.
I'm concerned about accidently restoring the Production Database.:w00t:
What do you think?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 8:14 am
The way I've dealt with this in the past is to use T-SQL or PowerShell to generate the scripts. Script the scripts. That way you don't have to rely on the GUI. You can exercise direct control.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2016 at 8:24 am
Grant Fritchey (1/28/2016)
The way I've dealt with this in the past is to use T-SQL or PowerShell to generate the scripts. Script the scripts. That way you don't have to rely on the GUI. You can exercise direct control.
Sounds good.
This is a lot to ask but do you have an example that I could use as a template to do this?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 10:36 am
Welsh Corgi (1/28/2016)
Grant Fritchey (1/28/2016)
The way I've dealt with this in the past is to use T-SQL or PowerShell to generate the scripts. Script the scripts. That way you don't have to rely on the GUI. You can exercise direct control.Sounds good.
This is a lot to ask but do you have an example that I could use as a template to do this?
Thanks.
A search here on SSC will turn up a few.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2016 at 11:45 am
Grant Fritchey (1/28/2016)
Welsh Corgi (1/28/2016)
Grant Fritchey (1/28/2016)
The way I've dealt with this in the past is to use T-SQL or PowerShell to generate the scripts. Script the scripts. That way you don't have to rely on the GUI. You can exercise direct control.Sounds good.
This is a lot to ask but do you have an example that I could use as a template to do this?
Thanks.
A search here on SSC will turn up a few.
I got several hits but not what I was looking for.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 2:06 pm
Just query the backup tables in MSDB.
eg Something like the following will get the logs since the last full backup.
SELECT 'RESTORE LOG YourDBRestore FROM DISK =''' + M.physical_device_name + ''' WITH NORECOVERY;' --,STOPAT = 'YourTime'
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
CROSS APPLY
(
SELECT TOP (1) SD.backup_finish_date
FROM msdb.dbo.backupset SD
JOIN msdb.dbo.backupmediafamily MD
ON SD.media_set_id = MD.media_set_id
WHERE SD.[type] = 'D' -- Full Backup (I = Diff, L = Log)
AND SD.database_name = S.database_name
AND MD.device_type = M.device_type
ORDER BY SD.backup_finish_date DESC
) A
WHERE S.[type] = 'L'
AND S.database_name = 'YourDB'
AND M.device_type = 2
AND S.backup_start_date >= A.backup_finish_date
ORDER BY S.backup_start_date;
You could also check for no breaks in LSNs etc.
January 28, 2016 at 2:21 pm
Ken McKelvey (1/28/2016)
Just query the backup tables in MSDB.eg Something like the following will get the logs since the last full backup.
SELECT 'RESTORE LOG YourDBRestore FROM DISK =''' + M.physical_device_name + ''' WITH NORECOVERY;' --,STOPAT = 'YourTime'
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
CROSS APPLY
(
SELECT TOP (1) SD.backup_finish_date
FROM msdb.dbo.backupset SD
JOIN msdb.dbo.backupmediafamily MD
ON SD.media_set_id = MD.media_set_id
WHERE SD.[type] = 'D' -- Full Backup (I = Diff, L = Log)
AND SD.database_name = S.database_name
AND MD.device_type = M.device_type
ORDER BY SD.backup_finish_date DESC
) A
WHERE S.[type] = 'L'
AND S.database_name = 'YourDB'
AND M.device_type = 2
AND S.backup_start_date >= A.backup_finish_date
ORDER BY S.backup_start_date;
You could also check for no breaks in LSNs etc.
Thanks.
I would need to get the last differential backup as well.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 2:42 pm
Welsh Corgi (1/28/2016)
Ken McKelvey (1/28/2016)
Just query the backup tables in MSDB.eg Something like the following will get the logs since the last full backup.
SELECT 'RESTORE LOG YourDBRestore FROM DISK =''' + M.physical_device_name + ''' WITH NORECOVERY;' --,STOPAT = 'YourTime'
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
CROSS APPLY
(
SELECT TOP (1) SD.backup_finish_date
FROM msdb.dbo.backupset SD
JOIN msdb.dbo.backupmediafamily MD
ON SD.media_set_id = MD.media_set_id
WHERE SD.[type] = 'D' -- Full Backup (I = Diff, L = Log)
AND SD.database_name = S.database_name
AND MD.device_type = M.device_type
ORDER BY SD.backup_finish_date DESC
) A
WHERE S.[type] = 'L'
AND S.database_name = 'YourDB'
AND M.device_type = 2
AND S.backup_start_date >= A.backup_finish_date
ORDER BY S.backup_start_date;
You could also check for no breaks in LSNs etc.
Thanks.
I would need to get the last differential backup as well.
Thanks again.
So, given the comment in the code on sd.[type], how would you query for it?
If you need it, the Books Online page for the table is https://msdn.microsoft.com/en-us/library/ms186299.aspx.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply