Automated backup and maintenance strategy

[CraigNicholson]

I would like to know if SourceGear or anyone here has any ideas on an automated backup and maintenance strategy for the Vault database.

I notice that the Recovery Model for the sgvault database is by default set to Simple meaning that only full backups are possible.

I have tried to setup an automated routine where I actually force the DB into single-user mode, however the Vault user sometimes manages to login between switching to single-user mode and the start of my Maintenance Plan execution, causing the plan to fail.

Anyone have any good solutions or scripts? I am not a DBA and don't want to be :p

[jclausius]

I would like to know if SourceGear or anyone here has any ideas on an automated backup and maintenance strategy for the Vault database.

The answer here depends on how much time or money do you want to spend.

If you would like to spend very little time, but invest money into software, there are plenty of tools you can use to backup the vault database. http://www.google.com/search?hl=en&ie=U ... %22+backup

If you want to spend some time, and little money, you could write a script using BACKUP DATABASE sgvault TO DISK = 'c:\temp\vault.bak', install Windows Scheduler, and schedule the script, in conjunction with osql.exe or isql.exe to run on a regular basis. (see the at.exe command). You could then backup the database dump to tertiary storage.

I have tried to setup an automated routine where I actually force the DB into single-user mode, however the Vault user sometimes manages to login between switching to single-user mode and the start of my Maintenance Plan execution, causing the plan to fail.

The Vault Server has its own stored proc to kick people off of the Vault database before running a backup in the Admin Tool. After setting the DB to single user mode, try running EXEC master.dbo.spkillusers N'sgvault'.

I notice that the Recovery Model for the sgvault database is by default set to Simple meaning that only full backups are possible.

<snip>

I am not a DBA and don't want to be :p

As you mentioned, you don't want to be a DBA. It is for that very reason, the Vault Server is configured with Simple Recovery. If you would like to change the backup strategy, see ALTER DATABASE SET RECOVERY <option> for more information.

[CraigNicholson]

If you want to spend some time, and little money, you could write a script using BACKUP DATABASE sgvault TO DISK = 'c:\temp\vault.bak', install Windows Scheduler, and schedule the script, in conjunction with osql.exe or isql.exe to run on a regular basis. (see the at.exe command). You could then backup the database dump to tertiary storage.

Well I am currently doing just that and thats where it is failing. I am trying to run the SQL Server Maintenance Plans.

Currently I have defined a maintenance plan called "sgvault" and instead of letting SQL Agent run each job at a configured time, I manage it using the AT scheduler so that each step always flows in order whilst enabling me to do other file based things like compression and encryption of the backups. An example of my script execution would result in the following running:

Code: Select all

OSQL -E -Q "ALTER DATABASE sgvault SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
SQLMAINT -PlanName sgvault -WriteHistory -CkDBRepair
SQLMAINT -PlanName sgvault -WriteHistory -RebldIdx 100 -RmUnusedSpace 50 10
SQLMAINT -PlanName sgvault -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"
OSQL -E -Q "ALTER DATABASE sgvault SET MULTI_USER WITH ROLLBACK IMMEDIATE"

Now generally it runs ok, but occasionally the sgvaultuser sneaks into the DB between maintenance steps.

I have tried to setup an automated routine where I actually force the DB into single-user mode, however the Vault user sometimes manages to login between switching to single-user mode and the start of my Maintenance Plan execution, causing the plan to fail.

The Vault Server has its own stored proc to kick people off of the Vault database before running a backup in the Admin Tool. After setting the DB to single user mode, try running EXEC master.dbo.spkillusers N'sgvault'.

Actually when putting the database into single-user mode its quite easy to kick off the active users by executing

Code: Select all

ALTER DATABASE sgvault SET SINGLE_USER WITH ROLLBACK IMMEDIATE

which will force the users out immediately. What I really need is a way to prevent the user from logging in while my maintenance tasks execute.

I was thinking of changing the sgvaultuser's password temporarily or trying to lock that specific user out, but unfortunately I am unable to do so as I am not sure what the password is and where it is configured.

I think part of the problem resides in the fact that the sgvaultuser account is a member of db_owner. This allows the user to login when under single-user mode if I'm not mistaken. I think that if a non-dbo_owner role was configured by default and the sgvaultuser made a member of that role, it would be easier. Any suggestions or comments?

[jclausius]

i see your problem now.

unfortunately db_owner does not control who can login / use a database. it controls who actually owns the database objects, and allows them to grant / deny privileges on those db objects.


a couple of different routes you could take. others may have different strategies. here are my suggestions:

1) remove the sgvaultuser from the database before running the script, and then re-add the user after your maintenance plan has succeeded.

Code: Select all

OSQL -E -Q "USE sgvault; sp_revokedbaccess N'sgvaultuser'; USE master; ALTER DATABASE sgvault SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
...  
OSQL -E -Q "USE sgvault; sp_grantdbaccess N'sgvaultuser'; USE master; ALTER DATABASE sgvault SET MULTI_USER WITH ROLLBACK IMMEDIATE"

2) (this is not as elegant...) run everything from within one database session.

• convert the sqlmaint commands to the appropriate DBCC commands.
• place all of them into a text file.
• within the sql script, after setting the database into single user mode, run EXEC master.dbo.spkillusers N'sgvault'
• schedule just one item - osql w/ the -i flag.

[CraigNicholson]

Thanks for the ideas. I think I might try the revoke access option in my backup run tomorrow.

Tonight I am experimenting using:

Code: Select all

IISRESET /STOP
OSQL -E -Q "ALTER DATABASE sgvault SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
SQLMAINT -PlanName sgvault -WriteHistory -CkDBRepair
SQLMAINT -PlanName sgvault -WriteHistory -RebldIdx 100 -RmUnusedSpace 50 10
SQLMAINT -PlanName sgvault -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"
OSQL -E -Q "ALTER DATABASE sgvault SET MULTI_USER WITH ROLLBACK IMMEDIATE"
IISRESET /START

This will effectively take Vault offline for the time period of the maintenance. Ugly, but an immediate solution.

[Guest]

I am looking for the same thing, did this work out for you? I don't mind taking Vault down. I will just run the script at midnight or something like that.

Matt Fleming

[CraigNicholson]

I am looking for the same thing, did this work out for you? I don't mind taking Vault down. I will just run the script at midnight or something like that.

It sure does work for me. I am thinking of posting a bit of a HOWTO post here to help everyone else out. Watch this space.