Hi

I have a website (Windows NT Server) and Microsoft SQL inside.

My question: how to make backup my Ms SQL data to my harddisk with easily?

Thank you

theres a program you can use to back up data using MS Sql
sqldump i think.... http://www.developercentral.org/sqldump/
im not real familar with MS SQL yet but im learning, just MySQL.

If you have access to SQL server via terminal service then you can do it easily using Enterprise manager.

Right click on database name then choose Backup from menu. It will ask for a device (file) and then some other options then it will backup database to the specified file.

You can also do this using an enterprise manager installed on your local client computer. Just install SQL server with the Client mode (do not install server). Connect to remote server and think it is your local server and make backups the same way.

Dumping databases will not do it on all cases. Full backup is the most efficient way.

By the way it will be better if you can decrease volume of Log files before backup as they take a large space.

Mac

Try this command in a query window.... change path to your server location where DBNAME is your database name


Backup database DBNAME to disk = 'F:\Mssql7\Backup\DBNAME.dat'
with INIT

What Mac said it the best way to backup SQL databases. Then you can take the backups and copy them to a CD, Tape or what ever you like (or you can backup directly to tape)

Hope that helps!

Go to Enterprise manager of your SQL server after Login to your online database with username and password. Then right click on database name and select "All Task" option. Under it there will be "Backup" option. Give path of your local machine

copy the .mdf and .ldf file in the Data directory.

to restore it, go to ent manager and "attach database"

using backup is fine, but this is much easier.

Yes, This (the way anantatman says) is another way to backup a database but this is applicable if you have complete access to your server files + ldf and mdf files are sometimes very very larger than backups.

I have seen a backup file of only 10M creates a ldf and mdf pair of 1Gigs.

Mac

thats because the mdf and ldf files have the indexes for your tables if you specify them.. the bak files are a compressed text/sql dump

It's actually better to backup/restore the databases than it is to copy/attach DBs.

Right...Copy and restore sometimes does not properly restores the database and gives out error. Backup will compress the database as well as take the backup in a file to transfer.

agreed, but sometimes when you have to move 50 plus databases and don't feel like writing all the t-sql scripts to do the job, copy , detach, attach is fast..

also note that if you have any system wide users that have ownership of the database, you should assign dbo or sa as the owner, and delete the old user.

this can free you from a lot of hassle when restoring the database. sometimes it'll think there are users which have permissions to objects in the db like tables, storedprocs, etc... when they don't exist.

ahh, if only u were using unix instead...its so easy in unix.....PEOPLE SHOULD BE USING LINUX, just say NO when it coems to windows servers.....unless they are running apache..:D

Originally posted by dreamrae.com
ahh, if only u were using unix instead...its so easy in unix.....PEOPLE SHOULD BE USING LINUX, just say NO when it coems to windows servers.....unless they are running apache..:D


? what are you talking about ?

agreed, but sometimes when you have to move 50 plus databases and don't feel like writing all the t-sql scripts to do the job, copy , detach, attach is fast.. Then all you have to do is create a quick job and it will back them all up for you :)

ahh, if only u were using unix instead...its so easy in unix.....PEOPLE SHOULD BE USING LINUX, just say NO when it coems to windows servers.....unless they are running apache..Obviously you have no idea of what you are talking about, LOL