We have a SQL2000 database that is created via a dump from a third party
product. Being as this dump runs every day, we don't need to back up the
data. However we need the schema, views, triggers, etc. Everything except
the actual data in the tables. Is there a way to make a backup of this?
Thanks!
BrianYes, use some tool that generates DDL script for the database:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian" <me@.here.there> wrote in message news:O%2323gnK8HHA.484@.TK2MSFTNGP06.phx.gbl...
> We have a SQL2000 database that is created via a dump from a third party product. Being as this
> dump runs every day, we don't need to back up the data. However we need the schema, views,
> triggers, etc. Everything except the actual data in the tables. Is there a way to make a backup of
> this?
> Thanks!
> Brian
>
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Backup display
I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
Tom
I am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
Tom
I am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
Backup display
I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
--
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
--
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
Backup display
I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Data
base
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>sql
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Data
base
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>sql
Backup devices and expired backups
Hello, I am having a problem with backup devices, and
hope someone knows a solution.
I have created a backup device on my 100GB D: drive
called Mustang_Backup. The file is located at
D:\MSSQL\BACKUP\Mustang_Backup.BAK.
My maintenance plan is a full backup on Sunday and a
differential on all other days. These are working
correctly.
My server also has a 14 day retention setting.
My problem is that the backup device is not removing the
expired backups. I now have a 75GB backup file for a 3GB
database. I am concerned that the backups are going to
fill the entire drive and then start to fail.
Does anyone know of a way to delete or remove the expired
backups in a device?
Thanks for your help,
SteveYou can't keep the different backups in one device if you want to remove
some of them after a certain time. It's all or nothing in a single device.
If your using the MP then have it create different files for each backup and
it should work as expected.
--
Andrew J. Kelly
SQL Server MVP
"steve" <sgent001@.hotmail.com> wrote in message
news:055b01c35556$78041df0$a301280a@.phx.gbl...
> Hello, I am having a problem with backup devices, and
> hope someone knows a solution.
> I have created a backup device on my 100GB D: drive
> called Mustang_Backup. The file is located at
> D:\MSSQL\BACKUP\Mustang_Backup.BAK.
> My maintenance plan is a full backup on Sunday and a
> differential on all other days. These are working
> correctly.
> My server also has a 14 day retention setting.
> My problem is that the backup device is not removing the
> expired backups. I now have a 75GB backup file for a 3GB
> database. I am concerned that the backups are going to
> fill the entire drive and then start to fail.
> Does anyone know of a way to delete or remove the expired
> backups in a device?
> Thanks for your help,
> Stevesql
hope someone knows a solution.
I have created a backup device on my 100GB D: drive
called Mustang_Backup. The file is located at
D:\MSSQL\BACKUP\Mustang_Backup.BAK.
My maintenance plan is a full backup on Sunday and a
differential on all other days. These are working
correctly.
My server also has a 14 day retention setting.
My problem is that the backup device is not removing the
expired backups. I now have a 75GB backup file for a 3GB
database. I am concerned that the backups are going to
fill the entire drive and then start to fail.
Does anyone know of a way to delete or remove the expired
backups in a device?
Thanks for your help,
SteveYou can't keep the different backups in one device if you want to remove
some of them after a certain time. It's all or nothing in a single device.
If your using the MP then have it create different files for each backup and
it should work as expected.
--
Andrew J. Kelly
SQL Server MVP
"steve" <sgent001@.hotmail.com> wrote in message
news:055b01c35556$78041df0$a301280a@.phx.gbl...
> Hello, I am having a problem with backup devices, and
> hope someone knows a solution.
> I have created a backup device on my 100GB D: drive
> called Mustang_Backup. The file is located at
> D:\MSSQL\BACKUP\Mustang_Backup.BAK.
> My maintenance plan is a full backup on Sunday and a
> differential on all other days. These are working
> correctly.
> My server also has a 14 day retention setting.
> My problem is that the backup device is not removing the
> expired backups. I now have a 75GB backup file for a 3GB
> database. I am concerned that the backups are going to
> fill the entire drive and then start to fail.
> Does anyone know of a way to delete or remove the expired
> backups in a device?
> Thanks for your help,
> Stevesql
Backup devices (named pipes)
I hope someone can at least point me in the right direction on this one.
I've written some procs and created appropriate tables to replicate logshipping without incurring the added expense of Enterprise, just for the GUI.
I'm now looking for ways to extend/enhance this functionality. Currently I use a commandline compression app (bzip2) to compress the tran log backup after it's been written to disk, and although it works, I was thinking it would be nice if I could capture the backup stream on the fly (before it get's written to disk) and compress it before writing to disk. I've found several products that do this, but almost no documentation on the actual process of capturing the backup output.
Through a little sleuthing, I was able to create a device with sp_addumpdevice using a named pipe, and a small .Net app that was to read from that pipe, but I'm running into a problem. For some reason, all I get is the header information, then SQL server seems to expect a response from me and if I don't respond within a certain timeframe (let alone that I have no idea what to send it) it closes the pipe and the backup operation fails with a device write error, listing OS error 232(pipe being closed) in the logs.
Any assistance that someone could provide would be greatly appreciated.
Thanks in advance for taking the time to read this gobbledy-gook!
That being the case, are there any resources relevant to this situation in SQL2000?
Also, I apologize if this is posted in the wrong forum, but as I wasn't able to find any SQL2000 forums, this seemed the most appropriate place to pose my question.
Thanks again for your help!
|||
I've written some procs and created appropriate tables to replicate logshipping without incurring the added expense of Enterprise, just for the GUI.
I'm now looking for ways to extend/enhance this functionality. Currently I use a commandline compression app (bzip2) to compress the tran log backup after it's been written to disk, and although it works, I was thinking it would be nice if I could capture the backup stream on the fly (before it get's written to disk) and compress it before writing to disk. I've found several products that do this, but almost no documentation on the actual process of capturing the backup output.
Through a little sleuthing, I was able to create a device with sp_addumpdevice using a named pipe, and a small .Net app that was to read from that pipe, but I'm running into a problem. For some reason, all I get is the header information, then SQL server seems to expect a response from me and if I don't respond within a certain timeframe (let alone that I have no idea what to send it) it closes the pipe and the backup operation fails with a device write error, listing OS error 232(pipe being closed) in the logs.
Any assistance that someone could provide would be greatly appreciated.
Thanks in advance for taking the time to read this gobbledy-gook!
You can write a VDI (Virtual Device Interface) application to get access to the backup data.
Named pipe backups have been deprecated since sql7, and are removed in sql2005.
You can download doc and samples for VDI here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en
|||Thanks very much for the quick reply Steve. Unfortunately, I won't be able to utilize sql2005 in a production environment for at least another year. We have many legacy systems that interface with SQL2000 via batch loads, DTS and a vagary of third party interfaces (Liant Relativity for example) and until testing is complete company wide, policy is that SQL2005 not be used.That being the case, are there any resources relevant to this situation in SQL2000?
Also, I apologize if this is posted in the wrong forum, but as I wasn't able to find any SQL2000 forums, this seemed the most appropriate place to pose my question.
Thanks again for your help!
|||
You can use VDI since sql7, so that should do the trick for you.
The VDI has not changed since sql2000, so our newer 2005 spec is mostly just clarifications. Any VDI app written for sql2000 (or sql7) should work without changes on sql2000 or sql2005.
I strongly advise you against writing a named pipe backup application. The reason is that such an app will not be compatible with sql2005.
Of course you are free to pass the VDI stream on via your own named pipes if you really want to.
This forum is good for any sql questions.
Cheers,
Tuesday, March 20, 2012
Back-up DB but keep the files for 3 days?
Hi
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
I try adding cleanup history step and see if i can get it working.
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
My question is how can i modify or program this backup plan to keep the backup files for only last 3 days and delete older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Matt Fraser wrote:
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps 3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits
|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits
|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits
|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
Back-up DB but keep the files for 3 days?
Hi
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
I try adding cleanup history step and see if i can get it working.
I have created a job to backup all our database. It works fine and creates .bak files in default folder. I have scheduled the task to run every 4 hours.
My question is how can i modify or program this backup plan to keep the backup files for only last 3 days and delete older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps 3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits
|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits
|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits
|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
sqlBack-up DB but keep the files for 3 days?
Hi
I
I try adding cleanup history step and see if i can get it working.
I
have created a job to backup all our database. It works fine and
creates .bak files in default folder. I have scheduled the task to run
every 4 hours.
My question is how can i modify or program this
backup plan to keep the backup files for only last 3 days and delete
older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Matt Fraser wrote:
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps
3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.
Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
Back-up DB but keep the files for 3 days?
Hi
I
I try adding cleanup history step and see if i can get it working.
I
have created a job to backup all our database. It works fine and
creates .bak files in default folder. I have scheduled the task to run
every 4 hours.
My question is how can i modify or program this
backup plan to keep the backup files for only last 3 days and delete
older backup files?
Mits
That's simple. I assume you are using SQL Server 2000.
If so, in Enterprise Manager, Expand Management node, select Database Maintenance plans.
Right click the maintenance plan and select properties.
Go to the complete backup tab.
Change the "Remove files older than" option to read 3 days and click ok.
Hope that Helps.
|||A few questions for you to think about if you haven't already:
Are the backups stored on a different physical drive than the databases? If not then they absolutely should be.|||
Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
|||Yes, it's simple in SQL Server 2000. It's not simple at all in SQL Server 2005. We have just recently set up our first few SQL 2005 systems, and I cannot find any way to do this (remove older backup files) using the Database Maintenance Wizard, or Database Maintenance plans in general. There is a database maintenance task called "Cleanup History," but when looking deeper into this task all it does is called a system stored procedure msdb.dbo.sp_delete_backuphistory. When looking at the code for this stored procedure, it does indeed clean out the history in the msdb tables, but it doesn't reach out to the filesystem where the backup files are created and remove the older ones.
This functionality, which worked so wonderfully in SQL Server 2000 and was so central to plans involving backups staged to disk before moving them to tape, appears to have been removed in SQL Server 2005. I cannot understand why this would be so, and I hope that I am wrong and just missing something here.
I too would like to know if there is a mechanism for automatic removal of older backup files in SQL 2005.
Matt,
I discovered this issue as well with SQL 2005, and I am perplexed as to why we have a way to back up databases but no way to manage the retention of old backups which can cause our disks to fill up. As a workaround I found this script on the web and use it to delete backup files older than "x" days, and it has worked flawlessly from the first time I started using it.
There are a few different ways to implement this workaround, but I have found the easiest way to is to copy the code below into a Notepad and save it as a .vbs file in the root of your backup drive, you might name the file something like DeleteOldBackups.vbs. This way you would simply create a Scheduled Task that points to this file and set the task to run either before or after all of your backups complete.
Make sure you set the directory paths and number of days to meet your needs.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Delete database backup files older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\databackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database backup files are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Delete database log file backups older than 5 days
iDaysOld = 4
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = ".\logfilebackups"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'If database log file backups are older than 5 days, delete them.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing
This has worked great for me, hopefully it will be useful to you and others as well.
|||Thank you everyone for you responses and i like the idea of script and i honestly agree with Matt Fraser.
I am using SQL Server 2005 Enterprise Edition.
I have had it working on SQL Server 2000 using a batch file using following command and this batch file was scheduled to run on sepecific time of the day. This will backup DB and Log files.
SQLMAINT -D DBName -BkUpDB E:\SQLBKUP -BkUpMedia DISK -DelBkUps
3days -VrfyBackup
The backup is taken on a shared Drive on the DB Server which will be backed up in the overall daily backup plan on tape. So everyday on tape, we will have the backup of last 3 days and we dont have to use hard disk space.
Regarding disaster recovery - we literally cant have any downtime from monday to saturday, but, couple of hours on Sunday could be arranged. Backup time is about 10-15 mins.
Yes I regularly restore the backups to check the validity.
I am quiet sure that this batch file will work for SQL Server 2005, but i not particularly keen on using batch file(and if i have to, i will use it). I want to do it through Maintenance Plan.
Mits|||
I also use script to achieve this at my customers (WMI/VB Script). It's much more flexible and you can code it to do what you want. I've done a few custom versions, if, for example, they want to keep N number of fulls, and only a certain number of days worth of transaction logs, etc.
It works very well, but as you hint at Mits, it is outside the control of SQL Server.
|||I did some digging and I found out that there is in fact a way to delete old backup files within the SQL management studio. I was told this functionality was going to be added with SP1 and apparently it was, just not where I expected to find it. I haven't had a chance to test it very much so I'm going to continue running my VB Script, but it is there.
Basically there are two ways to create backup jobs for your databases from within the SQL management studio Maintenence Plan area, manually or by using the wizard. If you choose to create your backup plans manually you will see the functionality to delete old backups right away, but here is how I found it because I always like to use the wizard instead.
In the SQL management studio under maintenence plans, right click maintenence plans and select the wizard. Follow the wizard all the way through to create your backup plan, you will not see any option to delete backup files older than "x" days, at the end save your backup plan.
Once your backup plan is visible under maintenence plans, right click it, and click on modify, this will open your backup plan in what appears to be a development environment window and you will see several boxes connected to each other with either green or blue arrows. Look down in the lower left hand corner and you will see a toolbox with about eight different components in it.
Select the component called Maintenence Cleanup History and drag it onto the page with your other boxes. Right click this new box and click edit, when it opens up you will see options to delete backup files older than x days. You can configure it, and then you will need to connect it to the other boxes and save the plan and then run it to test it. I tried it a couple of times and couldn't get it to work so I will have to keep testing it.
The ability to delete old backup files does exit, just not where I thought I would find it.
|||thank you very much andy
I try adding cleanup history step and see if i can get it working.
Mits|||
Well done, Andy. It seems that SP1 did in fact add this functionality, but the Wizard hasn't been updated yet to include it. So, the key is to add the step for "Maintenance Cleanup Task," recognizing that it's different from "History Cleanup Task" (which is included in the Wizard).
I've added it to my maintenance plan, and in about a week's time I should know if it's working properly.
|||This appears to work, but I won't be able to tell until tonight since Microsoft has removed the ability to delete files older than x hours. Only days, weeks, months, and years. I always used hours because of variations in backup duration. If a backup had the time of 8:01 one night and 8:00 the next, I would end up with both on the drive if I picked the 1 day option. I always used 23 hours to avoid the drive filling up. It appears that option is gone. Grrrrr! It is annoying to have a feature that you use and rely on only to have it removed.
Microsoft, please add back the hours option in a service pack.
|||
I think the frustration expressed in this thread is entirely justified and I'll ensure that the tools team here sees this.
As added weight, please go to connect.microsoft.com and give this feedback - this will open issues directly in our bug database and you will get feedback from the relevant teams.
Thanks
|||Thanks for the suggestion! I didn't know about that site. I see a closed item where the response was that "hours" would come back as a duration in SP2. I voted for that and the open item about the same issue.
I also authored an item to make the cleanup task available through the wizard as it was in SQL2000.
Thanks for taking time to listen to our concerns and point me to the right forum for airing them. Your responsiveness does your employer proud.
I want to run the backup plan ever 285 mins or 4.75 hours starting at 8.15 in the morning and finishing at 9.15 at night.
I have tried but it wont let me put anything more then 60 mins or 4.75 hours.
Mits|||
In sql2005 the retention period comes as common sql server setting.when check properties of sql server in database setting you have option to set retention period
Monday, March 19, 2012
Backup Database Maintenance Plan eats up too much disk space
I created a basic DB maintenance plan to routinely backup our database.
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
files
than
Run through the maint wiz again and you will find just that option :-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
name,
files
than
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
http://groups.google.com/groups?oi=...ublic.sqlserver
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
quote:
> the older backup files. Is there an automated way to have SQL delete
files
quote:
> older than a given number of days - for example, remove all files older
than
quote:
> 3 days.
Run through the maint wiz again and you will find just that option :-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
quote:
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
quote:
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
quote:
> older than a given number of days - for example, remove all files older
than
quote:|||D-oh! I feel so stupid...
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
quote:
remove[QUOTE]
> files
> than
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> name,
used[QUOTE]
remove[QUOTE]
> files
> than
this[QUOTE]
'DailyDBBackup.bak'[QUOTE]
>
Backup Database Maintenance Plan eats up too much disk space
I created a basic DB maintenance plan to routinely backup our database.
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days.
Run through the maint wiz again and you will find just that option :-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>|||D-oh! I feel so stupid...
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days.
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> > I created a basic DB maintenance plan to routinely backup our database.
> > Every backup job that runs through this plan is created using a unique
> name,
> > putting the date into the back file name, as in
> > DBProduction_db_200401120000.bak.
> >
> > The problem is this creates a new backup file each day - our backups our
> > faily large and after only a few days, the drive space is completely
used
> > up.
> >
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days. When we do a log ship using a maintenance plan, it asks for
this
> > information, but I haven't seen it when just running basic back ups and
> > transaction log back ups.
> >
> > What about changing the default name to something like
'DailyDBBackup.bak'
> > so that it just overwrites previous backup everytime?
> >
> >
> >
>
Every backup job that runs through this plan is created using a unique name,
putting the date into the back file name, as in
DBProduction_db_200401120000.bak.
The problem is this creates a new backup file each day - our backups our
faily large and after only a few days, the drive space is completely used
up.
What is the best way to solve this problem? Currently, we manually remove
the older backup files. Is there an automated way to have SQL delete files
older than a given number of days - for example, remove all files older than
3 days. When we do a log ship using a maintenance plan, it asks for this
information, but I haven't seen it when just running basic back ups and
transaction log back ups.
What about changing the default name to something like 'DailyDBBackup.bak'
so that it just overwrites previous backup everytime?> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days.
Run through the maint wiz again and you will find just that option :-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave Slinn" <dslinn@.gms.ca> wrote in message
news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> I created a basic DB maintenance plan to routinely backup our database.
> Every backup job that runs through this plan is created using a unique
name,
> putting the date into the back file name, as in
> DBProduction_db_200401120000.bak.
> The problem is this creates a new backup file each day - our backups our
> faily large and after only a few days, the drive space is completely used
> up.
> What is the best way to solve this problem? Currently, we manually remove
> the older backup files. Is there an automated way to have SQL delete
files
> older than a given number of days - for example, remove all files older
than
> 3 days. When we do a log ship using a maintenance plan, it asks for this
> information, but I haven't seen it when just running basic back ups and
> transaction log back ups.
> What about changing the default name to something like 'DailyDBBackup.bak'
> so that it just overwrites previous backup everytime?
>
>|||D-oh! I feel so stupid...
Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU$VRHc5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days.
> Run through the maint wiz again and you will find just that option :-).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dave Slinn" <dslinn@.gms.ca> wrote in message
> news:OtxsPCc5DHA.564@.TK2MSFTNGP10.phx.gbl...
> > I created a basic DB maintenance plan to routinely backup our database.
> > Every backup job that runs through this plan is created using a unique
> name,
> > putting the date into the back file name, as in
> > DBProduction_db_200401120000.bak.
> >
> > The problem is this creates a new backup file each day - our backups our
> > faily large and after only a few days, the drive space is completely
used
> > up.
> >
> > What is the best way to solve this problem? Currently, we manually
remove
> > the older backup files. Is there an automated way to have SQL delete
> files
> > older than a given number of days - for example, remove all files older
> than
> > 3 days. When we do a log ship using a maintenance plan, it asks for
this
> > information, but I haven't seen it when just running basic back ups and
> > transaction log back ups.
> >
> > What about changing the default name to something like
'DailyDBBackup.bak'
> > so that it just overwrites previous backup everytime?
> >
> >
> >
>
Sunday, March 11, 2012
Backup Database
HI
i have created job which executes following Store Procedure, my Qs is after 1-day has passed shldnt it overwrite , i mean i have 1-GB Database my job runs for 3-times a day so end of day i have 3-GB so wen time goes past 12.00 am shldnt it becomes 1-GB again
Alter Procedure sp_DBBackup as
BACKUP DATABASE [XYZ] TO
DISK = N'D:\Office Purpose\Database\Backups\XYZ\Backup-Method (Append-Expiry)\XYZ_Complete_Append.Bak'
WITH NOINIT ,
NOUNLOAD ,
RETAINDAYS = 1,
NAME = N'XYZ backup',
NOSKIP ,
STATS = 10,
NOFORMAT
No....you are using NOINIT so you are appending backups to the device. You would use RETAINDAYS to throw up an error message if you try to do an INIT and you do a NOSKIP meaning that it will check to see if you have any backups that aren't yet expired. If there are backups not yet expired, you get an error. That's about all RETAINDAYS does - it won't maintain your history for you - it just keeps you from overwriting.
-Sue
Thursday, March 8, 2012
Backup Contains .mdf .ldf and .ndf dont want .ndf
Hello,
I have a database(.mdf and .ldf) with a large table that is attached to
its own filegroup(.ndf). I have created a full database backup and used
Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
I see 3 files
contact.mdf
contact.ldf
cont.ndf
I restored just the .mdf and .ldf to another database and when I look at
the properties and goto file groups it shows that the filegroup is still
there too. How is the ndf a part of this database when I never restored
the .ndf
I wan to restore only .mdf and .ldf and leave the .ndf behind is this
possible and how. Thanks for all HELP!
*** Sent via Developersdex http://www.examnotes.net ***First, if you don't supply a "WITH MOVE" clause for the .ndf file, it will
use the original file path from where the backup was created. Second, if
you do a full database restore, you must restore all the files. You cannot
omit any files. You can delete the file/filegroup after the restore is
completed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:eoNsFaTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a database(.mdf and .ldf) with a large table that is attached to
> its own filegroup(.ndf). I have created a full database backup and used
> Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
> I see 3 files
> contact.mdf
> contact.ldf
> cont.ndf
> I restored just the .mdf and .ldf to another database and when I look at
> the properties and goto file groups it shows that the filegroup is still
> there too. How is the ndf a part of this database when I never restored
> the .ndf
> I wan to restore only .mdf and .ldf and leave the .ndf behind is this
> possible and how. Thanks for all HELP!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||When trying to delete file it says that
The file 'Contact'cannot be removed because it is not empty.
Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
and no .ndf?
*** Sent via Developersdex http://www.examnotes.net ***|||Have a look in BOL under DBCC SHRINKFILE specifically the EMPTYFILE option
and then ALTER DATABASE specifically the REMOVE FILE option.
Andrew J. Kelly SQL MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You have to delete all user objects from the file.filegroup. Then folow
Andrew's instructions.
Backups and restores always require a full database to work with. You can
do a filegroup backup, but it must be restored to a full database. The
short answer is no, you cannot transfer part of a database using backup and
restore. The tools simply do not work that way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***
I have a database(.mdf and .ldf) with a large table that is attached to
its own filegroup(.ndf). I have created a full database backup and used
Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
I see 3 files
contact.mdf
contact.ldf
cont.ndf
I restored just the .mdf and .ldf to another database and when I look at
the properties and goto file groups it shows that the filegroup is still
there too. How is the ndf a part of this database when I never restored
the .ndf
I wan to restore only .mdf and .ldf and leave the .ndf behind is this
possible and how. Thanks for all HELP!
*** Sent via Developersdex http://www.examnotes.net ***First, if you don't supply a "WITH MOVE" clause for the .ndf file, it will
use the original file path from where the backup was created. Second, if
you do a full database restore, you must restore all the files. You cannot
omit any files. You can delete the file/filegroup after the restore is
completed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:eoNsFaTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a database(.mdf and .ldf) with a large table that is attached to
> its own filegroup(.ndf). I have created a full database backup and used
> Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
> I see 3 files
> contact.mdf
> contact.ldf
> cont.ndf
> I restored just the .mdf and .ldf to another database and when I look at
> the properties and goto file groups it shows that the filegroup is still
> there too. How is the ndf a part of this database when I never restored
> the .ndf
> I wan to restore only .mdf and .ldf and leave the .ndf behind is this
> possible and how. Thanks for all HELP!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||When trying to delete file it says that
The file 'Contact'cannot be removed because it is not empty.
Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
and no .ndf?
*** Sent via Developersdex http://www.examnotes.net ***|||Have a look in BOL under DBCC SHRINKFILE specifically the EMPTYFILE option
and then ALTER DATABASE specifically the REMOVE FILE option.
Andrew J. Kelly SQL MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You have to delete all user objects from the file.filegroup. Then folow
Andrew's instructions.
Backups and restores always require a full database to work with. You can
do a filegroup backup, but it must be restored to a full database. The
short answer is no, you cannot transfer part of a database using backup and
restore. The tools simply do not work that way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***
BACKUP CERTIFICATE alters ACLs on created file
NOONE ASKED FOR THIS!!! why the heck is BACKUP CERTIFICATE altering the file's ACLs - it's just a PUBLIC CERT I'm exporting!!
in additions MS doesn't seem able to add the ability to re-inherit file-rights from parent objects via the command-shell.
I export the public-key for a reason! I want to import it on another machine (automatically) to establish a trust. But this freaking "feature" is costing me HOURS of research on how to get rid of these ACLs on a public certificate!!!
argh!!!
sorry, I really had to get this out!
why not at least give us a switch we can set in BACKUP CERTIFICATE command to stop this from happening?
is there a work-around? now I need to activate
EXEC sp_configure 'show advanced options', 1
and
EXEC sp_configure 'xp_cmdshell', 1
just to be able to issue a (not working) DOS command cacls... to try to fix the "feature".
there's got to be a better way!!!
also - why can't there be a switch to force overwriting the target file, if it already exists? It's a BIG PAIN having to do lots of other stuff, just to be able to "backup" the certificate!
You gave us "xp_FileExist", but no xp_FileDelete with similar parameters!! again, I need to issue DOS COMMANDS just to do stuff I would not have to if someone at the levers was actually activating the back-brain!
PLEASE give me some hints on how MS recommends to solve these features!
Hello -
First, you probably really don't want a lot of OS features in a database. Every one of them represents an extreme security risk. The ability to delete files from SQL Server would be a very dangerous thing, while checking to see if a file is present is less of a danger. There are always tradeoffs.
Second, the ACLs are there for protection, not to make things harder. We aren't trying to make things difficult. We do, however, want to hear your concerns - here's a good place for you to voice your wish-list:
http://connect.microsoft.com/SQLServer
Also, you can perform OS operations more safely in a CLR procedure than turning on xp_cmdshell. This article talks a little about the CLR and file ops:
http://aspalliance.com/1081_CLR_Integration_in_SQL_Server_2005
|||
then why supply xp_File* in the first place?
what's the point in securing a public certificate? would it not be reasonable to at least be able to turn that "feature" off by switch? even the private-key is secured by PWD and IIS, or the Certificate-MMC-SnapIn, for instance, leave the ACLs alone as they do assume anyone doing stuff like that knows what he's doing. This goes more so for any DBA exporting certs, as it's not that easy without reading some docs.
but thanks for your input!
I will try some more - maybe I can disable rights-changing on the network-share to get SQL to stop messing around with my ACLs.
|||The file sp's are there to help - and hopefully the least dangerous ones are all you need to do in a database. I'd recommend doing any other file operations (including the ACL work) in CLR's.
I agree that it would be great to have docs around this. In fact, we change the docs all the time, and add "use cases" that deal with this kind of thing. Again, thanks for using our stuff. We want you to be able to do your job quickly and easily. Go to that site I referenced and sound off! The developers acutally read what you put there and code the product around the features that get the most noise. Not many other software companies work like that, believe me.
|||First, you probably really don't want a lot of OS features in a database. Every one of them represents an extreme security risk.
one last note on that: I wouldn't have to, if SQL wouldn't mess-up (or mess around) with the ACLs and if SQL would let me overwrite a certificate. Instead it chooses to be smart and not let me turn off this smartness...
|||Point taken. Seems there are a couple of ways to handle this - please be sure and visit the link I mentioned so that the developers will get your requests!Saturday, February 25, 2012
Backup and Restore Databases
Hello,
I'm restoring some databases and when i take a look in the
users that were created on these databases they do not
appear after the restore. I've done this a lot of times
and this problem never happen before.
Best RegardsYou most probably have orphaned users:
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:14f4d01c4159d$92c1a6f0$
a301280a@.phx.gbl...
> Hello,
> I'm restoring some databases and when i take a look in the
> users that were created on these databases they do not
> appear after the restore. I've done this a lot of times
> and this problem never happen before.
> Best Regards|||Have you refreshed the user list?
>--Original Message--
>Hello,
>I'm restoring some databases and when i take a look in
the
>users that were created on these databases they do not
>appear after the restore. I've done this a lot of times
>and this problem never happen before.
>Best Regards
>.
>
I'm restoring some databases and when i take a look in the
users that were created on these databases they do not
appear after the restore. I've done this a lot of times
and this problem never happen before.
Best RegardsYou most probably have orphaned users:
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:14f4d01c4159d$92c1a6f0$
a301280a@.phx.gbl...
> Hello,
> I'm restoring some databases and when i take a look in the
> users that were created on these databases they do not
> appear after the restore. I've done this a lot of times
> and this problem never happen before.
> Best Regards|||Have you refreshed the user list?
>--Original Message--
>Hello,
>I'm restoring some databases and when i take a look in
the
>users that were created on these databases they do not
>appear after the restore. I've done this a lot of times
>and this problem never happen before.
>Best Regards
>.
>
backup and restore database on different servers
SQL SERVER 2000 (8.0.2039 ) on both servers.
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on the
destination server. When I try to restore the database on destination server,
On the Restore screen... In the option to select the .bak files (Show backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canadaDB,
You need to select the "Restore from device" radio button, then add the file
that you want to restore from.
Any server only has backup history for itself. It does not look for files
on disk of any particular pattern, it looks at the history in msdb.
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> SQL SERVER 2000 (8.0.2039 ) on both servers.
> I need to copy a database from one server to another server. I have
> created
> a backup of the database on source server. Moved the backup(.bak file) on
> the
> destination server. When I try to restore the database on destination
> server,
> On the Restore screen... In the option to select the .bak files (Show
> backups
> of database) I can see the backups of existing databases of the
> destination
> server but not the backup I want to restore which was taken on the source
> server.
> What needs to be done?
> --
> DB
> ontario, canada|||Have it backup from a device and specify the file there. Also, your build
of SQL Server 2000 has a known bug. Check out the following build chart and
apply the hotfix:
http://aspfaq.com/SQL2000Builds.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
SQL SERVER 2000 (8.0.2039 ) on both servers.
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on
the
destination server. When I try to restore the database on destination
server,
On the Restore screen... In the option to select the .bak files (Show
backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canada|||Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file) on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||Yep. Often the path on the destination serer is different from the original
server. Just specify whatever path you want.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the
file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file)
on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the
source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||DB,
Yes, you can give it the new paths where you want the .mdf and .ldf files to
reside.
Of course, I recommend always testing new commands on a test server. (I
just tested this on one of my SQL 2000 servers, using both SQL Server
Management Studio 2005 and SQL Server 2000 Enterprise Manager.)
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
> Hi Russel
> Under the options
> 1. Logical file name: I will keep same logical file names.
> 2. Move to Physical file name: Can I change it and give the path where I
> want to install the .mdf and .ldf files on the destination server.
> I know this is a silly question but I do not want to test it as working on
> production server.
> Thanks
> DB
>
> --
> ontario, canada
>
> "Russell Fields" wrote:
>> DB,
>> You need to select the "Restore from device" radio button, then add the
>> file
>> that you want to restore from.
>> Any server only has backup history for itself. It does not look for
>> files
>> on disk of any particular pattern, it looks at the history in msdb.
>> RLF
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
>> > SQL SERVER 2000 (8.0.2039 ) on both servers.
>> > I need to copy a database from one server to another server. I have
>> > created
>> > a backup of the database on source server. Moved the backup(.bak file)
>> > on
>> > the
>> > destination server. When I try to restore the database on destination
>> > server,
>> > On the Restore screen... In the option to select the .bak files (Show
>> > backups
>> > of database) I can see the backups of existing databases of the
>> > destination
>> > server but not the backup I want to restore which was taken on the
>> > source
>> > server.
>> > What needs to be done?
>> > --
>> > DB
>> > ontario, canada
>>
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on the
destination server. When I try to restore the database on destination server,
On the Restore screen... In the option to select the .bak files (Show backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canadaDB,
You need to select the "Restore from device" radio button, then add the file
that you want to restore from.
Any server only has backup history for itself. It does not look for files
on disk of any particular pattern, it looks at the history in msdb.
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> SQL SERVER 2000 (8.0.2039 ) on both servers.
> I need to copy a database from one server to another server. I have
> created
> a backup of the database on source server. Moved the backup(.bak file) on
> the
> destination server. When I try to restore the database on destination
> server,
> On the Restore screen... In the option to select the .bak files (Show
> backups
> of database) I can see the backups of existing databases of the
> destination
> server but not the backup I want to restore which was taken on the source
> server.
> What needs to be done?
> --
> DB
> ontario, canada|||Have it backup from a device and specify the file there. Also, your build
of SQL Server 2000 has a known bug. Check out the following build chart and
apply the hotfix:
http://aspfaq.com/SQL2000Builds.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
SQL SERVER 2000 (8.0.2039 ) on both servers.
I need to copy a database from one server to another server. I have created
a backup of the database on source server. Moved the backup(.bak file) on
the
destination server. When I try to restore the database on destination
server,
On the Restore screen... In the option to select the .bak files (Show
backups
of database) I can see the backups of existing databases of the destination
server but not the backup I want to restore which was taken on the source
server.
What needs to be done?
--
DB
ontario, canada|||Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file) on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||Yep. Often the path on the destination serer is different from the original
server. Just specify whatever path you want.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
Hi Russel
Under the options
1. Logical file name: I will keep same logical file names.
2. Move to Physical file name: Can I change it and give the path where I
want to install the .mdf and .ldf files on the destination server.
I know this is a silly question but I do not want to test it as working on
production server.
Thanks
DB
ontario, canada
"Russell Fields" wrote:
> DB,
> You need to select the "Restore from device" radio button, then add the
file
> that you want to restore from.
> Any server only has backup history for itself. It does not look for files
> on disk of any particular pattern, it looks at the history in msdb.
> RLF
> "db" <db@.discussions.microsoft.com> wrote in message
> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
> > SQL SERVER 2000 (8.0.2039 ) on both servers.
> > I need to copy a database from one server to another server. I have
> > created
> > a backup of the database on source server. Moved the backup(.bak file)
on
> > the
> > destination server. When I try to restore the database on destination
> > server,
> > On the Restore screen... In the option to select the .bak files (Show
> > backups
> > of database) I can see the backups of existing databases of the
> > destination
> > server but not the backup I want to restore which was taken on the
source
> > server.
> > What needs to be done?
> > --
> > DB
> > ontario, canada
>
>|||DB,
Yes, you can give it the new paths where you want the .mdf and .ldf files to
reside.
Of course, I recommend always testing new commands on a test server. (I
just tested this on one of my SQL 2000 servers, using both SQL Server
Management Studio 2005 and SQL Server 2000 Enterprise Manager.)
RLF
"db" <db@.discussions.microsoft.com> wrote in message
news:5BAF9FB9-6699-4716-A302-20E39B46ED11@.microsoft.com...
> Hi Russel
> Under the options
> 1. Logical file name: I will keep same logical file names.
> 2. Move to Physical file name: Can I change it and give the path where I
> want to install the .mdf and .ldf files on the destination server.
> I know this is a silly question but I do not want to test it as working on
> production server.
> Thanks
> DB
>
> --
> ontario, canada
>
> "Russell Fields" wrote:
>> DB,
>> You need to select the "Restore from device" radio button, then add the
>> file
>> that you want to restore from.
>> Any server only has backup history for itself. It does not look for
>> files
>> on disk of any particular pattern, it looks at the history in msdb.
>> RLF
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:E99E95DC-B68D-4325-9E65-35B6548CA6D1@.microsoft.com...
>> > SQL SERVER 2000 (8.0.2039 ) on both servers.
>> > I need to copy a database from one server to another server. I have
>> > created
>> > a backup of the database on source server. Moved the backup(.bak file)
>> > on
>> > the
>> > destination server. When I try to restore the database on destination
>> > server,
>> > On the Restore screen... In the option to select the .bak files (Show
>> > backups
>> > of database) I can see the backups of existing databases of the
>> > destination
>> > server but not the backup I want to restore which was taken on the
>> > source
>> > server.
>> > What needs to be done?
>> > --
>> > DB
>> > ontario, canada
>>
Backup and Restore - Config script
Hi all, how can I move my data from one Sql Server to a
new one I installed recently?, I mean, I want all my
databases and data (I created) that I have in my old
Server be copied in my new one. Is it just as simple as
making a Backup and then Restore my data?. Can I have the
same name for my Sql Server, if my new server is in a
different domain?. And one more question, I want to see
my actual Sql Server configuration, I mean when I
installed I chose some options I don't remember now, for
example: case sensitive, insensitive, dictionary order,
etc. So when I install my new SQL server I can pick the
same options and my new Server will act as my old one.
That's what I have planned!, I hope you can help me.
Thanks in advance
Freddy E.
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Freddy E." <freddye@.discussions.microsoft.com> wrote in message
news:1d61801c45451$379ee1a0$a601280a@.phx.gbl...
> Hi all, how can I move my data from one Sql Server to a
> new one I installed recently?, I mean, I want all my
> databases and data (I created) that I have in my old
> Server be copied in my new one. Is it just as simple as
> making a Backup and then Restore my data?. Can I have the
> same name for my Sql Server, if my new server is in a
> different domain?. And one more question, I want to see
> my actual Sql Server configuration, I mean when I
> installed I chose some options I don't remember now, for
> example: case sensitive, insensitive, dictionary order,
> etc. So when I install my new SQL server I can pick the
> same options and my new Server will act as my old one.
> That's what I have planned!, I hope you can help me.
> Thanks in advance
> Freddy E.
new one I installed recently?, I mean, I want all my
databases and data (I created) that I have in my old
Server be copied in my new one. Is it just as simple as
making a Backup and then Restore my data?. Can I have the
same name for my Sql Server, if my new server is in a
different domain?. And one more question, I want to see
my actual Sql Server configuration, I mean when I
installed I chose some options I don't remember now, for
example: case sensitive, insensitive, dictionary order,
etc. So when I install my new SQL server I can pick the
same options and my new Server will act as my old one.
That's what I have planned!, I hope you can help me.
Thanks in advance
Freddy E.
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Freddy E." <freddye@.discussions.microsoft.com> wrote in message
news:1d61801c45451$379ee1a0$a601280a@.phx.gbl...
> Hi all, how can I move my data from one Sql Server to a
> new one I installed recently?, I mean, I want all my
> databases and data (I created) that I have in my old
> Server be copied in my new one. Is it just as simple as
> making a Backup and then Restore my data?. Can I have the
> same name for my Sql Server, if my new server is in a
> different domain?. And one more question, I want to see
> my actual Sql Server configuration, I mean when I
> installed I chose some options I don't remember now, for
> example: case sensitive, insensitive, dictionary order,
> etc. So when I install my new SQL server I can pick the
> same options and my new Server will act as my old one.
> That's what I have planned!, I hope you can help me.
> Thanks in advance
> Freddy E.
Backup and Restore - Config script
Hi all, how can I move my data from one Sql Server to a
new one I installed recently?, I mean, I want all my
databases and data (I created) that I have in my old
Server be copied in my new one. Is it just as simple as
making a Backup and then Restore my data?. Can I have the
same name for my Sql Server, if my new server is in a
different domain?. And one more question, I want to see
my actual Sql Server configuration, I mean when I
installed I chose some options I don't remember now, for
example: case sensitive, insensitive, dictionary order,
etc. So when I install my new SQL server I can pick the
same options and my new Server will act as my old one.
That's what I have planned!, I hope you can help me.
Thanks in advance
Freddy E.Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Freddy E." <freddye@.discussions.microsoft.com> wrote in message
news:1d61801c45451$379ee1a0$a601280a@.phx
.gbl...
> Hi all, how can I move my data from one Sql Server to a
> new one I installed recently?, I mean, I want all my
> databases and data (I created) that I have in my old
> Server be copied in my new one. Is it just as simple as
> making a Backup and then Restore my data?. Can I have the
> same name for my Sql Server, if my new server is in a
> different domain?. And one more question, I want to see
> my actual Sql Server configuration, I mean when I
> installed I chose some options I don't remember now, for
> example: case sensitive, insensitive, dictionary order,
> etc. So when I install my new SQL server I can pick the
> same options and my new Server will act as my old one.
> That's what I have planned!, I hope you can help me.
> Thanks in advance
> Freddy E.
new one I installed recently?, I mean, I want all my
databases and data (I created) that I have in my old
Server be copied in my new one. Is it just as simple as
making a Backup and then Restore my data?. Can I have the
same name for my Sql Server, if my new server is in a
different domain?. And one more question, I want to see
my actual Sql Server configuration, I mean when I
installed I chose some options I don't remember now, for
example: case sensitive, insensitive, dictionary order,
etc. So when I install my new SQL server I can pick the
same options and my new Server will act as my old one.
That's what I have planned!, I hope you can help me.
Thanks in advance
Freddy E.Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Freddy E." <freddye@.discussions.microsoft.com> wrote in message
news:1d61801c45451$379ee1a0$a601280a@.phx
.gbl...
> Hi all, how can I move my data from one Sql Server to a
> new one I installed recently?, I mean, I want all my
> databases and data (I created) that I have in my old
> Server be copied in my new one. Is it just as simple as
> making a Backup and then Restore my data?. Can I have the
> same name for my Sql Server, if my new server is in a
> different domain?. And one more question, I want to see
> my actual Sql Server configuration, I mean when I
> installed I chose some options I don't remember now, for
> example: case sensitive, insensitive, dictionary order,
> etc. So when I install my new SQL server I can pick the
> same options and my new Server will act as my old one.
> That's what I have planned!, I hope you can help me.
> Thanks in advance
> Freddy E.
Backup and Restore - Config script
Hi all, how can I move my data from one Sql Server to a
new one I installed recently?, I mean, I want all my
databases and data (I created) that I have in my old
Server be copied in my new one. Is it just as simple as
making a Backup and then Restore my data?. Can I have the
same name for my Sql Server, if my new server is in a
different domain?. And one more question, I want to see
my actual Sql Server configuration, I mean when I
installed I chose some options I don't remember now, for
example: case sensitive, insensitive, dictionary order,
etc. So when I install my new SQL server I can pick the
same options and my new Server will act as my old one.
That's what I have planned!, I hope you can help me.
Thanks in advance
Freddy E.Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Freddy E." <freddye@.discussions.microsoft.com> wrote in message
news:1d61801c45451$379ee1a0$a601280a@.phx.gbl...
> Hi all, how can I move my data from one Sql Server to a
> new one I installed recently?, I mean, I want all my
> databases and data (I created) that I have in my old
> Server be copied in my new one. Is it just as simple as
> making a Backup and then Restore my data?. Can I have the
> same name for my Sql Server, if my new server is in a
> different domain?. And one more question, I want to see
> my actual Sql Server configuration, I mean when I
> installed I chose some options I don't remember now, for
> example: case sensitive, insensitive, dictionary order,
> etc. So when I install my new SQL server I can pick the
> same options and my new Server will act as my old one.
> That's what I have planned!, I hope you can help me.
> Thanks in advance
> Freddy E.
new one I installed recently?, I mean, I want all my
databases and data (I created) that I have in my old
Server be copied in my new one. Is it just as simple as
making a Backup and then Restore my data?. Can I have the
same name for my Sql Server, if my new server is in a
different domain?. And one more question, I want to see
my actual Sql Server configuration, I mean when I
installed I chose some options I don't remember now, for
example: case sensitive, insensitive, dictionary order,
etc. So when I install my new SQL server I can pick the
same options and my new Server will act as my old one.
That's what I have planned!, I hope you can help me.
Thanks in advance
Freddy E.Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Freddy E." <freddye@.discussions.microsoft.com> wrote in message
news:1d61801c45451$379ee1a0$a601280a@.phx.gbl...
> Hi all, how can I move my data from one Sql Server to a
> new one I installed recently?, I mean, I want all my
> databases and data (I created) that I have in my old
> Server be copied in my new one. Is it just as simple as
> making a Backup and then Restore my data?. Can I have the
> same name for my Sql Server, if my new server is in a
> different domain?. And one more question, I want to see
> my actual Sql Server configuration, I mean when I
> installed I chose some options I don't remember now, for
> example: case sensitive, insensitive, dictionary order,
> etc. So when I install my new SQL server I can pick the
> same options and my new Server will act as my old one.
> That's what I have planned!, I hope you can help me.
> Thanks in advance
> Freddy E.
Subscribe to:
Posts (Atom)