Interesting read Vik, thanks :)
Interesting read Vik, thanks :)
Footnote on above findings:
I went back to snapshot of clean install of spring 10. There were no logins created under security tab on SQL.
Restored my summer 10 backup. - Looking at the error message, the page full i could see was all related to the trusted logins, and then just starts on about a repair to another account. Might try to run from command line to see if error could be redirected to a file..
Anyway, closed that and found full list of logins in SQL, so just tried to login with a known account and it worked :) So don't need to even update one account to push settings as i thought, they were all pushed on as part of the restore which is really good to know. Had the machine been domained or i had no trusted logins, i probably would not even receive any errors.
Would be good if anyone else is testing that can confirm these findings. It looks like, it's not all that important to detach the db. Still i'm unconvinced, so tried to delete all users from sysman and start again, but have managed to bork the install, and cleared all users from sims, but not from SQL. Might need to consult jinnantonnix reincarnate for some sql tips tomorrow before i can know for sure.
Keep us informed as to the progress Vik :)
I never fully updated my findings because i ran out of time and over Easter needed to get the server up and running. I did a detach and reattach of my databases and didnt' lose any logins, as should be the case.
I think, that from now on, if i restore my new backup files, to a test server the logins will be pushed there, as i first thought, but the best test would be a brand new server with a clean master db. I'll see if i get time to do those tests later.
Seriously: I can confirm that this is not a safe backup method!
Why not use SQL2008 to backup and copy the files as a maintenance plan? Although once in a while I do the dbattach method.
...Which requires SQL Agent, which isn't include in the Express version.
Need less to say a thorough survey was done of all the backup methods used by the school we were involved with and the scripts to stop / start the service were removed and new scripts based on DBAttach were used instead. Recovery testing was also conducted to ensure that worked. It did.
Some online backup solutions will call DBAttach themselves to ensure the DB is backed up correctly. Smart move!
I have, on other occasions, pulled a live backup from the server and restored it to my workstation for testing. Despite being on the same Domain, I did receive the error message regarding trusted logins that @vikpaw is referring to. I can't tell you if this is because it's a workstation and not a server, or because of the method used to bring the DB over, but in both instances the account used to login into Windows was the same and has local administrator access only, not domain admin access (in case that's relevant).
As NorthernSands says, stopping the SQL service and backing up the files is a terrible idea.
You should backup the database, not the underlying files.
You can do this with relatively simple piece of TSQL coding, using a job running on the server's scheduler.
This might be worth a look: Download Details - Microsoft Download Center - Microsoft SQL Server 2008 Management Studio Express
It might be possible to use the 'generate script' feature in the above studio to give you the TSQL code which you can call from an OSQL command script.
If anybody's interested, here's a bit of code I wrote to do a manual backup (I must have had a reason).
You could set this to run in the server's regular scheduler, (as matt pointed out there's no SQL server agent in Express which is a shame).
Don't forget to put the full file path to OSQL, as there's no 'PATH' as far as the scheduler's concerned. Every file must be called or referred to by its full file path.
It takes the database name as a parameter. You could strip out the guts and just use the main OSQL bit if you just needed to backup one or two databases.Code:
rem script to backup SIMS sql database
rem v1.1 2/2/05
for /f "tokens=1" %%a in ('date /t') do set Today=%%a
set file=C:\Program Files\Microsoft SQL Server\MSSQL$SIMS\BACKUP\%1_%Today%_Backup.BAK
if $%1 NEQ $ (
echo Backing up database %1...
OSQL -S <insertSQLserverhere>\SIMS -U sa -P <insert_sa_password_here> -d master -Q "BACKUP DATABASE [%1] TO DISK = '%file%' WITH NOUNLOAD , NAME = '%1', NOSKIP , NOFORMAT"
echo Backup file on server is: %file%
) else (
echo Usage SQLBack [name of SIMS database]
echo e.g. SQLBack ABC
There's a bit of buzz about this, but I haven't tried it. The feature chart says the free version will work with up to two databases, so in a small set-up it might be ideal.
Free MySQL database backup software
What's wrong with dbAttach?
The free version doesn't encrypt and it doesn't FTP securely. So for god sake don't FTP to a external site!