SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

The questions I received in last two weeks:

“I do not have backup, is it possible to restore database to previous state?”

“How can restore the database without using backup file?”

“I accidentally deleted tables in my database, how can I revert back?”

“How to revert the changes, I have only logs but no complete backup?”

“How to rollback the database changes, my backup file is corrupted?”

Answer:

You need complete backup to rollback your changes. If you do not have complete backup you can not revert back. Sorry.

To restore the database to previous stage if you have full backup:

1) Restore the full backup

2) Restore the latest differential backup.

3) Restore the second most latest (latest – 1) transaction back. Now restore the log to the point step by step.

Reference : Pinal Dave (http://www.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable
Next Post
SQL SERVER – SPACE Function Example

Related Posts

46 Comments. Leave new

  • hi pinaldave how are you.

    please i want more information abot Partitioning table in SQL Server 2005 and SERVICE_BROKER

    How to use service_broker in stored procedure……

    if any notes or material please mail me

    Please help me

    Reply
  • Hi pinal,
    I want to get all ur interview question. Could I get it in one book. Could u please tell me where to buy these all interview questions books.

    Deepak Kumar

    Reply
  • I have added tables to the NOTHWIND database. In the past, when I wanted to update NORTHWIND on a new computer to contain the additional tables, I would do the following in SQL Server 2000:

    1)I would go to my development computer, that contains NORTHWIND with the additional files, and perform a backup to the disk file myfirstback using the enterprise manager.

    2) on the new computer I perform a backup to the disk file myfirstbackup.

    3) I overwrite myfirstbackup on the new computer with myfirstbackup from my development computer.

    4) I perform a restore on the new computer. NORTHWIND database then contains the added tables.

    Please assist me in that SQL SERVER 2005 is not permitting me to use the SQL SERVER 2000 back up file named myfirstback to restore. I created the disk file myfirstbackup while in SQL 2005, overwrite the file with the SQL 2000 version of myfirstbackup and then attempt the restore. SQL 2005 does not permit the operation as I did while in SQL 2000.

    Regards,
    drgorin

    Reply
  • Hello,

    What would I do to recover a database if a backup had not been made for 3 months (no transaction logs in that time either, only my current _log.ldf file)? Is this out of the question or could my current log hold the info to restore back from 3 months ago? (It says it’s 120 MB).

    Love you blog, I’ve learned a lot.

    Thanks,

    Rob

    Reply
  • i want to know how to get back last deleted table in sql server using query

    Reply
  • aliakbar sadeghi
    March 13, 2008 11:17 am

    hi pinaldave ,

    May I Help me for this question :

    when i use this command :

    RESTORE DATABASE Anbartechnical
    FROM DISK = ‘D:anb1222.bak’
    WITH NORECOVERY

    Sqlserver 2k5 told me :

    RESTORE cannot process database ‘Anbartechnical’
    because it is in use by this session. It is
    recommended that the master database be used
    when performing this operation.

    PLEASE HELP ME !

    Reply
  • hello
    i do not have any back up and my user delete the data how can i restore data from log file?it is very impportant for me to restore these data.would you please help me?
    thanks for your answer

    Reply
  • Hi,

    I want to backup entitre database using C#, can I do that if no than how do I back up using T-SQL.

    Thank You
    Rajesh

    Reply
  • how can i restore a differential bakup after a full backup .
    i have full backup and diffential bakup on different backup sets

    Reply
  • Talwasa khairkhwa
    August 21, 2008 10:48 am

    Hello dear Pinal

    i want some information about backup from one computer to another computer
    i hope help me THANK YOU.

    Reply
    • Take a backup
      Move the file to another compute
      Using restore command restore the database

      Reply
  • Friend, I want the serial or crack of Data recovery software for corrupted Microsoft® SQL Server databases ou Data Recovery Sql Server.

    Reply
  • I am facing a weird issue and need help on this one.

    What I am doing is a database restore using a production backup on a tst server. Databases name is same on both the servers. I am using GUI for this task. I am able to pick backup file but I could not see it under Select backup to restore as a reason I am not able to checkmark the backup file and user furthur options. Please assist.

    Reply
  • hi pinal ,

    1)how to findout tran log backups

    i have four tran logs backups ,lsn mis-matched which one should first restore transcational backups ,How to seached please tell me…

    2) how to do backup in sequential order ? please tell me

    3) In sqlserver 2005 Copy_only option is there ,in Sqlserver 2000 how to use not break LSN how to move different server

    please tell me

    Thanks& Regrads

    harishkumar.M

    Reply
  • hi
    how i can make buckup for sql server by delphi

    Reply
  • hi,
    i need help restoring my db.

    accidently i deleted 2 tables. immediately i did take full backup and log backup. but i couldn’t retrieve my lost tables. unfortunate i have 3 days old full backup and no log backup. is it possible to retrieve my lost table?
    Scenario:
    10:30 – deleted 2 tables
    10.31 – took full backup
    10.33 – took log backup

    i guess i did blunder!

    help me

    Thanks in advance

    Nanda

    Reply
    • u can get deleted table by restoring 3 days old back up.but the work that perform on these three days is not get. if u restore backup with “10.31 – took full backup ” back up u cannot get the deleted table.
      there is no any way to get both last 3 days work and deleted table.

      Reply
  • Hello Nanda,

    You can get the tables in 3 days old status from 3 days old backup.
    If after dropping the tables, you would have taken the transaction log backup first then you would be able to completely recover the tables but by taking the full backup lost the log as well as the possibility to recover later than 3 days old status.

    Regards,
    Pinal Dave

    Reply
  • Hello,

    I want to take regular backup of my database that shows along with it month, date & time using stored procedure.
    I have written following sql command for that purpose :

    DECLARE @str nvarchar(10), @sql NVARCHAR(1024)

    SET @sql = N’select @str=Left(CONVERT(VARCHAR(20), GETDATE(), 101),2)+substring(convert(varchar(10),getdate(),101),4,2)+substring(convert(varchar(20),getdate(),108),1,(patindex(”%:%”,convert(varchar(20),getdate(),108))-1))+substring(convert(varchar(20),getdate(),108),(patindex(”%:%”,convert(varchar(20),getdate(),108))+1),2)’

    EXEC sp_executesql @query = @sql,@params = N’@str nvarchar(10) OUTPUT’, @str = @str OUTPUT

    BACKUP DATABASE [MyDatabaseName] TO DISK = N ‘E:\data\backup\Cmpl’ + @str + ‘.bak’
    GO

    Then the command returns error near the ‘+’ sign. That is it does not concat the path and string.

    Please resolve the error.

    Reply
    • Refer this. It may help you in taking backup by appending current date

      The follwoing will help you in doing it for all databases excluding the system databases

      Reply
  • how to restore a database from a backup file which contains data, but i want to restore the database with no data(i just need only table structure)

    Reply
  • Ranjith Daniel
    July 30, 2010 6:00 pm

    I take backup in sql server 2005 in windows 7. But i need to restore the backup file in to sql server 2005 in windows xp sp2.
    i tried but i can’t able to find the solution..
    Plz replay to me…

    Reply
    • You need to make use of the restore command

      Restore database your_db from disk=’backup path’

      Reply
  • how to restore a database. the scenario is–
    i make a software that use to take a full or differential back up and i use store procedure to take back up.
    but when i want to restore that back up. its create problem
    by store procedure execution i can restore backup but in that instance s/w should not be in running mode.

    how to solve this problem ..
    if i use locking system then is it possible.
    if yes then how..

    please help me

    thanks and regards..

    vinit singh

    Reply

Leave a Reply

Menu