Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Back up scripts Expand / Collapse
Author
Message
Posted Wednesday, January 21, 2009 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 2, 2009 8:55 AM
Points: 33, Visits: 139
Hi guyz! I want to create scripts that can do a backup on a database on regular basis. How can i do that? Please help. The database is in sql server 2005 express edition.
Post #640749
Posted Wednesday, January 21, 2009 8:12 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:44 AM
Points: 770, Visits: 1,597
How about creating a job on SQL agent and schedule it to run on a regular basics :D
Post #640768
Posted Wednesday, January 21, 2009 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 2, 2009 8:55 AM
Points: 33, Visits: 139
Thanks Crazy Man, the problem in this case is, i want to do it with express edition. In this case, there is no sql agent. So how do i create the scripts to do the manual back up, then i can get alerts from the agent on when back ups have been done adn when they have not. Please help.
Post #640795
Posted Wednesday, January 21, 2009 1:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
You can use the windows scheduler and sqlcmd to do scheduled backups. It's not as easy as SQLAgent, but it does work. For sqlcmd, you'll want to use the -i switch to specify an input file. That query file would then contain all the backup scripts.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #641104
Posted Friday, January 23, 2009 1:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 2, 2009 8:55 AM
Points: 33, Visits: 139
Hi. Thanks for your reply, but please explain to me how the scheduler and sqlcmd work. I have gotten an understanding of how the scheduler works, but in this case i need to have the script execute like at a given time on daily basis. So how do i do it in the scheduler using scheduler?
Post #642325
Posted Friday, January 23, 2009 9:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 11, 2014 4:21 AM
Points: 97, Visits: 392
See attached for SQLCMD usage..

SQLCMD

Have an input sql script - i.e. your backup database script. Put the sqlcmd command into a batch file and feed the batch file into scheduler


My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access
Post #642528
Posted Wednesday, May 8, 2013 1:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 10:06 PM
Points: 16, Visits: 73
Hi,
in your environment having test servers ? then create one linked server for that 2005 server(express edition) and create job for taking backup in your test servers.
note: in backup command you should have mention the path like '\\your 2005express edition location path'
script: create one job by using the below script in your test server and make sure your mentioned path like '\\ D:\ yoursql server2005 express edition\backup\......'

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

-- specify database backup directory
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 NOT IN ('master','model','msdb','tempdb') -- exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

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

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor


subahan munthamadugu
Post #1450433
Posted Wednesday, May 8, 2013 8:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,404, Visits: 36,849
Please note: 4 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1450590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse