how to programmatically backup a DB? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

how to programmatically backup a DB?

Author

Topic

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-04 : 08:21:08

I need to programmatically backup a database in SQL Server Express. I actually also need to programmatically restore it from a backup file. How can I do this programmatically? I know how to do simple ADO commands for simple db operations, but backup and restore sound like "meta" commands to me, and I don't know where to begin from.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-04 : 08:42:24

Have a look at
http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html
http://www.nigelrivett.net/SQLAdmin/s_RestoreLatestBackup.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-04-04 : 08:50:49

Hi kskartsiounis, Welcome to SQL Team!

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Planning+for+Fully+Automated+Backup

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 09:12:29

Kris, the search terms are not highlighted but this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Planning

Madhivanan

Failing to plan is Planning to fail

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-04 : 10:52:45

I tried the stored procedure from the nigelrivett link. I created the Admin database and the DatabaseBackup table. When I execute the procedure (from within the Management Studio Express), I get:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Msg 3201, Level 16, State 1, Procedure s_BackupAllDatabases, Line 143
Cannot open backup device 'C:\\backups\\master_Full_20060404_174756.bak'. Operating system error 3(error not found).
[...] Etc.

(1 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2006-04-04 : 11:04:02

"the search terms are not highlighted"

How annoying! Turns out there were two spaces between the words in that topic ...

... so the link should now work: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Planning+for+Fully+Automated+Backup

"SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server"

You'll need to get xp_cmdshell made available to your login to get it to work. Pretty much anything of this ilk is going to need that XP I'm afraid

Kristen

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-04 : 11:16:24

I see on the web that Surface Area Configuration is a functionality reserved for SQL Server, not SQL Server Express. Is it possible that the nigelrivett stored procedure cannot, after all, be executed on SQL Server Express?..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 13:25:25

You don't need to use the Surface Area Configuration tool as you can enable it with sp_configure. I did it yesterday on a 2005 box.

EXEC sp_configure 'xp_cmdshell, 1
RECONFIGURE

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 13:28:24

You are going to need to change Nigel's code a little to make it work on 2005. Instead of:

select name from master..sysdatabases

you'll need:

select [name] from master.sys.databases

Tara Kizer
aka tduggan

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-05 : 02:22:06

To Tara:

Thanks for your replies. I assume you suggested running a small stored procedure with the sp_configure code. Anyway, I did that, namely:

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

and got that:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

It would be a lie if I said I know what is going on :)

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-05 : 03:38:16

Found a solution. I create a store procedure with this one line:

BACKUP DATABASE ATEasy_Test_DB to disk = 'c:\Projects\Toy.bak'

It works! So much happiness for such a trivial accomplishment :)

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-05 : 03:49:23

Before I get too happy, is there a
stored procedure one-liner for a
database restore operation?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-04-05 : 03:58:57

Maybe RESTORE DATABASE

Do you have some documentation there ? The syntax will be there.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

kskartsiounis
Starting Member

7 Posts

Posted - 2006-04-05 : 04:45:36

Woe to me.. I cannot store the backup stored-procedure under the database of interest in SQL Server Express management studio.. I choose "New Procedure...", an edit box opens, I can type, I can execute the contents, but when it's time to save, it saves the code to a file and I cannot see my stored procedure under "Stored Procedures" for my database. And if it does not list the SP, how can I invoke it programmatically?..

-K

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-05 : 06:42:26

Are you creating a stored procedure or just typing in the code?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-05 : 12:31:24

quote:
Originally posted by kskartsiounis

To Tara:

Thanks for your replies. I assume you suggested running a small stored procedure with the sp_configure code. Anyway, I did that, namely:

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

and got that:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.

It would be a lie if I said I know what is going on :)

You have to enable advanced options first.

EXEC sp_configure 'show advanced options', 1

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources