Sql-server – SQL Server 2008 database restore

sql-server-2008

I have database Registered and I backup that database from right click on database and backup and it is store registred.bak file.

After I format the system once again I want to restore that database then I follow the step create new database and right click on that newly created database and select restore and I choose file those already I take as back up file (.bak) file and click on restore the database restoring completed after then in right side of explorer view my database name registered (restoring...).

What is restoring point? Because when I click on this database it says

The dataase not accessible

What is this please explain me ???

Best Answer

Here is a run down to accomplish what you are trying to do. I added screen shots to help you along with backing up and restoring your database. The file paths won't exactly be the same.

BACKUP DATABASE

Backup the database Registered

enter image description here

Click on OK

enter image description here

The database has been backed up

RESTORE DATABASE

To restore the database registered, right click on the Registered Database in SSMS and choose Restore Database

In the window that appears, select From Device and click on the button with three ... to search for your backup

enter image description here

From here, click on Add and locate your backup file

enter image description here

enter image description here

Now click on OK

enter image description here

Click on OK

You are now back in the Restore Database Window, select Options

Make sure to select Overwrite the existing database (WITH REPLACE) and Leave the database ready to use option in the Recovery State options.

Then Click on General in the Select a page zone

enter image description here

Click on the checkbox "Restore" and then click on Script. I prefer sending the restore command to a new windows as a Script instead of directly clicking on OK. This way, I can drop any active connections to the database before restore which could result in a database in use error.

enter image description here

A new query window will be opened with the code necessary to restore the Registered database.

Add the following code just above the restore database script

-- drop all connections and put database in restricted user mode
ALTER DATABASE [Registered] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

-- restore database
RESTORE DATABASE [Registered] 
FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Registered.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 1
GO

You will notice that I modified the STATS option from STATS=10 STATS=1. This can be useful when restoring large databases. In the Messages tab, you can view the progression of the restore by %. With STATS=1, the restore can be monitored 1% at a time instead of every 10%

Now hit F5, to run the script.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

13 percent processed.
23 percent processed.
32 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
83 percent processed.
92 percent processed.
100 percent processed.
Processed 168 pages for database 'Registered', file 'Registered' on file 1.
Processed 5 pages for database 'Registered', file 'Registered_log' on file 1.
RESTORE DATABASE successfully processed 173 pages in 0.076 seconds (17.783 MB/sec).
Related Question