Best way to backup ms sql server windows 2003

[kumarullal]

I have just migrated a windows 2003 ms SQL server 5.0 from a Dell physical server to proxmox on local storage using qcow2 disks. The performance is excellent.
However, there is question I need answer to.
What is the best way to run vzdump. In snapshot mode, or suspend mode, so that the data consistency remains.
The other problem I am facing is that the windows 2003 server does not reboot or stop completely. It hangs after stopping all the services. I have to manually stop the server and restart.
Will this cause any issues while running vzdump backups?
Please advise.

 

[udo]

I have just migrated a windows 2003 ms SQL server 5.0 from a Dell physical server to proxmox on local storage using qcow2 disks. The performance is excellent.
However, there is question I need answer to.
What is the best way to run vzdump. In snapshot mode, or suspend mode, so that the data consistency remains.
The other problem I am facing is that the windows 2003 server does not reboot or stop completely. It hangs after stopping all the services. I have to manually stop the server and restart.
Will this cause any issues while running vzdump backups?
Please advise.

Hi,
I do an sql-backup inside the VM to be sure that all is fine.
About reboot: How many RAM do you have on this VM? I have an VM with 24GB ram (W2k8) and the VM take a long time to shutdown - in the first times I wait not long enough and also reset the VM.

Udo

 

[kumarullal]

I have 4 GB Ram. It is a windows 2003 Standard edition. The max allowable ram is 4GB.
Is is possible to do vzdump backup with "Stop" option? If yes, will it take entire sql server database backup well? Will it affect the running state of my VM?

 

[udo]

I have 4 GB Ram. It is a windows 2003 Standard edition. The max allowable ram is 4GB.
Is is possible to do vzdump backup with "Stop" option? If yes, will it take entire sql server database backup well? Will it affect the running state of my VM?

Hi,
Stop, like the name says, stops the VM (I never used it) - normaly not the right thing for a DB-server.

Udo

 

[kumarullal]

What is the right way to back up a DB server?

 

[e100]

What is the right way to back up a DB server?

Use the native tools that MS provides to backup the database(s) in your server.
Preferably also copy the DB backup files to some other physically separate system.

Backing up the whole VM is simple, do a snapshot backup using vzdump.

Do NOT rely on vzdump alone.
Starting a restored snapshot backup is like you pressing the rest button on your VM at the time the snapshot was made.
It is possible, that the filesystem gets backed up in an unstable state leading to filesystem corruption.
*Most* of the time this is not an issue, but if it ends up being an issue with your DB files, you will want that MS SQL Native backup file to restore your data.

 

[davegeeit]

[FONT=Times New Roman, serif]Database of MS SQL Server is stored in .MDF file format either on Server or local drive. MDF files get damaged due to slow running of SQL Server, virus codes, accidental deletion of files and other factors. With RecoveryFix for SQL database, you can retrieve user data such as triggers, views, tables, hyperlinks and stored procedures from corrupt MDF files. It recovers SQL database in its original text formatting.

Thanks[/FONT]