Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
raxbat
Yak Posting Veteran
52 Posts |
Posted - 2009-11-28 : 10:21:08
|
Hello all!Maybe somebody have bat file to backup SQL DB from command line?Actually I have SQL EXPRESS 2005 and I have to schedule backup process. I supose it can be done using BAT file.Look after Your help, exprets!Thanx a lot! :) |
|
akholbert
Starting Member
4 Posts |
Posted - 2009-11-28 : 10:44:05
|
Create an SQL Script file.i.e c:\sql_script.sqlUSE masterGOBACKUP DATABASE [<Database>] TO DISK = N'c:\backup.bak' WITH NOFORMAT, NOINIT, NAME = N'Backup Name', SKIP, NOREWIND, NOUNLOAD, STATS = 10-------------------------------------------------Then add the following to the batch filecd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"sqlcmd -E -S .\SQLEXPRESS -i c:\sql_script.sql-----------------------------------------------------------------------To Restore the backup add the following to a batch fileUSE masterGOEXEC sp_addumpdevice '<disk/tape>', '<logical name of device>', 'c:\backup.bak';GODROP DATABASE <Database>;GORESTORE DATABASE <Database> FROM DISK = 'c:\backup.bak'GOFor more information on sp_addumpdevice goto http://msdn.microsoft.com/en-us/library/ms188409.aspxAKH |
|
|
raxbat
Yak Posting Veteran
52 Posts |
Posted - 2009-11-28 : 10:46:25
|
Great! Thank You ;) |
|
|
X002548
Not Just a Number
15586 Posts |
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-28 : 10:57:40
|
i suspect it's just legacy -- the way akh is used to doing it. it took me a long time to stop doing that lol |
|
|
raxbat
Yak Posting Veteran
52 Posts |
Posted - 2009-11-28 : 11:06:28
|
Unfortunately I get this error after executing sql script:HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.my script looks like:USE masterGOBACKUP DATABASE [incasso] TO DISK = N'C:\SQL Backup\ink.bak' WITH NOFORMAT, NOINIT, NAME = N'incasso', SKIP, NOREWIND, NOUNLOAD, STATS = 10Where is the problem?Thanx |
|
|
raxbat
Yak Posting Veteran
52 Posts |
Posted - 2009-11-29 : 02:32:25
|
problem resolved! thanx! |
|
|
Govind16mayekar
Starting Member
1 Post |
Posted - 2013-01-10 : 02:27:55
|
Hey i am also facing same problem.whats the solution for this. |
|
|
|
|
|
|
|