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?”


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)

47 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


  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.



  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.




  4. hi pinaldave ,

    May I Help me for this question :

    when i use this command :

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

    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.



  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


  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.


  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



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



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


  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.

    Pinal Dave


  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’

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

    Please resolve the error.


  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)


  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…


  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


  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


  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


  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%
    MODIFY FILE ( NAME = N'DB1_log', SIZE = 1009664KB )
    If SIZE < 20% USED file size increase FILE SIZE + 20%


  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.


    • 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


  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?


  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=’\\\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


  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


  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.



  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


  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.


  26. hello sir
    i am using oracle 10g in window 7. after creating a table i am able to perform all the queries and operation but when i close Sql command line and reopen it. so all the data get deleted from tables which i have created previously i mean to say that structure of table remain but data is not there. so i insert data again and again to perform queries. please tell me solution

    thanks sir


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