Solved

Sql2008express backup: drag and drop backup

Posted on 2014-10-14
4
154 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 49

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
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…

680 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