Backups - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Backups

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-05 : 12:11:47

Hi,

Been doing some disaster recovery exercises on sql 2005 express. I'm creating a backup file like this.

DECLARE @now char(14)
DECLARE @path1 varchar(100)

SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

SET @path1 = 'D:\backups\DbPubMatrix' + @now + '.BAK'

BACKUP DATABASE DbPubMatrix
TO DISK = @path1
WITH INIT

When I try and restore it on a new server I get this error

Directory lookup for the file <path> failed with the operating system error 3 - the system could not find the path specified. The file its looking for is a .mdb, not the .bak that I am trying to restore.

Am I doing this wrong?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 12:15:15

Dynamic SQL.

DECLARE @now char(14)
DECLARE @path1 varchar(100), @sys varchar(8000)

SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

SET @path1 = 'D:\backups\DbPubMatrix' + @now + '.BAK'

set @sys = '
BACKUP DATABASE DbPubMatrix
TO DISK = '' + @path1
+ ''' WITH INIT'

exec(@sys)

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 13:05:25

~~You've probably got a file called "@path1" somewhere on your system ...~~

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 13:20:37

Peter, dynamic SQL is not required for BACKUP commands. They take variables just fine. Check out my isp_Backup stored procedure.

Mondeo, please post the RESTORE command that you are using.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 13:26:07

Even if the filename contains spaces?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 13:30:46

Yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 13:31:48

Ok. Have to remember that.
I need to learn a lot more about admin part.

Thanks.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 14:38:41

Here's the code directly out of the "guts" of our Backup Sproc


BACKUP DATABASE @strDB 
	TO DISK = @BackupFile
WITH	
	DESCRIPTION = @Description,
	MEDIADESCRIPTION = @Description, 
	MEDIANAME = @MediaName,
	NAME = @Name,
	STATS = 10

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 16:38:03

He's having a problem with the restore command, so we need to see that in order to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-05 : 22:13:25

You can copy backup file to new server's local disk then restore, or start sql servicde on new server with domain account and do remote restore using unc name to reference backup file location.

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 04:02:53

"He's having a problem with the restore command"

Indeedie, and good point. But it might be that the backup file was not created where he intended it should be?

i.e. restore said "the system could not find the path specified"

Although the bit about "The file its looking for is a .mdb" suggests an error in the Restore command syntax, as you astutely deduced. OK, I'll retire now ...

Kristen

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-06 : 05:22:19

Thanks people, okay to clarify.

The backup is being created fine, then i'm copying it onto the new server.

I'm not using a restore command as such, I was trying to use the GUI restore in management studio express.

thanks

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 06:01:23

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-06 : 11:39:15

Please describe exactly what you have filled out on each of the restore screens.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources