Solved

Sql2008express backup: drag and drop backup

Posted on 2014-10-14
4
140 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 45

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 24

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
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 Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

744 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

12 Experts available now in Live!

Get 1:1 Help Now