Solved

Sql2008express backup: drag and drop backup

Posted on 2014-10-14
4
144 Views
Last Modified: 2014-10-20
Hell all,
New sql express database. A week old. Right now, to back it up the employee makes sure that no one is logged in then goes into the database server and stops all the services related to the database. She then copies the data folder that contains the databases to another drive which she takes off site and then restarts the services so others can log into the database. Is this method it back up okay? I know that backing up to .BAK files through the sql management tool Is the preferred way to back up but Symantec backup exec is not installed yet and no backup scripts have been written yet. For right now drag-and-drop is the backup method I just want to make sure that in case of an emergency I can restore that drag-and-drop database which is not in the .bak format.
Thanks
Dt
0
Comment
Question by:dronethought
4 Comments
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 167 total points
ID: 40379795
If you are asking is this methodology better than anything it is, but if it is the best option it is not.

I would recommend that you do Backup from the SSMS and then take those .bak files to off site location. f the database is fresh and small in size you can do it in few minutes and with that you can easily restore.

With file copy backup you need to make sure there a few more steps required in order to have database up and running...

I think it will be better if you do the backup by using the SSMS instead of file copy. Also, you can use the same file and then by restore you  can chose at which point in time you can restore...
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 167 total points
ID: 40379798
It's a very rudimental way for backups. Of course it works but need a downtime period. If the company and the users can afford that downtime then it's ok.
Now tell me, what's the schedule for this kind of backup?
Imagine that in the middle of the day, really few minutes before you perform this backup, someone delete the database by mistake (or the disk where are the files just get corrupted). What much do you lost? It's important those losses? Do you need to repeat all the work from last backup files?
0
 
LVL 25

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 166 total points
ID: 40379841
It could be automated to perform backups at specified time and the .bak files can be backed up to a remote device/computer for offsite storage.  This can be done by creating a script to perform the backups and this script needs to be schedule in Scheduled Tasks.  Below is a sample script to perform backups:

REM -- Start of Script
@echo off
setlocal
Rem Set current date in form of YYYY-MM-DD
for /F "tokens=1,2,3,4 delims=/" %%A in ('Date /T') do set PresentDate=%%D-%%B-%%C

Rem Backup users databases only
Set UserDBs = c:\userdbs.txt
sqlcmd -E -S localhost -h-1 -W -Q "Set nocount on; select name from master.dbo.system where [Name] NOT in ('master','model','msdb','tempdb')"  > %UserDBs%

For /F "tokens=*" %%I in (%UserDBs%) Do (
sqlCMD -E -S localhost -Q "backup database [%%I] to disk='c:\sqlbackups\Backup-%PresentDate%-%%I.bak'" }

Rem -- End of script


Save the script above to a folder, create folder c:\sqlbackups, schedule the script to run as administrator.
0
 
LVL 2

Author Closing Comment

by:dronethought
ID: 40393121
Excellent!!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now