In the post below I mentioned a store procedure which can generate the database restore script based on the msdb history table
http://jamessql.blogspot.com/2012/04/generate-restore-script-automatically.html
Obviously, I had not tried restore in SQL Server 2012 Management Studio at that time. Now we have SQL 2012, which can provide more powerful ways to restore database. let's run the test script first.
==================================
--Create Test database
CREATE DATABASE [Test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Test] SET RECOVERY FULL
GO
ALTER DATABASE [Test] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Test] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
SET NOCOUNT ON
GO
USE [Test]
GO
--Create TABLE
create table mytest (a int, b char(10))
go
--Create full backup
Print convert(varchar(101),getdate(),113)
Print '--Created full backup 1'
backup database [Test] to disk = 'C:\temp\backup\Test_full_1.bak'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 1'
go
insert into mytest values(1, '1')
go 5
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '----Create diff backup 2'
backup database [Test] to disk = 'C:\temp\backup\Test_diff_2.bak' WITH DIFFERENTIAL
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 2'
go
insert into mytest values(2, '2')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 3'
BACKUP LOG [Test] TO DISK = N'C:\temp\backup\Test_log_3.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 3'
go
insert into mytest values(3, '3')
go 5
Print convert(varchar(101),getdate(),113)
Print '--Created full backup 4'
backup database [Test] to disk = 'C:\temp\backup\Test_full_4.bak'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 4'
go
insert into mytest values(4, '4')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 5'
BACKUP LOG [Test] TO DISK = N'C:\temp\backup\Test_log_5.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 5'
go
insert into mytest values(5, '5')
go 5
Print convert(varchar(101),getdate(),113)
Print '----Create diff backup 6'
backup database [Test] to disk = 'C:\temp\backup\Test_diff_6.bak' WITH DIFFERENTIAL
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 6'
go
insert into mytest values(6, '6')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 7'
BACKUP LOG [Test] TO DISK = N'C:\temp\backup\Test_log_7.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 7'
go
insert into mytest values(7, '7')
go 5
Print convert(varchar(101),getdate(),113)
Print '------Create Log backup 8'
BACKUP LOG [Test] TO DISK = N'C:\temp\backup\Test_log_8.trn'
go
waitfor DELAY '0:01:00'
Print convert(varchar(101),getdate(),113)
Print '--Insert batch - 8'
go
insert into mytest values(8, '8')
go 5
SET NOCOUNT OFF
GO
==================================
We created database "Test", inserted data and generated full/diff/log backup. In SSMS, right click database "Test", and select "Task"->"Restore"->"Database", the new UI popup
1. At the top of the window, there is message which notice you that a tail-log backup will be taken by default. That's a nice option, if you want to recover database to current point, a tail-log backup of current active database is necessary.
In the "Option" page, you can set the path of the tail-log backup file.
2. You can select restore source from
- Database: which get the restore information from msdb backup history tables.
- Device: which get the
restore information from backup file header
3. in "Destination" section, you can set the new database name, and restore database to any time point, which is a really nice feature. here let's change the dbname to "TestDB"
4. According to the source you select, the restore window will auto list all backup files which meet your requirement( which time point you want to restore).
In our sample, it listed last full backup +diff+2 log backup.
5. before you restore, you can click "Verify Backup Media" button to verify if backup file is corrupted. That's really helpful when you try to restore many files, but if the size of the files are big, it will take long time to verify.
6. In Files page, here is a enhancement, you can change the location of all files, it is convenient if you have multi-files.
7. In "options" page, we can set the path of tail-log backup file. since we restore test database to a new database TestDB, so we unchecked the "Leave source database in the restoring state" option.
Click OK, the new database testdb will be restored, however, you will not find data batch 8 in the mytest table, because we didn't restore trail-log backup.
Next, let's try restore to time point.
1. Click the "TimeLine..." button to open the Backup timeline window. here you can set the specific date and time.
2. Click OK and return to the restore window, in the restore plan section, it will list all backup files auto. that's really nice.
Last, let's try to restore database with the backup files directly.
1. Drop the Test Database with all backup history in msdb
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Test'
GO
USE [master]
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [Test]
GO
2. Click "Restore Databases" to open the restore db window, and select "Device" in Source section.
3. add all backup files, click ok
4. in the restore plan section, it will list the restore plan with the files we need.
if any backup file is missing, the tool will report error that the restore chain is broken.
I think if the file name could be listed in the restore plan as well, that will be great! without the file name, I can not know which files I will use to restore. the only way I can use is script the restore action, and check the script.
Except for improvement of the restore database, there is new UI for page restore as well. I like these improvement for the db restore :)
