T-SQL Backup and Restore steps help

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

SQL Server Development (2000)

T-SQL Backup and Restore steps help

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-17 : 17:28:22

Please help guys, I'm trying to backup my MSDE database via stored procedure calls.

The backup - succeeds but reports an error about shrinking the log file

The restore totally fails, leaving the database in an unusable state

Here's my script...please tell me what I'm doing wrong

BACKUP SCRIPT...

declare @DATABASEPath varchar(50)
declare @DATABASELOGPath varchar(50)

set @DATABASEPath = 'C:\BackupTest\tsbkup.bak'
set @DATABASELOGPath = 'C:\BackupTest\tsbkuplog.bak'

/* Set to full Recorvery mode to full to allow backup of Log File*/
ALTER DATABASE myAppDB SET RECOVERY FULL

/* Create Dump Device for Database and Log */
EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUP', @DATABASEPath
EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUPLOG', @DATABASELOGPath

/* Shrink Transaction Log */
DBCC SHRINKFILE (myAppDB_log, 1)

/* Backup Database and Log */
BACKUP DATABASE myAppDB TO T2MSDEBACKUP
BACKUP LOG myAppDB TO T2MSDEBACKUPLOG

/* Drop Dump Devices */
exec sp_dropdevice 'T2MSDEBACKUP'
exec sp_dropdevice 'T2MSDEBACKUPLOG'

/* Reset Recovery Mode */
ALTER DATABASE myAppDB SET RECOVERY SIMPLE

RESTORE SCRIPT...

declare @DATABASEPath varchar(50)
declare @DATABASELOGPath varchar(50)

set @DATABASEPath = 'C:\BackupTest\tsbkup.bak'
set @DATABASELOGPath = 'C:\BackupTest\tsbkuplog.bak'

/* Set DB to Single user Mode */
ALTER database myAppDB set SINGLE_USER

/* Create Dump Device for Database And Log*/
EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUP', @DATABASEPath
EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUPLOG', @DATABASELOGPath

/* Restore Database and Log*/
RESTORE DATABASE myAppDB FROM T2MSDEBACKUP WITH NORECOVERY
RESTORE LOG myAppDB FROM T2MSDEBACKUPLOG WITH RECOVERY

/* Drop Dump Devices */
exec sp_dropdevice 'T2MSDEBACKUP'
exec sp_dropdevice 'T2MSDEBACKUPLOG'

/* Enable Multi-User Mode */
ALTER database myAppDB set MULTI_USER

Kristen
Test

22859 Posts

Posted - 2006-03-19 : 02:29:24

Isn't it easier to just backup direct to a file, rather than a Dump Device?

Not sure what you are trying to do with:

/* Set to full Recorvery mode to full to allow backup of Log File*/
ALTER DATABASE myAppDB SET RECOVERY FULL

The logs are only useful AFTER the database has been running in FULL mode, if it isn't already in FULL mode then changing it and backing up the logs won't get you anything useful. And I think you need a Full Backup AFTER you set Recovery model to FULL before the log backups become usable.

/* Shrink Transaction Log */
DBCC SHRINKFILE (myAppDB_log, 1)

You should avoid shrinking a database. I presume you are moving the database to another for a debugging or additional-use purpose. Assuming you are going to continue to use the original do NOT Shrink it before backup, but if you are going to clear a whole lot of data from the Target database (to create a re-use database) or if the target is just for testing, then shrink that AFTER restore (and deleting of unneeded data)

See:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrink,Shrinking

For help with the Restore Syntax see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,Restore

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-19 : 10:41:25

Have a look at

http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html
http://www.nigelrivett.net/SQLAdmin/s_TestRestore.html

It'll do it all for you.

==========================================
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.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-20 : 10:27:29

Thanks a million guys...i appreciate your help...Thanks for links and explanations.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-20 : 11:49:39

One more issue guys...
How do i switch to the master db in my stored procedure for restore

Create Proc Restore @DBPath
AS

USE MASTER
....
....etc

generates the following error

a USE database statement is not allowed in a procedure or trigger

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-20 : 11:53:36

You don't.
The restore will work from any dtabase context it doesn't have to be master.

If you want to run something in the context of another database you can use sp_executesql

declare @sql varchar(2000)
select @sql = 'exec ' + @mydb + '..sp_executesql N''select * frmo sysobjects'''
exec (@sql)

==========================================
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.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-20 : 12:04:55

The query works fine in query analyzer directly, but when I try to run it via a stored proc - I get the error

Exclusive access could not be obtained because the database is in use.allowed either

create proc restoredb
as

USE master

ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

declare @DATABASEPath varchar(50)
set @DATABASEPath = 'C:\BackupTest\2006_3_20_11_27_50.bak'

/* Create Dump Device for Database and Log */
EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUP', @DATABASEPath

RESTORE DATABASE mydb
FROM T2MSDEBACKUP
WITH REPLACE, RECOVERY

/* Drop Dump Devices */
exec sp_dropdevice 'T2MSDEBACKUP'

quote:
Originally posted by nr

You don't.
The restore will work from any dtabase context it doesn't have to be master.

If you want to run something in the context of another database you can use sp_executesql

declare @sql varchar(2000)
select @sql = 'exec ' + @mydb + '..sp_executesql N''select * frmo sysobjects'''
exec (@sql)

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-20 : 12:31:41

If you are trying to run the sp in the database you are trying to restore then I don't think it is possible as you have to connect to that database to run it.
You can't have any connections to the database when you do the restore.
You could try dynamic sql - that will allow the use statement - you will have to include everthiongh in a single batch.

This seems to successfully change the database
exec ('select * from master..sysprocesses where spid = ' + @@spid + ' use master select * from sysobjects select * from sysprocesses where spid = ' + @@spid)

I usually create an admin database to hold these sorts of procedures.

==========================================
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 - 2006-03-20 : 12:55:50

From the link I gave above:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Might need to be in a statement block by itself (not sure). Put "GO" before and after it [if you are executing in Query Analyser]

Kristen

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-20 : 13:05:57

It work from Query analyser, but I need to run it from a stored procedure...how do I go about this

quote:
Originally posted by Kristen

From the link I gave above:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Might need to be in a statement block by itself (not sure). Put "GO" before and after it [if you are executing in Query Analyser]

Kristen

Kristen
Test

22859 Posts

Posted - 2006-03-20 : 13:41:48

Can you EXEC it?

Kristen

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-20 : 14:28:19

Thanks guys, the Exec did the trick...

One last problem. the backup and restore works on my local hard drive, but when I try to backup to my home directory, i get an error
I'm using Windows Authentication for my sql server connection, and I have full access to my home drive...

here's the query analyser script

declare @DATABASEPath varchar(50)
set @DatabasePath = '\\svrp01\to8313\DefaultBackupDirectory\T2bkup.bak'

/* Create Dump Device for Database and Log */EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUP', @DATABASEPath

/* Backup Database and Log */

BACKUP DATABASE myDatabase TO T2MSDEBACKUP

/* Drop Dump Devices */
exec sp_dropdevice 'T2MSDEBACKUP'

I get the following error
Cannot open backup device 'T2MSDEBACKUP'. Device error or device off-line. See the SQL Server error log for more details.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-22 : 09:31:58

Can't backup to Network drive...please help guys...

here's my script

declare @DATABASEPath varchar(50)
set @DatabasePath = '\\svr01\to8\DefaultBackupDirectory\T2bkup.bak'

/* Create Dump Device for Database and Log */
EXEC sp_addumpdevice 'disk', 'T2MSDEBACKUP', @DATABASEPath

/* Backup Database and Log */
BACKUP DATABASE myDatabase TO T2MSDEBACKUP

/* Drop Dump Devices */
exec sp_dropdevice 'T2MSDEBACKUP'

I get the following error
Cannot open backup device 'T2MSDEBACKUP'. Device error or device off-line. See the SQL Server error log for more details.

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 14:33:46

Personally I wouldn't attempt to backup to a network drive - I would backup locally and THEN Copy or Move the file to the remote location. If the network is down at least you get a local backup copy!

You need to make sure that the "user" running the job has access to the network share. This will most probably be whichever user the SQL Agent SERVICE is assigned to.

Kristen

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-23 : 09:35:29

Thanks Guys...I really appreciate all the help.

Like you said Kristen, I backup to a local directory and then copy to the remote share...and during restore...I reverse the same sequence if the selected restore folder is remote..

Thanks a million

quote:
Originally posted by Kristen

Personally I wouldn't attempt to backup to a network drive - I would backup locally and THEN Copy or Move the file to the remote location. If the network is down at least you get a local backup copy!

You need to make sure that the "user" running the job has access to the network share. This will most probably be whichever user the SQL Agent SERVICE is assigned to.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources