December 21, 2008 at 1:52 am
Hi,
I'm trying to write a trigger that will backup a database on creation, but i get this error
"Cannot perform a backup or restore operation within a transaction."
what options do i have if i want to automatically create a backup database when new DB is create?
THX
December 21, 2008 at 4:57 am
Why would you want to backup a database within a trigger?
You can manually fire the backup statement after you create the database OR if you've a script to create a database, the last part of the script can contain backup statement seperated by a GO.
You can create maintanence plans to do automated backups OR you can write your own jobs to do backups. Refer BOL.
December 21, 2008 at 5:05 am
I'm responsible for lots of SQL Server that have an ERP Program and this ERP program every few days creating new DB (companies) in the SQL.
i don't want to go to every company do a full backup for the new DB that created.
THX
December 21, 2008 at 5:20 am
i'm sure u're not physically going for db creation either. You can create your database from you location(provided the new location is on network and you've appropriate rights).
Taking a backup of a database after firing a script is hardly of any use. Since it doesnt contain any data, you dont have to worry abt losing data. you can definately re-create the database in case of disaster.
You can definately attach a script with ur db creation script that creates a scheduled job for taking the backup of the new database periodically.
December 21, 2008 at 5:32 am
yes i know.
but still what are my options of automatically backup new created database.(can't do it on the ERP that create the database)?
THX
December 21, 2008 at 5:33 am
Mad-Dog (12/21/2008)
I'm responsible for lots of SQL Server that have an ERP Program and this ERP program every few days creating new DB (companies) in the SQL.i don't want to go to every company do a full backup for the new DB that created.
THX
Are you talking about backing up the database once after it was created or about adding the database to a backup plan, so it will automatically be backed up on a daily (or any other interval) basis?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 21, 2008 at 5:44 am
I'm talking about backing it up once on creation automatically.
THX
December 21, 2008 at 5:44 am
this should help you.
http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx
Contains description of creating backup jobs through t-sql and ssms.
December 21, 2008 at 7:00 am
Mad-Dog (12/21/2008)
I'm talking about backing it up once on creation automatically.THX
Since the trigger is part of the transaction, and you can not backup a database inside a transaction, you can create a table that holds the names of databases that you should backup. In the trigger you just have to insert a record to this table. Then you can create a job that checks that table. When ever there are records in the table, you can run a backup to the database, that it's name is in the table, and then delete the record of the database that you've just backed up. I know that it will not backup the database at the same time that it was created, but you can control the job's interval and according to it's interval you can know how long you'll have to wait for the backup at the worth case scenario. An alternative is to create a job that queries sysbackupset table in msdb and sys.databases in master db to check if there are database that never had a backup. If it finds such databases, it will back it up.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 21, 2008 at 7:18 am
THX.
this was my thought too,but i was try to avoid the sql agent job for the check and backup.
i'll give it a try anyway.
December 21, 2008 at 10:49 am
Pradeep's point is well taken here: There is no point to backing up the database immediately after it is created, because it does not have any data yet. What you really want to do is to back it up after the ERP has loaded the new data into it, but there is no event/trigger for that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 11:20 am
RBarryYoung (12/21/2008)
Pradeep's point is well taken here: There is no point to backing up the database immediately after it is created, because it does not have any data yet. What you really want to do is to back it up after the ERP has loaded the new data into it, but there is no event/trigger for that.
Actually, that's not quite true. If the database is created in the full recovery model - until you actually backup the database for the first time, the transaction log is really in a pseudo simple recovery model.
So, if you have an automated process that backs up the transaction logs of all databases every x minutes (or differentials) - that task is going to fail as soon as it hits a new database that has not been backed up.
And finally, until that first backup is created - any transactions on that database are not recoverable. What would happen in the following scenario:
Automated weekly backups performed every Sunday night.
Automated daily differential backups (all databases).
Automated hourly transaction log backups (all databases in full recovery)
Database created Monday morning at 8:25am in full recovery model.
At 9:00am - the transaction log backup process will fail. Depending upon how it is written - it may just skip over the failure or not. Let's assume it skips over the failure (for now) - the same thing happens with the differential.
Now, we have a system issue on Thursday at 3:22pm and we need to restore the database created on Monday. Only, we can't restore anything but the original database that was created because we don't have a backup, a differential or any transaction logs that are valid.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2008 at 11:37 am
this is exactly one of my problem that i want to resolve by backing up newly created database on creation.
December 21, 2008 at 11:44 am
That's kinda what I thought - so, your choice is simple. You need to create a job that checks for newly created databases and, if found - back them up.
How often you run this job is going to depend upon how often you backup the transaction logs.
There really is no way you are going to get around creating a SQL Agent job. Trying to do this without creating a job may be possible, but the amount of work required - and the level of risk involved in implementing something is much higher than creating a job.
I don't understand the reluctance to creating a job. You already have SQL Agent jobs to backup the databases and transaction logs - why is another job such a problem?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2008 at 12:03 pm
Jeffrey Williams (12/21/2008)
RBarryYoung (12/21/2008)
Pradeep's point is well taken here: There is no point to backing up the database immediately after it is created, because it does not have any data yet. What you really want to do is to back it up after the ERP has loaded the new data into it, but there is no event/trigger for that.Actually, that's not quite true. If the database is created in the full recovery model - until you actually backup the database for the first time, the transaction log is really in a pseudo simple recovery model.
...
Good explanation and an excellent point Jeffrey, I had not thought of that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply