How to backup/restore entire database in easiest w

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to backup/restore entire database in easiest w

Author

Topic

Tien
Starting Member

12 Posts

Posted - 2005-01-14 : 22:08:05

Hi,

I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?

-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1

Thanks,
Tien,

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-15 : 02:19:43

You can save to another computer by replacing "c:\" with "\\servername\". The account that runs the backup though, which is probably the same one running the SQL Server or SQL Server Agent service needs to have write/read access on the share you are writing the backup to.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-01-15 : 06:38:26

What will happen if the backup routine can't find the share one day? Quite possibly "no backup" and "no alert", unless you are sure that your alerting system is bulletproof.

I would suggest you backup to local server, and then copy to remote machine (or even MOVE to remote machine - at least if that fails the backup will be left on server)

You can do the COPY/MOVE, after the backup, using

EXEC master.dbo.xp_cmdshell 'COPY c:\path\MyBackupFile.BAK \\MyServer\MyShare'

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-15 : 06:43:10

See
http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
to backup all databases (or one) on a server

http://www.mindsdoor.net/SQLAdmin/s_CopyLatestBackup.html
To copy the latset backup to a local folder

http://www.mindsdoor.net/SQLAdmin/s_RestoreLatestBackup.html
To restore the latest backup in a folder.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tien
Starting Member

12 Posts

Posted - 2005-01-16 : 20:51:10

Thanks nr, but page not found error!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-16 : 20:58:02

Try it again - and post if you still get an error.
It's ok for me.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tien
Starting Member

12 Posts

Posted - 2005-01-17 : 03:45:05

I still can't access these pages, don't know why :(

Tien
Starting Member

12 Posts

Posted - 2005-01-18 : 03:44:46

When I restore database with RESTORE DATABASE, there is an error:

Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is
terminating abnormally.

What does it mean? I'm only one user connect to this server at this time.
What are conditions to perform restore database?

thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-18 : 05:12:01

try (same pages different server)

http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html
to backup all databases (or one) on a server

http://www.nigelrivett.net/SQLAdmin/s_CopyLatestBackup.html
To copy the latset backup to a local folder

http://www.nigelrivett.net/SQLAdmin/s_RestoreLatestBackup.html
To restore the latest backup in a folder.

also try
www.mindsdoor.net
www.nigelrivett.net
www.mindsdoor.com
www.nigelrivett.com

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-01-18 : 14:01:04

"What does it mean? I'm only one user connect to this server at this time."

Are you connected to the database you are trying to restore to?

Put a

USE MASTER
GO

in front of your RESTORE command

If others MAY be connecting to the database use:

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

(EDIT: This will kill any open connections and rollback their transactions)

Kristen

Tien
Starting Member

12 Posts

Posted - 2005-01-18 : 20:15:31

thanks, I will try it all,

this is testing time, so there is only me accessing this database, and my application is connection-less, which don't keep any connection open longer 5 seconds.

in fact, for executing RESTORE statement, I've connect to database using connection string like "DataSource=(local);Initial Catalog=master;..." do I have to use "USE MASTER" more?

thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-18 : 21:09:13

A lot of providers will leave the connection accessing a different database if the login doesn't have access to the requested one without giving an error.

In query analyser try
select * from master..sysprocesses where dbid = db_id('mydbname')
to see if anything is connected to the database (it's often enterprise manager if you have used that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources