Backup SQL Files

Hi,

We are about to install MSSQL Server 2000, on a Windows XP Home
Machine. However, we have servers we could set routine backups of files
to be done to. What what be the best way of doing this?

Is there functionality in SQL Server 2000, where we can say dump all
data definitions, accounts, and data to files on this drive at regular
intervals?

What other suggestions do you have apart from obviously the usual RAID,
and Tape Drive stuff?

Thanks

DavidDavid (david.goodyear@.gmail.com) writes:
> We are about to install MSSQL Server 2000, on a Windows XP Home
> Machine. However, we have servers we could set routine backups of files
> to be done to. What what be the best way of doing this?
> Is there functionality in SQL Server 2000, where we can say dump all
> data definitions, accounts, and data to files on this drive at regular
> intervals?

You could set up a job that runs from SQL Server Agent that backups
the database to a disk somewhere using the BACKUP command.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What "flavour" of SQL Server 2000 (e.g. Enterprise, Developer) will you
be installing on this machine? I only ask because certain flavours
won't let you install the server components on an XP machine...

Anyway, you can use the Database Maintenance Plan Wizard to set up a
regular full backup of a database. This will preserve all the data in
the database (including the schema), objects such as functions and
stored procedures and the user accounts you have defined in the
database.

In addition to backing up any user defined databases, you should also
consider doing a backup of the system databases (master, msdb, model)
via the Database Maintenance Plan Wizard. Doing this should allow you
to have a backup of the server logins, SQL Server Agent jobs,
maintenance plans etc.

Hope that helps a bit

Backup Server

I have just finished my install of SQL2K on Win2003. I
want to make a complete backup of the server. Do I back
with the SQL services running, or do I stop them?Do your normal NT backup, then back up all of the sql databases using SQL
Server Backup...
Some folks ( for the initial setup) will bring down sql and do the NT
backup...
"Chris" <cj.hunter@.excite.com> wrote in message
news:19e3901c3876b$8dca6d50$a601280a@.phx.gbl...
> I have just finished my install of SQL2K on Win2003. I
> want to make a complete backup of the server. Do I back
> with the SQL services running, or do I stop them?

backup on SQL2005EE

I install SQL 2005 Express Edition (upgrade from SQL 7.0 MSDE)
When I execute:
BACKUP DATABASE [Market2000] TO DISK = N'C:\PROGRA~1\MARKET~1\file.bak'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
I received:
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 4504 pages for database 'Market2000', file 'Pusta_Data' on file 1.
100 percent processed.
Processed 1 pages for database 'Market2000', file 'Pusta_Log' on file 1.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mirror_count'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_password_protected'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mirror'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'flags'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'unicode_locale'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'unicode_compare_style'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'collation_name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_password_protected'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'recovery_model'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_bulk_logged_data'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_snapshot'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_single_user'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_backup_checksums'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_damaged'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'begins_log_chain'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_incomplete_metadata'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_force_offline'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_copy_only'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'first_recovery_fork_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'last_recovery_fork_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'fork_point_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'database_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'family_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state_desc'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'create_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'drop_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'file_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_only_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_write_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'backup_size'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'filegroup_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_present'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state_desc'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'create_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'drop_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'file_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_only_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_write_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'backup_size'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'filegroup_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_present'.
Msg 3009, Level 16, State 1, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
BACKUP DATABASE successfully processed 4505 pages in 8.069 seconds (4.573
MB/sec).
What is wrong? msdb?Piotr Maciejewski (gbbsoft@.qdnet.pl) writes:
> I install SQL 2005 Express Edition (upgrade from SQL 7.0 MSDE)
> When I execute:
> BACKUP DATABASE [Market2000] TO DISK =
> N'C:\PROGRA~1\MARKET~1\file.bak'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> I received:
>...
> Processed 1 pages for database 'Market2000', file 'Pusta_Log' on file 1.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'mirror_count'.
>...
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> BACKUP DATABASE successfully processed 4505 pages in 8.069 seconds (4.573
> MB/sec).
>
> What is wrong? msdb?
Indeed. It looks as if the definition of msdb is still on SQL7 and misses
new columns added in SQL 2000 and SQL 2005. I have no idea how that could
have happened. A possible workaround could be to install a new fresh
instance of SQL 2005, and then attach the databasees to that instance.
But then you would have to migrate users from the old instance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.se> napisal w wiadomosci
news:Xns97077C2E3945Yazorman@.127.0.0.1...
> Indeed. It looks as if the definition of msdb is still on SQL7 and misses
> new columns added in SQL 2000 and SQL 2005. I have no idea how that could
> have happened. A possible workaround could be to install a new fresh
> instance of SQL 2005, and then attach the databasees to that instance.
> But then you would have to migrate users from the old instance.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
You right.
You idea works, but it is't sollution always.
Let's look at my post on microsoft.public.sqlserver.setup called: "Re: SQL
7.0 MSDE -> SQL 2005 Express".
There is more errors (miising files in setup, don't register SQLDMO, after
register it don't want to work well) when upgrading from SQL 7.0 MSDE to SQL
2005. I think than it hadn't be tested!|||Piotr Maciejewski (gbbsoft@.qdnet.pl) writes:
> You right.
> You idea works, but it is't sollution always.
> Let's look at my post on microsoft.public.sqlserver.setup called: "Re:
> SQL 7.0 MSDE -> SQL 2005 Express". There is more errors (miising files
> in setup, don't register SQLDMO, after register it don't want to work
> well) when upgrading from SQL 7.0 MSDE to SQL 2005. I think than it
> hadn't be tested!
I didn't look at your post in .setup. (Setup issues are always so difficult
to assist with. :-(
But I did get the tip that you may have to run instmsdb.sql to fix MSDB.
I was also told that msdb was not included in MSDE 1.0 by default. This
may be why Setup ignored it. If you do SELECT * FROM sys.databases,
which dbid does msdb have?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Backup MSDE database

Hi,

we have about 12 databases in an MSDE server.

We can install the enterprise manager, but cant get the maintenance plans to work. (fields stay empty and setting dont get saved)
I've searched a bit, but couldn't fix it.

No problem, i created a job, with 12 T-SQL commands, and the backups are working perfect.

Now we wanne backup with a history (we take a backup every 4 hours of the day), because now they always get overwritten.

So im trying to change my transactSQL to create dynamic files.

The code i'm using right now :

BACKUP DATABASE PW0001A00 TO DISK = 'C:\SQLBACKUP\PW0001A00.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT

I've tried different things like

BACKUP DATABASE PW0001A00 TO DISK = 'C:\SQLBACKUP\PW0001A00' + DATEPART(month, GETDATE()) + '.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT

but nothing worked.

so basically I wanne be able to create dynamic filenames in the above command.

You got any ideas/solutions.

tnx.Take Umachander's http://www.umachandar.com/technical/SQL70Scripts/Main30.htm solution in this case.|||thank you.

i'll try that solution. looks good. :-)