Skip to content

SQL Server Restore with GUI

    Restore database from backup using SQL Server Management Studio

    If you have SQL Server Management Studio installed, you can restore database backup using its interface alone. Just follow the instructions:

    1. Connect to your SQL Server and right-click on the “Databases” directory and choose “Restore Database”

    2. Click the button beneath the “Source” section next to “Device”
    3. In the “Select backup device” press “Add”
    4. Select the backup file or files (.bak) you are going to restore, then click “OK”
    5. In the “Restore Database” window specify the database’s name you will restore and click “OK” to start

    Your SQL Server database is restored.

    Restore SQL Server Database using SQL Script

    
    Create a test database
    
    Use master
    go
    
    Create database BackupDatabase
    go
    
    
    use BackupDatabase 
    go
    
    --Create table
    
    Create table Products
    (ProductID int IDENTITY (1,1) Primary Key,
    ProductName varchar (100),
    Brand varchar (100))
    go
    
    --Insert data into table
    
    insert into Products values ('motor','chevy')
    insert into Products values ('Hat','Nike')
    insert into Products values ('Shoe','Payless')
    insert into Products values ('Phone','Apple')
    insert into Products values ('Book','Green')
    insert into Products values ('Cup','Large')
    
    --View data
    
    select * from Products
    
    --Take full database backup of six rows using GUI
    
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    --Insert data into table AFTER a full database backup, but before a transactional log backup
    
    Insert into Products values ('Doll','Toy_R_us')
    
    --View data
    
    select * from Products
    
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --Using a GUI (number 1)
    
    
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    --BackupType
    --1 = Full
    --2 = T Log
    --5 = Differential
    
     --Another insert
    
    Insert into Products values ('House','Remax')
    
    select * from Products
    
    --another transaction log backup
    
    --Taking a transactional log backup (this can only be taken if the database recovery model is in Full mode, and a full database backup had been executed)
    --Using a GUI (number 2)
    
    --Look insde the .bak file
    RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\BackupDatabase.bak'
    
    --At this point, we have 1 full database backup with data from 1 to 6 rows
    --1 (first) transactional log backup of row 7
    --2 (second) transactional log backup of row 8
    
    --To restore the database if a disaster occured, we will need ALL the full, and 2 transactional logs to recover all the data from 1 through 8 rows
    
    --Lets delete the database and restore the using GUI

    Also See: