Restore database with all data

Question

I created a full backup of a database (.bak file). Then, I restored it to another machine. I used this query to restore:

IF DB_ID('Northwind') IS NULL 
BEGIN
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM DISK = N'C:\Program Files\Microsoft SQLServer\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH 
   FILE = 1, NOUNLOAD, STATS = 10,
   MOVE N'YOUR logical name of data file as shown by RESTORE FILELISTONLY command'
        TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
   MOVE N'YOUR logical name of Log file as shown by RESTORE FILELISTONLY command'
        TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF'
END

But when I used a select query, data is still displayed the same as before restoring. How to solve this problem if i want all data match with each other.

possible duplicate of [How to restore to a different database in sql server?](https://stackoverflow.com/questions/6267273/how-to-restore-to-a-different-database-in-sql-server?rq=1) — Be1ng_Kr1Sh, Jun 04 '18 at 07:29
i don't think so. Cuz i successfully restore db but data on tables isn't restored @Be1ng_Kr1Sh — Nguyễn Văn Hưng, Jun 04 '18 at 07:38
Are you restoring over an existing database? If so, my guess is the restore failed (probably because it was in use).. — Thom A, Jun 04 '18 at 08:22
Your script only executes the restore if the database does not exist at all. If the database is the same as before the restore, that implies it already exists and the restore would not be performed. — Wes H, Jun 04 '18 at 16:55
What is the reason you do not mark people's answers as the correct answers when they post? If you are unaware of how to, you can use the up/down arrow next to the answer to vote for a good post and select the grey tick mark next to the question to the correct answer to mark it as the correct answer. It is just making a question more complete and it helps others find completed questions and answers as well as rewarding the poster some reputation points for assisting you. — Gerhard, Nov 06 '19 at 10:17

score 0 · Answer 1 · answered Jun 05 '18 at 15:59

The script provided has an IF condition which checks the DB_ID system function to see if any database exist on the SQL Server with the same name. If check is true, then the restore process is skipped.There should not be a database with the same name on the SQL Server for this script to run.

Or you can modify the script to include the REPLACE option for restoring databases. See MSDN Link: https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017#REPLACEoption

Your script should now look like this:

   IF DB_ID('Northwind') IS NULL 
BEGIN  //This runs if no database of the name "Northwind" exist
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM DISK = N'C:\Program Files\Microsoft SQLServer\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH 
   FILE = 1, NOUNLOAD, STATS = 10, 
   MOVE N'YOUR logical name of data file as shown by RESTORE FILELISTONLY command'
        TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
   MOVE N'YOUR logical name of Log file as shown by RESTORE FILELISTONLY command'
        TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF'
END
ELSE
BEGIN //This runs if there is a database with same name by replaceing it.
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM DISK = N'C:\Program Files\Microsoft SQLServer\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH 
   FILE = 1, NOUNLOAD, REPLACE, STATS = 10, 
   MOVE N'YOUR logical name of data file as shown by RESTORE FILELISTONLY command'
        TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
   MOVE N'YOUR logical name of Log file as shown by RESTORE FILELISTONLY command'
        TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF'
END

There are great third party tools that can perform these backups and restores and even fix corrupted database files with ease to assist you in your work. Check out Stellar Database Toolkit.

NOTE: Stack Overflow has a database administrator forum https://dba.stackexchange.com/ separate from this which is targeted for database questions like this. Post database related questions over there for a quicker and accurate answers.

Thanks and HTH.

Restore database with all data

1 Answers1