backupp issues - 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

SQL Server Administration (2005)

backupp issues

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-06-20 : 05:06:44

I did a backup doing

BACKUP DATABASE users TO usersbackup (my media)

now the date on the file shows todays date as last modified

when i restore this backup to a new db - it does not show updated information.

what could I be doing wrong?

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 05:28:46

Are you appending multiple backups to a single file? (and then not specifying which sub-backup to use in your Restore?)

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-20 : 11:18:30

Use 'restore filelistonly' to find out which backup you like to restore from.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-06-20 : 11:58:57

so instad of restore database i should do restore fileonly?
and what will this give me?

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 12:43:08

For Syntax of Restore and Restore Filelistonly commands see:

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-06-22 : 02:08:13

thanks for the link - very helpful

why is the physical location different then the actual backup file?
does that mean if I just copy the backup file itself (the .bak) I don't really have a complete backup

when trying restore I get an error

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\user.mdf'. Operating system error 32(The process cannot access the file because it is being used by another process.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-06-22 : 02:11:10

the restore file list gives me this

user C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\user.mdf D PRIMARY 234881024 35184372080640 1 0 0 A1D5A228-316D-478C-8155-8C9E68C63492 0 0 234881024 512 1 NULL 5391000000038900141 D8A70C27-ED58-4B98-B6BA-29D3D6BB2BB3 0 1
user_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\user_log.ldf L NULL 33226752 2199023255552 2 0 0 ABDC990C-8F86-45AF-8A26-A26E5F6CA5FA 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1

please advise how I can restore this to a new db correctly?

Kristen
Test

22859 Posts

Posted - 2007-06-22 : 05:25:02

"please advise how I can restore this to a new db correctly?"

Using the example I gave above:
[code]
RESTORE DATABASE MyNewDatabaseName
FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
WITH
REPLACE, -- Overwrite DB - if one exists
RECOVERY, -- Use if NO more files to recover, database will be set ready to use
STATS = 10, -- Show progress (every 10%)
MOVE 'user' TO 'x:\MySQLDataPath\MyNewDatabaseName.mdf',
MOVE 'user_log' TO 'x:\MySQLDataPath\MyNewDatabaseName.ldf'
GO
-- Optionally Rename logical names:
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'user', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'user_log', NEWNAME = 'MyNewDatabase_log')
GO
[code]
Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-22 : 15:35:01

Double check with 'restore headeronly'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources