Solved

Create Backup of SQL DB

Posted on 2009-06-30
18
686 Views
Last Modified: 2012-05-07
Hi,

I am looking to create a backup (an exact copy with data of a db) the db is hosted on server but I need to make the copy on my local laptop.

I looked up some other Q and A in EE but cannot find what I'm looking for, I think I need to use backup to and include Mirror to

The DB to backup (hosted server) is called 'Test1' - could someone please help?
0
Comment
Question by:sanjshah12
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 4

Expert Comment

by:Prashant Sabnekar
ID: 24744407
To backup a database, do the following
1. Log in to the MS Sqlserver Management Studio
2. In the object Explorer, expand the Databases highlight the database you want to backup
3. Right click on that database (in your case it is Test1)
4. From the menu generated Select Tasks > Backup
5. by clicking ok button the back up will be created , the back up of a database id actually a .bak file whose location will be shown in the destination section of the back up dialog box.

Prashant Sabnekar
0
 

Author Comment

by:sanjshah12
ID: 24744476
Prashant, Thanks for the reply.

For the destiniation how do I select my desktop PC or laptop which is different from the hosted machine?
0
 
LVL 4

Expert Comment

by:Prashant Sabnekar
ID: 24744502
hey,
from the back up window, in the destination section select add and then select your appropriate location.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24744511
Make sure you have access from SQL Server to your box and use a UNC path, as in:
\\YourComputer\YourShare\Some.bak
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24744518
P.S.  No points please.
0
 

Author Comment

by:sanjshah12
ID: 24744575
ace - cool!, now that I have the backup of the DB as backupdb.bak how can I add this to my local SQL do I need to 'attach'?
0
 
LVL 4

Expert Comment

by:Prashant Sabnekar
ID: 24744606
acperkins >>> P.S.  No points please.

I didn't understand this
0
 

Author Comment

by:sanjshah12
ID: 24744636
Apologies, I have made a copy frm the server to my local PC but it does not show up in the Management Studio, so do I need to rename and rightclick attach DB?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24744791
you can run following script and can mention path there, you can simply give database name in WHERE clause and you will have backup of all databases given there.
DECLARE @BackupFolder AS varchar(250);
DECLARE @Path AS varchar(250);
DECLARE @DBName AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @Status AS int;
SET @CurDT = getdate();
SET @Path = '\\UNC-ServerName\SQL-BACKUP\';
DECLARE crDatabases CURSOR
FOR
 select name from master..sysdatabases where name in ('adventureworks')
OPEN crDatabases
FETCH NEXT FROM crDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 SET @BackupFolder = @Path 
  SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.0'+'.bak';
 BACKUP DATABASE @DBName
 TO  DISK = @BackupFolder 
 WITH INIT,  
 NOUNLOAD, SKIP, NOFORMAT 
   FETCH NEXT FROM crDatabases INTO @DBName
END
CLOSE crDatabases
DEALLOCATE crDatabases
 

Open in new window

0
 

Author Comment

by:sanjshah12
ID: 24745353
Ritesh,

I tried your script but because the DB is on a hosted server the path is invalid, I baced up the DB locally and copied to my local machine and renamed, when I tried to attach Database I recieved an error:

E:\temp\Test1DB Backup.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=5171

Any ideas?

Thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24745390
file you are trying to attach is .MDF file and from my script you will get .BAK file which you have to restore. how did you get .MDF file? are you trying to copy .MDF and .LDF both file and attach in your server?
0
 

Author Comment

by:sanjshah12
ID: 24745483
Ritesh, after your script I tried and got this message:

Msg 3201, Level 16, State 1, Line 17
Cannot open backup device '\\tsclient\E\temp\Test1_15.8.0.bak'. Operating system error 67(error not found).
Msg 3013, Level 16, State 1, Line 17
BACKUP DATABASE is terminating abnormally.

I am using RDP into the hosted server.

But then tried to backup on the hosted server itself and copy the .bak file to my PC, renamed and tried to attach but no luck
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24745499
your given path should be accessible (\\tsclient\E\temp\) from your SQL Server, is it? no matter from where you are talking backup, SQL Server will try to access above path from itself, it should have proper permission as well.
0
 

Author Comment

by:sanjshah12
ID: 24745819
Ritesh,

OK I now have the file as 'Test1_15.42.0.bak' how do I attach this to my local pc as a db that I use without a network connection?

Regards,

Sanjay
0
 
LVL 7

Assisted Solution

by:wilje
wilje earned 50 total points
ID: 24750652
Since you backed up the database - and copied the backup file to your machine, now you need to perform a restore.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 200 total points
ID: 24751728
first create database (I guess it is Test1 in your case) in your server with same MDF and LDF file structure, than put your .BAK file anywhere in your server (may be at D drive, as I explained in below command) and run following script in your SSMS.


use master
RESTORE DATABASE Test1
FROM DISK = N'D:\Test1_15.42.0.bak'
WITH RECOVERY, RESTRICTED_USER
GO
0
 

Author Comment

by:sanjshah12
ID: 24759424
Ritesh thanks all working!

Wilje thanks also!

I tried both and both work!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24759470
You are welcome.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question