asked on

SQL Server Database Backup and Restore

Dear Experts,

I have configured an SQL 2016 with some dummy data and program an hourly backup but it will override the sql backup file.

I tried to do a database restore from the backup and renamed the database to another name.

Some who after the backup, the original SQL DB SQLDB became SQLDB (Restoring...)

Where could I have gone wrong?

SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi!

I am using a hyper-V in my office to do the SQL Server backup and restore as we need to get a solution for the client.

Basically, everyday, we want to do a full SQL backup, then every hour there is a differential backup which it will restore to full.

The next day is the second backup of the same thing with one full and an hourly differential backup with restore.

As I am not in the office, i will send you the screenshots later of the SQL 2016, thanks.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Bharat Bhushan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi! Máté Farkas

Here are the backup screenshots.

Let me know if there is any screenshots you require.
SQL-Server-2016-Backup-screenshots.doc

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi! Vitor,

I executed

"RESTORE SQLDB WITH RECOVERY
GO"

It came out the error message
"Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.

SOLUTION

Bharat Bhushan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi! Bharat,

But the issue is:

1. I created a new DB and created a table with some dummy data.
2. I scheduled and also did a manual backup of the DB.
3. I then did a DB restore with a different name.
4. After the restore is completed, the original DB becomes (Restoring...) which is puzzling.

Could the SQL DB options may have been set wrongly or the restore is set wrongly?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Alamak!

Another set of error messages

Msg 3118, Level 16, State 1, Line 1
The database "SQLDB" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Anonymous KH

ASKER

Hi! Vitor,

I think I will redo the whole SQL and take a screenshot step by step of what configuration i chose and how I got to this result.

SOLUTION

Bharat Bhushan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi! Vitor,

I dare not delete the database. It is still around.

Anyway this was what i did to do the backup.
SQL-Server-2016-Backup-Procedure.doc
SQL-Server-2016-Restore-Procedure.doc

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi! Vitor,

I created a new databse called TestDB.

The restore process I got stuck after selecting the same backup file to restore but to a new DB named RestoreDBTest.

But when I clicked OK, I have the error message.

So I am not sure whether the backup was done properly or not to get the error message.

===========================================================================

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'TestDB' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The file ID 6 on device '\\192.168.3.9\softwares\SQL Backup Test\SQLFullBkUp.bak' is incorrectly formed and can not be read. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.5000.0+((SQL14_PCU_main).160617-1804)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

Hi! arnold,

Is there a guide which I can follow on how to sql backup and restore?

I would like to lean how to do a backup of the DB.

Restore the same backed up file using a seperate name.

As my client is doing an hourly backup of the same DB.

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anonymous KH

ASKER

HI! Experts,

My apologies.

I have to admit I am really clueless about theis SQL server backup and restore.

I remembered doing it for another client where we configure the publication and just right and launched the manual backup.

But I don't know why I am not getting it right on this SQL server testing.

I have delayed this for weeks and yet I really don't know where I went wrong.

SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial