August 4, 2011 at 12:51 pm
Hi,
We have a SQL database about 25GB in size. Our document management system uses the database to store links to images on the disk. A large folder was deleted from the document management system and the doc management software removed all traces of it from the database. We use Iron Mountain for our backups and can restore any version we need.
Here is the issue...I restored the MDF and log file from last friday to a new location and gave it a new name. We would like SQL server (2005) to simply see this database as a "new" database. We do not have another SQL instance so putting this on another server isn't an option. How do I attach this database to SQL server so it thinks its simply a new database? We need to access it so we can find the location of where the images were stored so they can be restored as well.
Any help would be greatly appreciated. Please let me know if you need any additional information about this scenario.
Thanks,
Lewis.
August 4, 2011 at 12:56 pm
CREATE DATABASE ...
FOR ATTACH
Just one problem though - copies of the mdf and ldf are NOT backups. Unless your backup tool interfaced with SQL and quiesced the database while it copied the files off, there's a good chance the copies of the files are useless.
The proper way to backup a database is with BACKUP DATABASE or a 3rd party SQL backup tool.
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
August 4, 2011 at 1:04 pm
Gail - I forgot to mention when I originally went into the management studio and did an attach on the restored MDF file, it knew the original name of the database (that is still running) and the path to it and the log file. How does doing the "create database foo for attach" solve that issue? I want to be able to access both of them. The original is part of our production system that I won't and can't touch. The backup copy (and hopefully working copy) is just so we can go back in time and extract some information from it.
August 4, 2011 at 1:20 pm
You can change the name of the database in the attach dialogue. It's just a default. Same with the default of the log file. Change it to point to the log file that you're actually attaching.
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
August 4, 2011 at 1:36 pm
And doing this won't impact/touch anything with the original database as long as I give it a new name? This is the script it wants to execute:
USE [master]
GO
CREATE DATABASE [LfPBCWall] ON
( FILENAME = N'D:\LFPBCWall\WallRestore.mdf' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'LfPBCWall' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [LfPBCWall].dbo.sp_changedbowner @loginame=N'PPENET\Administrator', @map=false
GO
The original db name was "LfPBC".
August 4, 2011 at 1:40 pm
Specify the log file name as well, give it a new name.
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
August 4, 2011 at 1:46 pm
It didn't like the log file part. It says the system cannot find the file specified for the LDF file.
USE [master]
GO
CREATE DATABASE [WallRestore] ON
( FILENAME = N'D:\LFPBCWall\WallRestore.mdf' ),
( FILENAME = N'D:\LFPBCWall\WallRestore.LDF' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'WallRestore' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [WallRestore].dbo.sp_changedbowner @loginame=N'PPENET\Administrator', @map=false
GO
August 4, 2011 at 1:49 pm
Specify the path and name of the log file that you restored.
p.s. You need to look at this 'backup' solution. Copying mdf and ldf files is NOT how SQL backups should be taken.
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
August 4, 2011 at 1:51 pm
Agreed. I am already in contact with our backup vendor to sort that part out. I'm going to try this at an off hour just in case. Thanks so much for your help.
August 4, 2011 at 2:16 pm
Gail - some of what I found out so far is that the backup solution we are using uses VSS and not the transaction logs to back things up. Apparently its a different approach than other SQL backups. Again - thanks for all your help.
August 4, 2011 at 2:21 pm
Something you need to check with them, if the database fails catastrophically at (say) 14h36 one day, what's the best restore they can do? (properly configured SQL backups can, in most cases restore to 14h36) and is what it is capable of acceptable to the users of this system?
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
August 4, 2011 at 2:30 pm
Not sure I follow your question exactly, but we backup everything every 15 minutes. According to the console, we have 98 versions in the past 24 hours and 114 versions in the past 48 hours.
I am looking at the restore points from today and see a bunch of them.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply