June 11, 2004 at 12:25 am
I have a unique problem that I hope someone can help me solve. I have a nightmare application that creates and uses thousands of databases on my server. The company that created it sets up the instance to use the -T3608 Trace Flag in an attempt to control memory usage.
They have also created an application to backup "MOST" of the databases. This application stops the SQL instance to free up memory and then backs up 500 databases and then stops the instance again....... over and over until it is done backing up the databases. Then it stops and restarts the instance one last time.
My problem is that hundreds of databases are NOT getting backed up. I can write a script to back these databases up but when you access the database it loads it into memory which is why they do this in chunks of 500 at a time. I am wondering if anyone has any ideas on how I might kind of duplicate the same sort of control for the other databases I need to make sure to backup.
It is very late and I have had a LONG day so please forgive me if I am not very clear.
Thanks
Tom
June 11, 2004 at 2:04 am
What about setting the 'autoclose' parameter on all the databases prior to the backups, then using something like
exec sp_msforeachdb 'if db_name() <> ''tempdb'' backup database [?] to disk=''d:\backups\?.bak'' with init'
Then turning autoclose off for those databases.
Otherwise another idea is as followings: Note: This may work and you would need to look at downtime for restores etc. Also this would need carefull testing etc. to ensure it actually works.
But I was thinking that if you already stop the sql instance, you could then backup (or copy to a different location) all the .mdf and .ldf files as all the files are closed and checkpointed at that point.
Then if you needed to restore the db you should be able to stop the server, and replace the orgional files. But this would mean that the whole server was down for the duration for the restore.
Steven
June 11, 2004 at 8:35 am
Steven's advice is good. Stop SQL Server and copy out your Data directory. I restored the servers twice using this technique. If the system databases are in this directory too and you are restoring to the same NTFS locations the restore is as simple as re-install SQL Server if needed, stop it, rename original Data directory, put your old Data directory (or directories) back, you are in business.
Another way may be use a maintenance plan that has an option to backup all databases. I am not sure of a limit on the number of databases. Does somebody know?
The preferred solution will be to create and test a DRP (Disaster Recovery Plan) in the presence of your manager. When your manager realizes that the application design leads to the very expensive mainenance maybe he will replace the application vendor
Regards,Yelena Varsha
June 11, 2004 at 8:39 am
I have had problems with maintenance plans when memory is short on the server, hence the first solution of backup up all databases using t-sql (of course you need to write some housekeeping code to remove the old backups as well)
exec sp_msforeachdb 'if db_name() <> ''tempdb'' backup database [?] to disk=''d:\backups\?.bak'' with init'
Steven
June 11, 2004 at 9:55 am
I never knew about this stored procedure before. It might come in kind of handy in the future.
As far as the timing etc. I have some questions. If I use the sp_msforeachdb procedure and have it set all the databases to AutoClose How long would it take before a 1000 or so databases closed? And then if I run the example as shown above how quickly after a database is backed up would it close?
Lets say I have 10,000 databases and they are small and backup quickly. If they take too much time to close I could end up with too many databases open at the same time and run into problems.
Thanks
Tom
June 11, 2004 at 10:03 am
You don't need the autoclose, I only mentioned it because you said auto the lack of resources, and that closes the db down after the last connection ends that uses it.
the for eachdb procedure runs through each one in turn, hence you shouldn't get many backing up together.
Testing would be the best solution (hopefully on a test server)
Steven
June 11, 2004 at 10:16 am
My main problem is that without autoclose or something to unload the servers or stop and restart the instance, the server would crash or at least the backups would fail.
I had previously written a script to automatically backup all databases. It worked great until it was trying to work with a few thousand databases. As each database is called for backup it loads into memory until bad things happen.
Thanks for your responses
Tom
June 11, 2004 at 3:35 pm
As you can chain sql commands together with a ;
You can add other code along with the database backup eg setting the autoclose, hence doing it one db at a time
Steven
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply