I've been too lax with performing DB backups on our internal servers.
Is there a simple command line program that I can use to backup certain databases in SQL Server 2005? Or is there a simple VBScript?
views:
16525answers:
5
+4
A:
Schedule the following to backup all Databases:
Use Master
Declare @ToExecute VarChar(8000)
Select @ToExecute = Coalesce(@ToExecute + 'Backup Database ' + [Name] + ' To Disk = ''D:\Backups\Databases\' + [Name] + '.bak'' With Format;' + char(13),'')
From
Master..Sysdatabases
Where
[Name] Not In ('tempdb')
and databasepropertyex ([Name],'Status') = 'online'
Execute(@ToExecute)
There are also more details on my blog: how to Automate SQL Server Express Backups.
GateKiller
2008-09-23 18:09:48
Can you elaborate on how to schedule?
Frank Krueger
2008-09-23 18:11:30
Please read my Blog Post. It details everything you need to know.
GateKiller
2008-09-23 18:16:30
I think the question asks for something that runs *outside* of SQL Server.
bzlm
2009-09-16 15:06:51
@bzlm, Like I said. My blog has details on how to do this outside of SQL Server :)
GateKiller
2009-09-17 15:16:54
This script doesn't seem to work...
Marnix van Valen
2009-09-22 14:46:47
@Marnix: Could you post any error messages and I might be able to help.
GateKiller
2009-09-24 13:20:04
A:
If you can find the DB files... "cp DBFiles backup/"
Almost for sure not advisable in most cases, but it's simple as all getup.
BCS
2008-09-23 18:14:48
Do NOT do this if SQL Server is running, even if nothing's using it.
Craig Trader
2008-09-23 18:17:53
The odds of using this method to successfully backup and restore a database are very slim. It will only work IF, during both the backup and restore: no SQL Server processes are running, you identify and copy ALL of the binary files involved, you're running EXACTLY the same version and patch-level of SQL Server (and/or Windows). The backup file format is designed to be cross-version portable; the binary database files are NOT. This is true of ALL databases, not just SQL Server. Just don't do it. REALLY. DO NOT DO THIS.
Craig Trader
2009-09-23 15:33:01
This answer is 98.7% a joke. OTOH if you will only be restoring to the exact same setup (a.k.a restoring/reverting) and it can be shut down (a.k.a. it's a local dev server) and probably a few other things...
BCS
2009-09-23 16:43:08
+14
A:
To backup a single database from the command line, use osql or sqlcmd.
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
-E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\db.bak' WITH FORMAT"
You'll also want to read the documentation on BACKUP and RESTORE and general procedures.
Craig Trader
2008-09-23 18:15:38
There's a good script to backup all user databases in one go here: http://www.mssqltips.com/tip.asp?tip=1070
Marnix van Valen
2009-09-22 14:47:21
+1
A:
The mentioned command line utility osql is replaced by sqlcmd in Microsoft SQL Server 2005.
Mackaaij
2008-09-23 18:34:14
+2
A:
I user ExpressMaint.
To backup all user databases i do:
C:>ExpressMaint.exe -S (local)\sqlexpress -D ALL_USER -T DB -BU HOURS -BV 1 -B c:\backupdir\ -DS
Meixger
2009-02-25 16:14:15