Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Database Backup through Batch File
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunny_10
Yak Posting Veteran

72 Posts

Posted - 03/07/2014 :  01:29:10  Show Profile  Reply with Quote
hi

Below is the stored procedure & then is the Batch file . Batch file is executing but it is not creating any backup

Create PROCEDURE [dbo].[Sp_Backup]
-- Add the parameters for the stored procedure here
@path VARCHAR(256) ,
@dbname varchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- specify database backup directory
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

--SET @path = 'C:\Backup\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name IN (@dbname)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @dbname + '_' + @fileDate + '.BAK'
BACKUP DATABASE @dbname TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
END


sqlcmd -S ABC -Q "EXEC sp_Backup @dbname='TEST', @path='D:\Backup\'"

Thanks

djj55
Constraint Violating Yak Guru

USA
352 Posts

Posted - 03/07/2014 :  09:46:41  Show Profile  Reply with Quote
The WHERE name IN (@dbname) will not work as you think.

If only one name is being used most of your logic can go away.

Create a dynamic SQL statement for the BACKUP and execute that.

djj
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 03/08/2014 :  03:12:12  Show Profile  Visit jackv's Homepage  Reply with Quote
I assume the purpose of the procedure is to complete one database backup. In that case , why are you using the the CURSOR function. You can delete all that logic.
If , on the other hand , you need to backup all databases - use : http://www.sqlserver-dba.com/2008/08/backup-all-data.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next 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.12 seconds. Powered By: Snitz Forums 2000