SIMs backup script after SQL 2005 migration failing...

[mullet_man]

After the migration to 2005 our SIMs backup script is failing with the error login failed

this is the script...

@ECHO OFF
ECHO YOUR SQL DATABASE IS CURRENTLY BEING BACKED UP, THIS MAY TAKE SEVERAL MINUTES
DBATTACH /AUTO /BACKUP /SERVER=server\SIMS /USER=user /PASSWORD=password /DATABASE=SIMS

I have tried to change the database to SIMS2005 but this still doesn't help. I have tried the sa user and an system manager user. both do not work.

Any other ideas?

[PiqueABoo]

You typically change this bit (not the database name):

/SERVER=server\SIMS2005

[altecsole]

I downloaded the latest version of DBAttach.exe (v1.7 I think) and this is my command:

"e:\SQL Server\MSSQL$SIMS\Binn\DbAttach.exe" /SERVER=SERVER /DATABASE="sims" /backup /auto /trusted /user=USER /password=PASSWORD

This works for me.

[Uraken]

hi this is probally a stupid question but is sims still available during these backups?

[altecsole]

Yes, it seems to be. I've used this script to backup the dB during the day and not had any problems. Don't forget, this just creates a zipped backup that you'll then need to backup using your usual method.

[webman]

Where does dbAttach it store the backup?

[altecsole]

Where does dbAttach it store the backup?

Run SIMS System Manager - Tools - Backup Path to get the path for your system.

It's likely to be "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\" or similar.

[webman]

Ah, thanks Altecsole

[mullet_man]

I downloaded the latest version of DBAttach.exe (v1.7 I think) and this is my command:

"e:\SQL Server\MSSQL$SIMS\Binn\DbAttach.exe" /SERVER=SERVER /DATABASE="sims" /backup /auto /trusted /user=USER /password=PASSWORD

This works for me.

I'll give this ago before the end of the day, thanks!!

[altecsole]

I'll give this ago before the end of the day, thanks!!

If your SQL Server uses an instance you'll probably have to add this to the script (we don't use one);

/SERVER=SERVER/INSTANCE

[Heebeejeebee]

I can only get my 2005 SIMS to backup if I use the SA username and password despite using dbattach 1.7. I used to use my specially created backup user but that doesn't seem to work anymore. Not happy about having the sa password in plain text in my script.

HBJB

[altecsole]

Is your backup user a member of the Backup User and System Manger groups? Also, try resetting the password on the backup account, logon as the backup user and set the password and then try the script again. For some reason this kicked things into life after our upgrade. I forgot about this - sorry.

[mullet_man]

You typically change this bit (not the database name):

/SERVER=server\SIMS2005

You were right, thanks PiqueABoo. Its working now. I use the SA username and password. Thanks for the tips guys.

Stevo.

[kcymer]

You can always use build in SQL 2005 backup plan. I have it set it up for everyday @ 23.00. Working fine no problems. Output is one file *.bak.