2

Whenever I try to create a copy of my database using the BACKUP and RESTORE commands, the copy is created, but contains no data. I'm running the following commands, and get success status back:

BACKUP DATABASE [SomeDB]
    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak'
BACKUP DATABASE successfully processed 161 pages in 0.021 seconds (62.805 MB/sec).

RESTORE DATABASE [SomeDB_Copy]
    FROM DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak'
    WITH MOVE N'SomeDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SomeDB_copy.mdf',
    MOVE N'SomeDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SomeDB_copy.ldf'
RESTORE DATABASE successfully processed 162 pages in 0.095 seconds (13.958 MB/sec).

Am I missing a step? All of the examples I found use this method. Even on StackOverflow. This is on SQL Server 2005.

Update:. I backed up the database to a new file, and restored using that and everything worked fine. I don't know if my original file was corrupted, or something else weird was going on.

Community
  • 1
  • 1
kyork
  • 713
  • 5
  • 14
  • 1
    i hate to ask the obvious, but could you please confirm that there is data in the source db? – swasheck Dec 05 '12 at 16:33
  • At this point, I'm all for idiot checks. The source database has 1 table with 1 row, just for testing this. – kyork Dec 05 '12 at 16:44
  • 1
    I just tested your code above. Created a database called `SomeDB`, with one table and one row. Ran the code to backup and restore. When I opened `SomeDB_Copy`, it has one table with one row as expected. Is it possible you can upload `SomeDB.bak` to some file sharing site somewhere? What version of SQL Server are you using? What version of SSMS (or how are you running the query)? Does it work the same if you use the UI for backup-restore instead of doing it via code? – mellamokb Dec 05 '12 at 16:50
  • I just repeated the test on a new database... and it works. I looked at the original backup file I had been using and it was pretty large, something like 125Mb for this almost empty database. I realized I was doing a full backup every time I tested this. I don't know if that was causing an issue. – kyork Dec 05 '12 at 17:18
  • Maybe you restored the wrong `FILE` in a multi-file backupset? Note, that by "FILE" I don't mean disk file. I mean the `FILE` option of the restore statement. – usr Dec 05 '12 at 18:16

1 Answers1

1

I realize this topic is old but I wanted to expand on what usr said in his comment about the multi-file backupset, although I would have called it a multi-backup device. The fact that your backup command processed 161 pages yet the restore processed 162 pages makes me think you have more than one backup in your device/file. You can use the INIT clause on the backup command to guarantee that you only have a single backup in the device:

BACKUP DATABASE [SomeDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak' 
with init;

From the Microsoft documentation at http://msdn.microsoft.com/en-us/library/ms186865.aspx:

A disk device does not have to exist before it is specified in a BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.

You should be able to use the HEADERONLY option on the RESTORE command to check whether there are multiple backups in a device/file.

BACKUP DATABASE [SomeDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak' 
with init;

RESTORE HEADERONLY 
FROM DISK = = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak'; 

BACKUP DATABASE [SomeDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak';

RESTORE HEADERONLY 
FROM DISK = = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SomeDB.bak';

The output from the first RESTORE HEADERONLY will show one backup in the file while the output from the second should show there are two backups in the file.

CrimeWire
  • 54
  • 3
  • Thanks, I didn't have the same problem as above, but I had written multiple backups to the same file without realising it so your answer pointed me in the right direction! – Stephen Holt Jul 02 '14 at 13:08