Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

Create Backup of SQL DB

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
sanjshah12
Asked:
sanjshah12
  • 7
  • 4
  • 3
  • +2
2 Solutions
 
Prashant SabnekarAVPCommented:
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
 
sanjshah12Author Commented:
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
 
Prashant SabnekarAVPCommented:
hey,
from the back up window, in the destination section select add and then select your appropriate location.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Anthony PerkinsCommented:
Make sure you have access from SQL Server to your box and use a UNC path, as in:
\\YourComputer\YourShare\Some.bak
0
 
Anthony PerkinsCommented:
P.S.  No points please.
0
 
sanjshah12Author Commented:
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
 
Prashant SabnekarAVPCommented:
acperkins >>> P.S.  No points please.

I didn't understand this
0
 
sanjshah12Author Commented:
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
 
RiteshShahCommented:
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
 
sanjshah12Author Commented:
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
 
RiteshShahCommented:
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
 
sanjshah12Author Commented:
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
 
RiteshShahCommented:
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
 
sanjshah12Author Commented:
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
 
wiljeCommented:
Since you backed up the database - and copied the backup file to your machine, now you need to perform a restore.
0
 
RiteshShahCommented:
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
 
sanjshah12Author Commented:
Ritesh thanks all working!

Wilje thanks also!

I tried both and both work!
0
 
Anthony PerkinsCommented:
You are welcome.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now