Backup of a db with t-sql script without sa login

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

Backup of a db with t-sql script without sa login

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2009-07-27 : 01:43:55

Hi..

I need T-SQL script to take the backup of a database... Important thing is i don't the sa credentials (Login-- Pwd)..Is it Possible..Please help..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 01:48:55

[code]CREATE PROCEDURE sp_Backup_Raj
as
BEGIN
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\PATH\'

Print 'Backup Done 10%'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
Print 'Backup Done 20%'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
Print 'Backup Done 30%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
Print 'Backup Done 40%'
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Backup Done 50%'
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
Print 'Backup Done 60%'
FETCH NEXT FROM db_cursor INTO @name
END
Print 'Backup Done 90%'
CLOSE db_cursor
DEALLOCATE db_cursor
Print 'Backup Done 100%'
END
[/code]

-------------------------
R..

raky
Aged Yak Warrior

767 Posts

Posted - 2009-07-27 : 02:04:36

Hmmm I had already the above script with me...But i need to have the backup onto my system instead of on the server...because i can't create the folder ( Path where backup file is placed ) at the server...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 02:16:50

Hi

You Have to give like this...
SET @path = '\\YourMachineName\Folder\FileName'
Instead of this........
SET @path = 'C:\PATH\'

-------------------------
R..

raky
Aged Yak Warrior

767 Posts

Posted - 2009-07-27 : 14:21:29

thanx for ur reply unfortunately it didn't helped me....hmmmm

manidipa_das
Starting Member

1 Post

Posted - 2009-07-28 : 12:53:44

BACKUP <database name> TO DISK='path'

e.g.>

BACKUP Employee TO DISK='C:\Empl.bak'

Manidipa Das

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources