[sims] DBAttach Script

[fiza]

When we ran SIMS on Server 2003 and SQL 2008 Express I used the following to backup the DB.

 

"F:\Microsoft SQL Server\MSSQL10_50.SIMS2008\MSSQL\Binn\dbattach.exe" \BACKUP /SERVER="KCN-SVR-06\SIMS2008" /USER="backup" /PASSWORD="xxxxxxxxx." /DATABASE="sims" /AUTO /PATH="c:\simsbackup\sims.bak

 

I have now rebuilt the server to 2008 R2 and SQL 2008 R2 Full. SIMS is working but when I try to use the script I just get the DBattach dialogue box up with the database field filled in and the username/password filled in but it doesnt actually execute and make a backup. If I click connect it says "login failed for user : backup". I know user "backup" exists as I have logged into SIMS with the user account.

 

If i replace the user "backup" with the sa credentials and run the script it still comes up with the DBAttach dialogue box but this time I am able to connect to the DB by clicking "connect" but it does nothing else.

 

Previously the script would just run and backup the DB without showing the DBattach dialogue box and using the user called "backup".

 

Have I missed something or done something wrong?

[Mcshammer_dj]

why is there a " between Path= and C:\simsbackup\sims.bak

 

Actually why are there speech marks around all the entires??

 

dbattach /backup /server=somepr-dc-01\sims2008 /database=sims /user=username /password=password /auto

 

This then places the backups into the backup folder of SQL

[fiza]

Oops my mistake - I just put them in there for clarity - not actually there in real script.

[vikpaw]

Maybe if it can take speech marks, then the one missing off the end is the issue.., maybe try running the script as admin, windows 2008 is more secure by default, the box popping up makes me think it didn't like one of the parameters though.

[fiza]

@mcshammer_dj - Just replaced my script with yours and it seems to have worked. Thanks

Not sure whats changed from before but I never had issues with it previously.

[creese]

I would say

 

"F:\Microsoft SQL Server\MSSQL10_50.SIMS2008\MSSQL\Binn\" DBATTACH /AUTO /SERVER="KCN-SVR-06\SIMS2008" /DATABASE="sims" /USER="backup" /PASSWORD="xxxxxxxxx." /PATH="c:\simsbackup\"

[Mcshammer_dj]

I also use the Tidybackups.exe that Matt Smith created for my backups. Got it set to zip the backups down and only keep the last 15 on the server. Defo worth taking a look at the utility

[vikpaw]

I also use the Tidybackups.exe that Matt Smith created for my backups. Got it set to zip the backups down and only keep the last 15 on the server. Defo worth taking a look at the utility

nudge - @matt40k - come bask in the glory

[fiza]

Where can I find this utility? Sounds like just what I need

[creese]

I keep my backups for a set number of day before deleting.

[Mcshammer_dj]

Where can I find this utility? Sounds like just what I need

 

If you search for Tidybackups you will Find a thread that Matt started regarding this with information about how to use it.

 

I would link the thread but can't see how you do that....

Edited November 17, 2011 by Mcshammer_dj

[matt40k]

Where can I find this utility? Sounds like just what I need

 

tidybackups | Free software downloads at SourceForge.net

[fiza]

@Matt40k Thank you.

 

Quick (dumb) question if it zips up and then deletes everything older than 30 days there are .bak files in there called master and msdb dated from the day I installed SQL. Is it ok for those to be zipped up and then the originals deleted?

[matt40k]

It deletes anything older then specified days and then if you've set it to zip, it'll zip any uncompressed .bak files - MASTER and MSDB included and delete the uncompressed copy.

 

It's really safe as it'll ignore anything that isn't a .bak file and you can use the built-in Windows Zip function to uncompress the files - which you'll need to do if you wish to restore them - not that restoring the MASTER or MSDB databases are easy!

 

The preserve switch allows you to set a a min number of backups for each type - for each sims\fms. This will stop you deleting all your backups if your backup routine stops

 

I've also got a tool somewhere to transfer the passwords from the MASTER database to the actual database (ie SIMS), so when you backup the SIMS database, it includes all the passwords.

[bossman]

I just use the backup tool in SQL to set a task sequence which backs up all the databases into a set folder and this is then backed up daily by our normal school backup solution using BE, SQL backs up throughout the weekly 7 days (People may have altered files remotely on the weekend) and it is set to keep the last 3 backups only after it has completed the 7 days backups via another set task within SQL,

 

This works very well along with our VM backup of the server we have it pretty much covered from all possibilities as far as we are able.

[matt40k]

My tool as the reserve option, so it guarantees you have at least at set number backup (per database), so if dbattach\whatever stops creating backups, you'll at least have something - not that that's ever caught anyone out!!