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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 BAT file to backup DB

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.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 - 2009-11-28 : 10:46:25
Great! Thank You ;)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-28 : 10:51:37
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

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
Go to Top of Page

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 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 - 2009-11-29 : 02:32:25
problem resolved! thanx!
Go to Top of Page

Govind16mayekar
Starting Member

1 Post

Posted - 2013-01-10 : 02:27:55
Hey i am also facing same problem.whats the solution for this.
Go to Top of Page
   

- Advertisement -