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)

46 thoughts on “SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

  1. 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

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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 !

    Like

  5. 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

    Like

  6. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  7. 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.

    Like

  8. 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

    Like

  9. 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

    Like

    • 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.

      Like

  10. 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

    Like

  11. 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.

    Like

  12. 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)

    Like

  13. 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…

    Like

  14. 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

    Like

  15. hi i facing the error while restore my database, my sql restore query is ‘RESTORE DATABASE Frogy FROM DISK = ‘D:\Frogy Automatic Database Backup\Frogy.02.12.2010.BAK’ WITH REPLACE, MOVE ‘Frog’ TO ‘C:\MSSQL\DATA\Frog_Data.MDF’, MOVE ‘Frog_log’ TO ‘C:\MSSQL\DATA\Frog_Log.LDF”

    but iam facing the error like this ‘

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

    how shall i rectify this error gyz

    Like

  16. hi…
    Im stuck up with a prob…. i happened to uninstall the default instance of sql server 2005 management studio express in my system… but after doinga system restore i brought back it… but after ths, all my systemdatabases like master, model n temp mdf and ldf files are missing… so when i try to connect to dataase server its saying error 26, not able to connect to server due to network specific or instance-specific error…. howto bring back my system databases for oper functioning of sql server 2005…. kindly help

    Like

  17. I would like to ask…. How could you modify multiple databases in a weekly job to have ALL databases maintain a BUFFERed usable amount of file growth available for ‘Initial Size(MB) ‘

    eg. ALTER DATABASE [DB1_base]
    MODIFY FILE ( NAME = N’DB1_base’, SIZE = 3999744KB )
    If SIZE < 20% USED file size increase FILE SIZE + 20%
    GO
    ALTER DATABASE [DB1_base]
    MODIFY FILE ( NAME = N'DB1_log', SIZE = 1009664KB )
    If SIZE < 20% USED file size increase FILE SIZE + 20%
    GO

    Like

  18. Good afternoon.
    One question, if I deleted a database by sdelecting right click – delete, where do files .ldf and .mdf go? They don’t appear in recycle folder, but they must go somewhere, could there be a possibility for recover those files?

    Thanks in advance.

    Like

    • SQL Server deletes it from the physical disks. So I dont think you can retreive it until you have a recent backup. Howvere detaching the database will not delete files from disk

      Like

  19. I hope this is interesting enough to warrant a piece of your time Pinal Dave..

    I have an application that remotely sync’s a database by copying across differential backups, for various reasons we cannot use replication options and everything is working perfectly except for one scenario..

    when we restore a differential backup that contains no data changes the following backups fail as they do not refer to the correct differential base ( a new full backup is created on the local machine when a successful restore on our server has taken place then the next time it runs it creates a new differential from this new base) however since the empty differential restore did not commit its changes each subsequent restore refers to the old differential base

    Is there a way to force a differential restore to update its base or to prevent it from returning a successful restore when it contains no data?

    Like

  20. Hello Sir,

    I have created one Store procedure for Backup and Restore a database. When i run my application and execute store procedure from it then my new database status is Restoring. It is not resrtore completely. Following is the commond i use to restore database.

    Use Master
    restore database TestDB
    from disk=’\\162.18.0.23\c$\backup\MyDB.bak’
    with move ‘MYDB’ to ‘C:\database\TestDB_data.mdf’,
    move ‘MYDB_log’ to ‘C:\database\TestDB_log.ldf’

    restore database TestDB with recovery

    Like

  21. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  22. Hi Pinal

    Is there any way or Sql Statement to find out the list of file name from a specified path.

    I want to provide a path of a folder (e.g. d:\prashant\) and Sql Statement or program should return all file names available in the d:\prashant\.

    Please help

    Like

  23. hello sir

    i restore full Production server backup on to Quality server by SQL query successfully, but i dont know how i should proceed further to run the quality server.
    Is there any post activity should i do? if yes, please suggest.

    Regards
    Parmod

    Like

  24. Hi, i need your help please i have some question about recover dropped database because i dropped my Database by mistake on sqlserver 2008 R2 and i want to recover my database how can i do? i have already tried to use the software recover my files and i didn’t find neither .mdf nor .ldf files .and i think on sqserver 20008 there isn’t command to recoverd dropped database like recover or flashback like oracle pleaase help me

    pleaase help me i need your help

    Like

  25. Hi Sir, I need your help on urgent basis. Sir I was working on a SQL server database last night. Morning Instead of taking backup, I restored it with the 3 yrs back month backup.That is Instaed of taking Backup I restored the previous database. Is there a way to roll back to my Database. Its urgent Sir.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s