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 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
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
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
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
i want to know how to get back last deleted table in sql server using query
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 !
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
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
Backup database your_db to disk=’file_path’
how can i restore a differential bakup after a full backup .
i have full backup and diffential bakup on different backup sets
Hello dear Pinal
i want some information about backup from one computer to another computer
i hope help me THANK YOU.
Take a backup
Move the file to another compute
Using restore command restore the database
Friend, I want the serial or crack of Data recovery software for corrupted Microsoft® SQL Server databases ou Data Recovery Sql Server.
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.
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
hi
how i can make buckup for sql server by delphi
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
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.
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
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.
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
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)
You need to restore the database and delete all data from all tables
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…
You need to make use of the restore command
Restore database your_db from disk=’backup path’
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
What was the error you got during restoreing the database?
You need to point application’s default database to other database like master