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)
SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
SQL SERVER – Recovery Models and Selection
Microsoft Backup and Restore Links:
Overview of Backup in SQL Server
Backup Under the Simple Recovery Model
Backup Under the Full Recovery Model
Backup Under the Bulk-Logged Recovery Model
Introduction to Backup and Restore Strategies in SQL Server
Creating Full and Differential Backups of a SQL Server Database
Working with Transaction Log Backups
Working with Backup Media in SQL Server
Security Considerations for Backup and Restore
Overview of Restore and Recovery in SQL Server
Implementing Restore Scenarios for SQL Server Databases
Working with Restore Sequences for SQL Server Databases
Backing Up and Restoring System Databases
Using Marked Transactions (Full Recovery Model)
Optimizing Backup and Restore Performance in SQL Server
Understanding Recovery Performance in SQL Server
Backup and Restore in Large Mission-Critical Environments
Backup and Restore APIs for Independent Software Vendors
Reference : Pinal Dave (http://blog.SQLAuthority.com)
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
[...] SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup [...]
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
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/backup-database.aspx
The follwoing will help you in doing it for all databases excluding the system databases
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/10/backup-all-databases.aspx
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
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
[...] Restore Database Without or With Backup – Everything About Restore and Backup [...]
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