• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Sql2008express backup: drag and drop backup

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
dronethought
Asked:
dronethought
3 Solutions
 
Haris DjulicCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mohammed KhawajaManager - Infrastructure: Information TechnologyCommented:
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
 
dronethoughtAuthor Commented:
Excellent!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now