August 10, 2010 at 5:51 am
It occurs to me that you need to start this over from scratch. Forget about your desired end result for a moment. What is the task(s) you were given to do in regards to this database?
List out the original statement of work given to you by your boss. Break it down into as many component parts as you can. Post both here. And we'll advise you from there. And if you are having specific problems / errors, please also post details about them.
August 10, 2010 at 6:03 am
My task.
-Create daily backups of the changes made that day
-Apply those changes to a remote database
August 10, 2010 at 6:14 am
throwing in my two cents here; if you really want to capture a full trace of everything, you can simply turn on C2 auditing; C2 auditing is actually just another trace...it just has more events, and of course would capture everything form all databases, not jsut the one you were thinking about. But it's still "just a trace".
you'll want to come up with a copy/zip/move strategy for the trace files, as they are not part of any sql backup.
if you enable the C2 auditing for a SQL 2005/08, you have to stop and start the services, and it makes trace #1 the C2 trace and moves the "default" DDL trace to be traceID #2;
if you needed to, you could even reverse engineer the C2 trace and script it out, add a filter for it to capture just one database Id, and add that script back as a job which starts when SQL starts.
Lowell
August 10, 2010 at 6:17 am
ODPOA (8/10/2010)
My task.-Create daily backups of the changes made that day
-Apply those changes to a remote database
backup the database, then restore the backup on the remote server. that is the best and the method i would recommended. don't apply daily changes, simply restore the latest copy.
Lowell
August 10, 2010 at 6:40 am
ODPOA (8/10/2010)
My task.-Create daily backups of the changes made that day
-Apply those changes to a remote database
Here's my two cents worth:
1. You're running on MSDE.
2. As Gail pointed out, this doesn't run jobs.
3. As Gail pointed out, you are limited in the size of the db to 2 GB. (I think she said 4, but it's 2.)
With this in mind, a 2gb backup is pretty small. So, I would do this:
1. Determine the SQL command to perform a full backup of the database to the location where you want it backed up to. (I'd suggest a shared folder on the local drive.)
2. Utilizing osql, write a batch file to perform this backup.
3. Use the Scheduled Tasks feature of the OS to schedule this backup.
If the remote server is also MSDE:
1. Determine the command to restore a database from the remote shared folder.
2. Utilizing osql, write a batch file to perform the restore.
3. Use the Scheduled Tasks feature of the OS to schedule this restore about 15 minutes after the backup occurred.
(Optionally, you can copy the file from the remote shared folder to a local folder, and perform the restore from there.)
If the remote server is a standard/enterprise edition of sql (NOT MSDE):
1. Determine the sql command to restore the database from the remote shared folder.
2. Schedule this command as a job to run 15 minutes after the backup occurred.
Same option applies. This would take an additional step in the job to copy the file.
I concur that you do NOT need to try to capture just the changes and re-apply them. This would be more hassle than it's worth.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 10, 2010 at 6:51 am
ODPOA (8/10/2010)
My task.-Create daily backups of the changes made that day
-Apply those changes to a remote database
Okay, then we're going about this the wrong way.
The engine you're using is a crippled copy of SQL 2000. It runs in the background for use with minor stuff. It doesn't have Enterprise Manager or a GUI interface to make your job easy. And it doesn't have the ability, so far as I can tell, to do Incremental or Differential backups. In fact, I'm not even sure it can do Log Shipping.
You're going to have to do this the hard way, probably through a batch file scheduled as a windows task, and you're going to have to do full backups each night.
This link http://support.microsoft.com/kb/325003 will tell you the commands you need to put in the batch file (I'm assuming you know how to write those). You backup the database, use RoboCopy or XCopy or something to move the backup file to your new server, and then I'm sure there's a similar command for restoring the database.
But what do you intend to do with the restored database once you have it?
August 10, 2010 at 8:45 am
Once you've done the full backup, use a zip utility to compress the backup file. With our databases, a 6GB back compresses to under 400MB. That should reduce the impact of your restricted bandwidth. Once the compressed backup is copied to teh remote location, unzip it and restore.
August 10, 2010 at 5:21 pm
Brandie Tarvin (8/10/2010)
The engine you're using is a crippled copy of SQL 2000. It runs in the background for use with minor stuff. It doesn't have Enterprise Manager or a GUI interface to make your job easy. And it doesn't have the ability, so far as I can tell, to do Incremental or Differential backups. In fact, I'm not even sure it can do Log Shipping.You're going to have to do this the hard way, probably through a batch file scheduled as a windows task, and you're going to have to do full backups each night.
Although it can't do automated log shipping (in SQL 2000 that requires Enterprise Edition, so neither Standard Edition nor MSDE has it) if you set the recovery model to full (using OSQL, as there are no interactive tools) you can then either use a windows scheduled job to use OSQL to do log backups and ship them over to the remote machine, or use SQL SQL Server Agent to run a scheduled job to do log backups (with a JScript/Active-X or other batch jobstep to ship the backup) and then have something on the remote machine that uses OSQL or SQL Server Agent to apply the log files. (Contrary to popular myth, SQL Agent is included with MSDE: see the URL http://support.microsoft.com/kb/325003 which Brandie quoted a few lines below the above quoted text; it recommends using SQL Server Agent for MSDE backup.) Another useful link (http://support.microsoft.com/kb/241397/EN-US/) actually gives some examples of using OSQL to create backup jobs for SQL Agent to run specifically for MSDE users. It's pretty straightforward to create the corresponding RESTORE commands.
Incidentally, I would be very surprised if MSDE couldn't do incremental backups; but I haven't actually tried it with MSDE so can't be certain. Using incremental backups would probably mean less data had to be shipped than using log backups, so if it works it's a better method than shipping log backups (but if the database is running on an AD domain controller that has system state backups using NTBackup a full database backup is going to be needed after each NTBackup, because NTBackup will probably break the chain without providing a useful accessible database backup).
Whatever sort of backup is used, compressing it (you can move the basckup into a compressed (zipped) folder and then ship the folder, so need no compression tools that don't come automatically with windows) will save a lot of transmission time.
Tom
Viewing 8 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply