April 17, 2015 at 8:01 am
Hi there,
I need to recover some data in a table but i'm not 100% sure the right way to do this safely.
I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?
if i restore, i assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?
the SQL server is 2008 R2 running as a VM.
Many thanks,
JV
April 17, 2015 at 8:08 am
You can restore a backup as a different database very easily. In the GUI just make sure that the DB is restoring to a different name and make sure the files get different names too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 8:13 am
So, am i using the attach method or Restore method?
i'm recovering for .bak and .trn files (if that makes a difference)?
Thanks,
JV
April 17, 2015 at 8:16 am
Also, do i create a new Database and then restore the files/tables into that database or will the GUI allow me to specify a new name etc during the process?
Thanks,
JV
April 17, 2015 at 8:25 am
You restore backups, you can't attach a backup.
In the restore dialog, just make sure that the DB is restoring to a new name and make sure the files get different names too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 8:26 am
Different Names or different Locations? Or Both?
April 17, 2015 at 8:33 am
If you mean the files, either. Either different names in the same folder as the other DBs or change the location. Doesn't matter, the point is that you don't want to tell the restore dialog to over write a file of an existing database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 8:47 am
Restoring your backup of "MyDatabase" will create a new database called "MyDatabaseCopy" with different file names if you follow the script below. You may need to highlight and execute the "Restore FileListOnly FROM DISK...bak'" to figure out the logical names.
RESTORE DATABASE [MyDatabaseCopy] -- Restore FileListOnly
FROM DISK = N'c:\Backups\MyDatabase.bak' WITH FILE = 1,
MOVE N'MyDatabaseFile' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabaseCopy.mdf',
MOVE N'MyDatabaseFile_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDatabaseCopy_log.ldf',
NOUNLOAD, REPLACE, stats = 20
GO
April 17, 2015 at 8:52 am
It actually might be easier to just use T-SQL rather than the GUI. It's much more straight forward to RESTORE:
RESTORE DATABASE 'ANewName'
FROM DISK = 'e:\backups\myotherdb.bak'
WITH MOVE 'logicaldataname' to 'd:\data\anewname.mdf',
MOVE 'logicallogname' to 'g:\logs\anewname.ldf'
Getting all that right is easier right there in a script than it is in the GUI.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 17, 2015 at 3:03 pm
Many thanks for your help guys.
Much appreciated!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply