backupscheduled-taskssql-server-2000
I would like to create a backup of four databases on one SQL Server 2000 instance at the exact same moment in time.
How can I achieve it?
Creating different jobs with the same schedule date and time does apparently produces a difference in the moment when the backup is taken.
As you can see in this table, there is no conflict between Full backup and Log backup. They can occur at same time, and, when log backup completes, log will not be cleaned until full backup (or diff backup) completes.
Not all administrative tasks are allowed to run concurrently. In the table below, a black circle indicates two operations that cannot run in a database at the same time.
References:
As noted in comments, the tables do not have clustered indexes therefore you can not logically defragment them (rebuild indexes) completely. That is, the indexes are defragged but not the actual data.
Also, DELETEs from heaps (a table without a clustered index) do not deallocate space. See http://msdn.microsoft.com/en-us/library/ms189245(v=sql.105).aspx for more
Finally, not having clustered indexes is generally a bad thing except for staging tables that are truncated. However, GUIDs are not good clustering keys
Solutions?
Best Answer
You will need to have the Full Recovery Model enabled on all four databases.
Now you will issue a full backup on all four databases, let's say ten minutes before the point in time you require.
Now you will do a transaction log backup of each database, at a point in time immediately after the exact moment you need.
On the other end, you will restore each full backup
WITH NORECOVERY, and then transaction log backup, with theRESTORE LOGWITH STOPATswitch to specify the exact moment in time you need, as a DATETIME.Remember to
RESTORE WITH RECOVERYat the end to bring them into active use.