January 6, 2011 at 12:01 am
Does anyone know about some simple utility which takes backup of the / creates a copy of the mdf and ldf files to some other location that can be used as a backup.
There are reason i can not use sqlserver command line tools or management
studio.
It has to be a third party solution that just creates a copy of the database files.
January 6, 2011 at 1:20 am
If you can afford down time you can bring down the SQL Server instance and then use a tool like ROBOCOPY to copy the files to a different location.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 6, 2011 at 1:23 am
To add to what Adiga said, you can always make a normal backup, restore it as a different name, detach from SQL, and do as you please with the mdf and ldf files....
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 6, 2011 at 3:16 am
raj.lath-932078 (1/6/2011)
Does anyone know about some simple utility which takes backup of the / creates a copy of the mdf and ldf files to some other location that can be used as a backup.There are reason i can not use sqlserver command line tools or management
studio.
It has to be a third party solution that just creates a copy of the database files.
You do realize you have to shut down SQL Server to do that, otherwise the data files will be locked.
If you can shut it down, you use a simple .bat file written in DOS to do the copying. No need to purchase expensive utilities.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 3:22 am
raj.lath-932078 (1/6/2011)
Does anyone know about some simple utility which takes backup of the / creates a copy of the mdf and ldf files to some other location that can be used as a backup.
Why? That's not usually the way to backup a SQL 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
January 6, 2011 at 8:17 am
the only way I know that you could do this is when your databases are running on a SAN. Then, you can use some of the transaction aware SAN utilities that will allow you to create snapshots of the database in real time, basically, copying the MDF & LDF.
The big question is, why can't you use the tools & capabilities provided for you within SQL Server? With the exception above, anything else is likely to be dangerous to the system, cause down-time, or flat out not work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 6, 2011 at 8:24 am
Sounds like shared hosting w/o admin access.
January 7, 2011 at 2:55 am
Yes i did finally achieve this with these command in a bat file.
cd D:\sqlbackup
sc stop mssql$sqlexpress
copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\hospital_log.ldf" D:\sqlbackup\ /Y
copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Hospital.mdf" D:\sqlbackup\ /Y
sc start mssql$sqlexpress
Some of the thing i was doing wrong for path having spaces in directory name
you need to put it under quote.
Once that was done i could do this easily.
Now its a very elegant and simple solution for peoples who do not have enough knowledge of handling sqlserver.It takes under a minute to do the whole backup so they can take the backup anytime with out distrurbing the client computers.
January 7, 2011 at 3:02 am
raj.lath-932078 (1/7/2011)
Now its a very elegant and simple solution for peoples who do not have enough knowledge of handling sqlserver.It takes under a minute to do the whole backup so they can take the backup anytime with out distrurbing the client computers.
If you use the TSQL BACKUP command (which is obviously the preferred method), you can also take backups without disturbing the client computers and you don't have to shut down SQL Server :w00t:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2011 at 3:06 am
OK can you tell me how exactly to do that.
I am always open to ideas and appreciate every thing that would make doing anything in a better and optimized way.
January 7, 2011 at 3:08 am
In your first post you said you couldn't use management studio or other tools.
Is this still the case?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2011 at 3:30 am
I am perfectly at ease with sqlstudiomanagementexpress or sqlserver.
What i meant was my clients are not too familiar with these and i need a solution for them.
January 7, 2011 at 5:10 am
raj.lath-932078 (1/7/2011)
I am perfectly at ease with sqlstudiomanagementexpress or sqlserver.What i meant was my clients are not too familiar with these and i need a solution for them.
Ah allright, that was not what I understood from your original question.
There are reason i can not use sqlserver command line tools or management
studio
Anyway, in that case, you can construct SQL Server Maintenance Plans (there is a nice UI tool and wizard to help you with those) or you can script the backups yourself using the BACKUP command.
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/ms187658(v=SQL.90).aspx
These tools will give you enough flexibility to take backups without any server downtime and without affecting running applications. Furthermore, if any databases are in full recovery mode and you take transaction log backups, the transaction logs get truncated. This doesn't happen by simple copying the mdf files.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply