November 22, 2018 at 10:25 am
Hi
I'm looking at some backup solutions/tools for SQL Server.
I can see the tool takes regular backups, using SQL backup database command as these are logged in MSDB.
When testing restore to my PC; I was expecting it to issue a restore database command however it does a dettach/attach
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'SampleDB')ALTER DATABASE [SampleDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'SampleDB', @skipchecks = 'false'
CREATE DATABASE [SampleDB] ON (FILENAME = N'C:\SQLData\SampleDB_Data.MDF'),(FILENAME = N'C:\SQLData\SampleDB_Log.LDF') FOR ATTACH
I'm curious How can it produce the mdf + ldf files from what I presume is a bak file?
I use restore from bak files and only used attach when moving DB files is it ok to rely on this attach process.
Thanks
November 26, 2018 at 6:42 am
I don't know which tool you're referring to. However, the command there is running an attach from an MDF and an LDF. You can't attach from a .BAK. You can only RESTORE. It sounds like whatever is doing this to your system, you may need to reevaulate it and do some testing. ATTACH/DETACH is not a backup plan that I would want on any system I managed.
"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
November 26, 2018 at 12:55 pm
terry999 - Thursday, November 22, 2018 10:25 AMHiI'm looking at some backup solutions/tools for SQL Server.
I can see the tool takes regular backups, using SQL backup database command as these are logged in MSDB.When testing restore to my PC; I was expecting it to issue a restore database command however it does a dettach/attach
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'SampleDB')ALTER DATABASE [SampleDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'SampleDB', @skipchecks = 'false'
CREATE DATABASE [SampleDB] ON (FILENAME = N'C:\SQLData\SampleDB_Data.MDF'),(FILENAME = N'C:\SQLData\SampleDB_Log.LDF') FOR ATTACHI'm curious How can it produce the mdf + ldf files from what I presume is a bak file?
I use restore from bak files and only used attach when moving DB files is it ok to rely on this attach process.Thanks
Hi, the method you are using is basically deattach and attach.
Deattach a database attach the .mdf and .ldf file of the database to a database from a location.
If you want have the method to restore a database, then first take backup of a database and the restore the backup file (.bak) to a new database.
This way it will not produce the .mdf and .ldf file. But it will use the existing .mdf and .ldf of the database that you restored on to.
Hope this explains and you are satisfied.
November 26, 2018 at 4:14 pm
Hi Thanks for replying.
The tool (I believe is called UniTrends) is being used for file backups. It has a SQL plugin (windows service/agent) which I've installed on an SQL Server.
I can see it is doing real SQL backups because there are entries in MSDB tables e.g. msdb.dbo.backupset etc
The SQL (dettach/attach) posted, is what I see in Profiler when a "restore" was invoked in the management console of the tool.
I was expecting it to do a normal restoreRESTORE DATABASE SampleDB FROM DISK = N'\\192.168.0.1\..\SampleDB.bak' .................................
A real SQL backup must be made, otherwise there would be no entries in MSDB?
How can it be extracting MDF+LDF files from a bak file without first restoring it somewhere. It does NOT do a "Restore Database" on the destination Server.
Any ideas?
I've always used back/restore i.e. using *.bak files. I'm weary of this solution.
I need to understand why this is a bad approach.
November 27, 2018 at 5:16 am
I don't know how it's extracting an MDF/LDF from the .BAK file (if it is). I assume it's possible because my company does some similar magic through one of our tools, but the mechanism isn't known to me. My concern is that if you're using the tool to do the restore, how will it do a point in time recovery? Can it do a point in time recovery. Any time I use any tool other than SQL Server to backup databases, I demand a test of the point in time recovery. If that fails, that tool is out the window and we're back to SQL Server. If it takes longer than a scripted point in time recovery will take, that tool is gone. Backups are important, yes, but it's the restore that matters most. Test those thoroughly before relying on any tool.
"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
November 27, 2018 at 7:33 am
While I have no experience with the tool in question here, a fairly simple test to see if it's taking a "normal" SQL backup or doing something weird behind the scenes would be to take one of the BAK files it creates and try to restore it yourself, without using the tool.
Try all the usual suspects, RESTORE HEADERONLY, RESTORE FILELISTONLY and see what happens.
My thought is, possibly what it might be doing is "combining" the MDF and LDF into one file somehow, rather than running an actual "BACKUP DATABASE" command. Which you could also check what it's doing during the backup by capturing the queries being run during a backup.
November 27, 2018 at 1:57 pm
Grant good question so far testing has been on Server with DBs all in SIMPLE recovery mode. It does have the option for Point in time recovery, but I'd have to install it on a Server which needs tran log backups. I'm not ready for that.
Jasona - I cannot get at the *.bak file I have to use the tool to do its restore. In other words can only restore to a server which has the tool installed. I don't see the bak file.
I'm pretty certain its doing a backup I can see it in MSDB tables, I also managed to break its Differential backup chain by taking a full backup and forgetting to use "copy only".
The tool lives on its own Server, where it stores the file backups, could it be storing backups there; When a restore is requested it restores to its instance of SQL to create the files? But how would it handle multiple versions of SQL engine?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply