locked
how to restore data base to previous state automatically RRS feed

  • Question

  • Hi all,

    i am working as  DBA as my  team now needs  to restore automatically  its previous state when something bad happens to data base how i can  achieve this requirement please help thanks in advance

     

    Regards

    Sunil Yoganna
    Monday, December 27, 2010 9:59 AM

Answers

  • Whats the SQL Edition? in 2005 onwards, you can use database snaphot for this purpose.

    use master
    go
    set nocount on
    go
    -- Drop database snapshot if it already exists
    IF EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'SunilYoganna_snapshot'
    )
    DROP DATABASE SunilYoganna_snapshot
    GO
    IF EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'SunilYoganna'
    )
    DROP DATABASE SunilYoganna
    GO
    
    create database SunilYoganna
    go
    use SunilYoganna
    go
    create table foo(bar varchar(20))
    go
    insert into foo values ('1st modification')
    insert into foo values ('2nd modification')
    go
    select * from foo
    go
    /*
    bar
    --------------------
    1st modification
    2nd modification
    */
    
    use master
    go
    -- Drop database snapshot if it already exists
    IF EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'SunilYoganna_snapshot'
    )
    DROP DATABASE SunilYoganna_snapshot
    GO
    
    -- Create the database snapshot
    CREATE DATABASE SunilYoganna_snapshot ON
    ( NAME = SunilYoganna, FILENAME = 
    'C:\temp\SunilYoganna_snapshot.ss' )
    AS SNAPSHOT OF SunilYoganna;
    GO
    -- make some modifications
    
    use SunilYoganna
    go
    update foo
    set bar = 'both rows modified'
    
    -- verify the modification
    select * from foo
    go
    /*
    bar
    --------------------
    both rows modified
    both rows modified
    */
    
    -- restore the database using snapshot
    use master
    go
    RESTORE DATABASE SunilYoganna
    FROM DATABASE_SNAPSHOT = 'SunilYoganna_snapshot'
    go
    -- verify the rollback of modifications
    use SunilYoganna
    go
    select * from foo
    go
    /*
    bar
    --------------------
    1st modification
    2nd modification
    */
    set nocount off
    go
    

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, December 27, 2010 10:53 AM

All replies

  • What do you mean by Automatically?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, December 27, 2010 10:04 AM
  • Hi Bala,

    It should restore automatically  to its previous state i don't know whether their is  any option to do this i am fresher i want clarify as in my firm every one asking me can possible so i posted so please help how it can be possible

    thanks in advance

    Regards

    Sunil Yoganna

    Monday, December 27, 2010 10:10 AM
  • If you can explain the scenerio, we may be able to help you.
    Did you look at database snapshot feature?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, December 27, 2010 10:14 AM
  • Hi,

    i am having the data base that is working fine after some modification i want my data base to be restored to previous state that is before modification How i can do this please help.

    Regards

    Sunil Yoganna

    Monday, December 27, 2010 10:31 AM
  • Read Tibor's artcle to get an idea

    http://www.karaszi.com/SQLServer/info_minimizing_data_loss.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 27, 2010 10:49 AM
  • Whats the SQL Edition? in 2005 onwards, you can use database snaphot for this purpose.

    use master
    go
    set nocount on
    go
    -- Drop database snapshot if it already exists
    IF EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'SunilYoganna_snapshot'
    )
    DROP DATABASE SunilYoganna_snapshot
    GO
    IF EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'SunilYoganna'
    )
    DROP DATABASE SunilYoganna
    GO
    
    create database SunilYoganna
    go
    use SunilYoganna
    go
    create table foo(bar varchar(20))
    go
    insert into foo values ('1st modification')
    insert into foo values ('2nd modification')
    go
    select * from foo
    go
    /*
    bar
    --------------------
    1st modification
    2nd modification
    */
    
    use master
    go
    -- Drop database snapshot if it already exists
    IF EXISTS (
    	SELECT name 
    		FROM sys.databases 
    		WHERE name = N'SunilYoganna_snapshot'
    )
    DROP DATABASE SunilYoganna_snapshot
    GO
    
    -- Create the database snapshot
    CREATE DATABASE SunilYoganna_snapshot ON
    ( NAME = SunilYoganna, FILENAME = 
    'C:\temp\SunilYoganna_snapshot.ss' )
    AS SNAPSHOT OF SunilYoganna;
    GO
    -- make some modifications
    
    use SunilYoganna
    go
    update foo
    set bar = 'both rows modified'
    
    -- verify the modification
    select * from foo
    go
    /*
    bar
    --------------------
    both rows modified
    both rows modified
    */
    
    -- restore the database using snapshot
    use master
    go
    RESTORE DATABASE SunilYoganna
    FROM DATABASE_SNAPSHOT = 'SunilYoganna_snapshot'
    go
    -- verify the rollback of modifications
    use SunilYoganna
    go
    select * from foo
    go
    /*
    bar
    --------------------
    1st modification
    2nd modification
    */
    set nocount off
    go
    

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, December 27, 2010 10:53 AM