SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 BAT file to backup DB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raxbat
Yak Posting Veteran

52 Posts

Posted - 11/28/2009 :  10:21:08  Show Profile  Reply with Quote
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 - 11/28/2009 :  10:44:05  Show Profile  Reply with Quote
Create an SQL Script file.

i.e c:\sql_script.sql

USE master
GO
BACKUP 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 file

cd "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 file

USE master
GO
EXEC sp_addumpdevice '<disk/tape>', '<logical name of device>',
'c:\backup.bak';
GO
DROP DATABASE <Database>;
GO
RESTORE DATABASE <Database>
FROM DISK = 'c:\backup.bak'
GO

For more information on sp_addumpdevice goto http://msdn.microsoft.com/en-us/library/ms188409.aspx

AKH
Go to Top of Page

raxbat
Yak Posting Veteran

52 Posts

Posted - 11/28/2009 :  10:46:25  Show Profile  Reply with Quote
Great! Thank You ;)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/28/2009 :  10:51:37  Show Profile  Reply with Quote
What's the benefit of defining a device over just using a file?


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 11/28/2009 :  10:57:40  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page

raxbat
Yak Posting Veteran

52 Posts

Posted - 11/28/2009 :  11:06:28  Show Profile  Reply with Quote
Unfortunately I get this error after executing sql script:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re 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 master
GO
BACKUP DATABASE [incasso] TO DISK = N'C:\SQL Backup\ink.bak' WITH NOFORMAT, NOINIT, NAME = N'incasso', SKIP, NOREWIND, NOUNLOAD, STATS = 10


Where is the problem?

Thanx
Go to Top of Page

raxbat
Yak Posting Veteran

52 Posts

Posted - 11/29/2009 :  02:32:25  Show Profile  Reply with Quote
problem resolved! thanx!
Go to Top of Page

Govind16mayekar
Starting Member

India
1 Posts

Posted - 01/10/2013 :  02:27:55  Show Profile  Reply with Quote
Hey i am also facing same problem.whats the solution for this.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000