MSSQL backup

[bungpeng]

I found that if I use Backup feature in MSSQL,

when I restore, the tables' primary key disappear.

So currently I backup the database.mdf and database.ldf files.

But the problem of backup these files is I need to stop the MSSQL service.

 

So, if there any other better way?

[jspencer]

Try running this script in the Query Analyser:

 

BACKUP DATABASE dbname

TO DISK = 'c:\dbname.bak'

 

You don't need to stop anything to run this backup.

[bungpeng]

then how to restore?

Is it backup same with the backup feature in Enterprise manager?

[jspencer]

Run this script to get the logical filenames:

 

RESTORE FILELISTONLY

FROM DISK = 'c:\dbname.bak'

 

The script above will return the logical filenames for you to put in place of:

'dbname_dat' = returned data filename

'dbname_log' = returned log filename

 

RESTORE DATABASE dbname

FROM DISK = 'c:\dbname.bak'

WITH MOVE 'dbname_dat' TO 'c:\sql\data\dbname.mdf',

MOVE 'dbname_log' TO 'c:\sql\logs\dbname.ldf'

GO

 

The paths for the data and log files can be anything you want.

[jspencer]

You can also do a single file attach (just to get more complicated). Use this option for dropping a log file when it gets too big.

 

Detach the database first:

 

EXEC sp_detach_db 'dbname', true

 

Move the log file so that SQL Server can't find it (don't delete it yet).

 

Attach the logical data file only and ignore the log file in the backup:

 

EXEC sp_attach_single_file_db @dbname = 'dbname',

@physname = 'C:\SQL\Data\dbname.mdf'

 

Delete the log file if everything works.

 

Let me know if you have any problems.

[bungpeng]

I can backup my database. But when I try to restore this backup from your command: "Restore filelistonly from disk = '..'", the command run fine but I get nothing? what is this command for? because the result still no change, still one backup file and the my database record still no change

 

I need to create a new blank database to restore or I can direct overwrite my existing database?

[PlausiblyDamp]

RESTORE DATABASE dbname
FROM DISK = 'c:\dbname.bak' WITH REPLACE

 

should do the trick

[bungpeng]

What if I do not has "Query Analyser"? If I am using MSDE rather than MSSQL?

 

I can backup but can't restore without using "Query Analyser"

[PlausiblyDamp]

The code could be executed via a command object - would only take about 1/2 hour to knock up a basic text editor in VB and give it the ability to execute SQL code via a command object.