January 5, 2005 at 12:26 pm
everyday new database will create application at 12:00am name like XX_20050105_001 and date will change everday. I want create back schedule around 5:00am and after that i want take transaction log backup and night 11:59pm i want take again full backup. i don't know how to automate ,anybody help me
January 6, 2005 at 8:08 am
Refer to the BOL, use the index tab and enter BACKUP DATABASE. That will explain how to write the backup script.
Then in Enterprise Manager, expand down to SQL Server Agent> Jobs. Right click, select New Job and fill out the information. This will allow you to schedule the job for the day(s) and time(s) you want the job to run.
-SQLBill
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
January 6, 2005 at 2:57 pm
Are you saying that your application is creating a new database every day? If so, thats a lot of databases! If this is an in house application, you really should look at your data model and see if you can change that!
However, if this is the case, you can write a script that will pull the database names from SQL and back them up.
Here's just a piece of a script that should get you going...
DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases
WHERE name not in ('tempdb','pubs', 'master','msdb','Northwind', 'model') ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @BKExec = 'BACKUP DATABASE ' + @DBname + ' TO DISK = ' + '''' + @BKDevice + '''' + ' WITH NOINIT, STATS=25'
Note that you will need to close the loops, ifs, etc.
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply