General discussion

Locked

Backing up MS SQL 2000 dB.

By DarthBuck ·
I just installed a clean install of SQL 2000. How do I back up the dB? Can someone point me to a white paper on this procedure? It is on a NT 4.0 server.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Backing up MS SQL 2000 dB.

by johntech In reply to Backing up MS SQL 2000 dB ...

you have to stop the sql server service b4 doing a backup.
See MS SQL Server Administrators Companion Chapter 3 Backing up and restoring databases.
Hope this helps

Collapse -

Backing up MS SQL 2000 dB.

by DarthBuck In reply to Backing up MS SQL 2000 dB ...

The question was auto-closed by TechRepublic

Collapse -

Backing up MS SQL 2000 dB.

by Peter Ritchie In reply to Backing up MS SQL 2000 dB ...

Handy little script to backup a SQL Server database to a file:

--- cut here ---
DECLARE @DIR varchar(255)
DECLARE @FILE varchar(255)
DECLARE @PATH varchar(510)
DECLARE @CATALOG varchar(255)
DECLARE @LOG_NAME varchar(255)

-- destination
SELECT @DIR = 'c:\bkp\'
SELECT @FILE = 'Northwind.bak'
SELECT @PATH = @DIR + @FILE

SELECT @CATALOG = 'Northwind'
SELECT @LOG_NAME = @CATALOG + '_Log'

BACKUP DATABASE Northwind

TO DISK = @PATH

-- restore
DECLARE @DEST_DB_FILE varchar(255)
DECLARE @DEST_LOG_FILE varchar(255)

SELECT @DEST_DB_FILE = 'c:\test\testdb.mdf'
SELECT @DEST_LOG_FILE = 'c:\test\testdb.ldf'

RESTORE FILELISTONLY

FROM DISK = @PATH

-- or whatever database name you want
RESTORE DATABASE TestDB

FROM DISK = @PATH

WITH MOVE @CATALOG TO @DEST_DB_FILE,

MOVE @LOG_NAME TO @DEST_LOG_FILE

GO
--- cut here ---

This also lets you move the database to a different server (depending on where you run the restore portion ofthe script).

This script is handy because you don't have to bring the database down while the backup is occuring. While the backup script takes a bit of time; it's nowhere near the time it takes to backup the hard-drive to a tape.

The caveat (or not) is that incremental backups will backup the entire database.

If your backup is remote (i.e. occurs from a server backing-up selected workstations/servers) you probably don't have much choice in bringing the server down. You can schedule this backup script to backup to the file and the server can simply snag the new file shortly after--not eating any CPU to the remote backup process.

Collapse -

Backing up MS SQL 2000 dB.

by DarthBuck In reply to Backing up MS SQL 2000 dB ...

The question was auto-closed by TechRepublic

Collapse -

Backing up MS SQL 2000 dB.

by DarthBuck In reply to Backing up MS SQL 2000 dB ...

This question was auto closed due to inactivity

Back to Windows Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums