How can i backup and restore a SQL server database?

How can i backup and restore a SQL server database?

How can i backup and restore a SQL server database?

Search for: How can i backup and restore a SQL server database?

Here is a reasonable start


SQL Server 2008 R2

SQL Server 2008 R2 backup restore

Search for: SQL Server 2008 R2 backup restore

MSDN link: Copying Databases with Backup and Restore

in SQL Management studio, right click on the database and from TASKS menu, select BACKUP. 

Backup Type: Full
Backup TO: DISK.
Note down where it is backing up, 
   OR REMOVE and ADD your own backup 
   location and file name (on your laptop).
Click OK

To Restore:

For Case II - FTP the backup file to JDServer before following the steps below.

For Case I - follow the steps below. 

right click on destination database, Tasks->Restore->Database
(you can also right click on DATABASES->"Restore Database")

Source: Device
Click on the button labelled "..." on the right of the Device.
Select the backup file you created

Destination - select the existing database to 
   overwrite or type name of a new database to create.

All the way on the left, "Select a Page", click on FILES

Under column RESTORE AS, select the folder where you want to restore the database.

You need to type in new file names if you are not overwriting the database.

All the way on the left, "Select a Page", click on OPTIONS

Check "Overwrite existing database"

Click OK 


c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup

C:\satya\data\data\akc-data-backups-only\sql-server\test-database\test-database.bak

what copy-only backups in sqlserver

Search for: what copy-only backups in sqlserver

what are back up sets in sql server

Search for: what are back up sets in sql server

What is the meaning backup set expiry in sql server?

Search for: What is the meaning backup set expiry in sql server?

the default is not copy-only. In such a case the database state has changed to remember when the back up took place, and where it is, and if continual partial backups can be done on top.

In my case I wanted to replicate a state that is fixed at a particular time. So I hope the copy-only is the right thing to do

I went to 'databases' node and chose to restore. I have typed a new database name as opposed to picking an existing database. I have also chosen a "device" to restore from and chose the filename that was backed up earier using "copy-only".

this created a database called "test2". Original is "test". I can see both databases.

What are good sqlserver database backup strategies

Search for: What are good sqlserver database backup strategies

for a one time backup and restore (in order to copy to another server), we do not need to worry about that.

But once in production, you want to consider this.

Essentially, a backup file or tape may contain backups from several different databases, or multiple backups from the same database. Each successful backup is called a backup set.

Specially when backing up on TAPE or a limited sized disk that will be reused, the system will not automatically overwrite the backup with a new backup unless the existing backupset is has passed its expire date.

So if we have a backup strategy where all the backups are set to expire after 8 days with: 

1. weekly FULL Backup,
2. daily incremental backup,

then we only need 8 backup sets (1 full and 7 days of incremental). On the 8th day, it will start overwriting 8 day old backups. But you will still have last 7 days of backups. However, you may not be able to retrieve backups from before that.

So essentially you can figure out how much disk space you need to store the backup.

You could even do weekly full and daily differential - longer time to backup, but shorter time to restore, as it needs only the full and the latest differential. The space requirements will be more in this case.

IF WE DO NOT specify the expire date, they will never expire, which means we will have to manually delete those in order to save space. These are good for monthly backups - so you can have permanent backups. These should really be moved offsite for permanent storage.

You will need a user id that has permissions for you to restore

The userid that you use to restore may not have permissions to open the database. You have to ask the sysadmin to grant these rights every time you restore. You may want to get a script that can do this everytime if you are doing this often.

Restore may not restore previous users. You may have to define users again to suit the access rights. 


Specifying a filename is tricky
choose copy one
Don't choose tape