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 Comments. Leave new
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
Use master
GO
–your restore statement
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
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
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
I see, that’s a shame but now I understand why is so important to have backups, thanks for the response.
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?
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
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
Dear Sir,
How to move the master database of sql server 2000 to sql server 2005.
Kindly help.
hai sir i need one small query
i deleted 2000 of records in an table
how i recover the records sir
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
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
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.
Please let me How to deleted old backup files in Sql server agent jobs
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
Can I get a deleted database again?????